Problem with Workbook not shutting down properly

[SaeOngJeeMa]

Hello, I'm having a problem with one of my Excel files. Whenever I close the
file I get a warning message:

"This workbook is referenced by another workbook and cannot be closed"

I never referenced this through any other workbooks to my knowledge. The
only thing I can think of is there is one sub in the workbook that does a SQL
query on the data within one of the worksheets. I pasted the code from that
sub below. Thanks in advance for any help you can provide.

--
Best Regards,
Dean


Public Sub rewriteFSFDataToWsht(strMeasConfig As String)
On Error GoTo Err_rewriteFSFDataToWsht

'*********************************************************************
'Name:
' rewriteFSFDataToWsht
'Type:
' Public subroutine, Excel VBA
'Author:
' Dean Faith
'History:
' Last updated 2006/11/30
'Purpose:
' clears current data from the FSF worksheet, requeries the
Master worksheet with the latest
' customer inputs on the Meas controls and writes the data
to the worksheet
'Args:
' strMeasConfig
'Returns:
' nothing
'Notes:
' none
'*********************************************************************

Dim rngTemp As Range
Dim rstData As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String

'allow changes to the FSF worksheet
gbytFSFWshtChangeIgnoreFlag = gcbytTrue

'clear the old contents of the worksheet
With Worksheets("FSF")
Set rngTemp = .Range(.Cells(3, 1), .Cells.SpecialCells(xlLastCell))
End With

rngTemp.ClearContents


' Create the connection string.
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\FILEBOX\ProductivityTools\DFTTI.xls;" & _
"Extended Properties=Excel 8.0;"

strSQL = buildFSFSQLString(strMeasConfig)

Set rstData = New ADODB.Recordset
rstData.Open strSQL, strConnection, adOpenForwardOnly, adLockReadOnly,
adCmdText


'verify data were received, if so write to worksheet, if not warn the user
If Not rstData.EOF Then
Worksheets("FSF").Range("A3").CopyFromRecordset rstData
Else
MsgBox "No records returned.", vbCritical
End If

'clear row of data I used to get the desired QAR order- pretty cheesy but
it works
With Worksheets("FSF")
Set rngTemp = .Range(.Cells(3, 31), .Cells.SpecialCells(xlLastCell))
End With

rngTemp.ClearContents

'reset protection from changes to the FSF worksheet
gbytFSFWshtChangeIgnoreFlag = gcbytFalse

'set the proper number format for the data cells
Call formatFSFWshtCells

'Clean up object and control variables
rstData.Close
Set rstData = Nothing
Set rngTemp = Nothing

'reset the range variables for the FSF worksheet
Call setWorksheetDataRangesFSF

Exit_rewriteFSFDataToWsht:
Exit Sub
Err_rewriteFSFDataToWsht:
MsgBox "sub rewriteFSFDataToWsht " & Err.Description
Resume Exit_rewriteFSFDataToWsht
End Sub

 

[Chip Pearson]

The first thing I would do is make sure that you do, in fact, have no
references to the workbook. In a module in the workbook you are trying to
close, paste and run the following code:

Sub FindRef()
Dim Ref As Object
Dim WB As Workbook
For Each WB In Workbooks
For Each Ref In WB.VBProject.References
If Ref.FullPath = ThisWorkbook.FullName Then
MsgBox "Workbook: " & WB.FullName & " references this
workbook."
End If
Next Ref
Next WB
End Sub

This will pop up a MsgBox if it finds another workbook that references this
workbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[SaeOngJeeMa]

Hi Chip,
I'm getting an error "Programmatic access to Visual Basic Project is not
trusted." I tried adjusting the security settings but that didn't do the
trick. I also set a break in the sub and tried to get a count
WB.VBProject.References.Count but got that same error. Any suggestions?
Thanks.

 

[dkinn]

What version of Excel are you running?
I was getting that same error in 2007 and had to check the "Trust access to
the VBA project object model" checkbox.

if you are in 2007
click on the office button (top left of excel) and click on the Excel
options button (lower right)
trust Center ( on the left) and then Trust Center Settings ( on the right)

Then you can check the Trust Access checkbox.

Hope this helps

David

 

[SaeOngJeeMa]

Hi David,
I'm running Office 2003 so I don't have access to those menus. Could this be
a problem with 2003 also? Thanks.

 

[Tom Ogilvy]

Yes it can. In tools=>Macro=>Security. . . then go to the Trusted
Publishers tab and see the checkboxes at the bottom of that dialog.

 

[SaeOngJeeMa]

all, I was able to run the sub to check for references thanks to your help.
What I found is that there are 4 references listed below. Chip's program
didn't find these. I put a break point in the sub and then:

? WB.VBProject.References.Count
4

? WB.VBProject.References.Item(1).Name
? WB.VBProject.References.Item(2).Name
? WB.VBProject.References.Item(3).Name
? WB.VBProject.References.Item(4).Name
VBA
Excel
stdole
Office

These look like standard references. Does anyone have any ideas why I'm
getting that warning message when I exit Excel? Thanks.

 

[Tom Ogilvy]

The error means a different workbook than the workbook your trying to close
is referencing your workbook. In turn, You would have to make each workbook
that is open the active project in the VBE (it is selected in the project
explorer window) and then look in tools =>references and see if one has a
reference to your workbook.

that is what Chip's code is doing. It doesn't show those references because
it will only show a reference to your workbook. The code you posted appears
to be looking in the workbook that won't close - which is the wrong workbook
to look in.