HELP! Excel just won't quit!

Hi there - I am trying to copy recordsets from Access dbs to Excel (using DAO), then save the workbook and close Excel.

I had problems repeating the copyfromrecordset command, so it's all got a bit messy now. It now works, but I can't get Excel to quit...

Workbooks.Add
Application.Visible = False
For intCount = 0 To Data2.Recordset.Fields.Count - 1
Cells(1, intCount + 1).Value = Data2.Recordset.Fields(intCount).Name
Next
Range("A2").CopyFromRecordset Data2.Recordset
Set objExcel = GetObject(, "Excel.application")
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Sheets("Sheet1").Name = strReportName
objExcel.ActiveWorkbook.SaveAs FileName:= _
"C:\Windows\Temp\"
objExcel.Quit
Set objExcel = Nothing

As soon as I stop the VB ap, Excel disappears from the active task list.
I'd appreciate some help before I throw my PC out the window

Thanks!

Hi,
I am having the same problem right now too.
I have tried a few different things but excel just hangs around.

If I get an anwser to this problem I will let you know.
vice versa I hope.

;)

Maybe try closing the active worksheet first, it shouldn't matter but you never know what the hold up is on these freaky things.

I have closed the active sheet and quit the xlapp. Then ensured the xlapp = nothing. but excel refuses to close.

I am now going as far to try an find the open window in the system and close it using the Enumwindows which to be honest with you, i know nothing about yet.:confused:

If anyone else finds any way around this problem, I would also like to hear the solution. I have spent far too many hours on this single issue and have not come up with a solution.

I struggled with this one myself... but I finally figured it. (Not that I really fully understand it!!!) Here goes, I'll do my best to explain it...

The problem boils down to this...
You have open references to excel without realizing it.

To demonstrate the problem try this example...
Place two command buttons on a form in a standard exe project. Place the following code in the form.

Dim XLApp as Excel.Application

Private sub Command1_Click()
Set XLApp = New Excel.application
XLApp.Workbooks.add
XLApp.Visible = true
end sub

Private sub Command1_Click()
XLApp.Quit
Set XLApp = Nothing
end sub

Run the application, (with task manager running)press command1 to open excel. Press Command2 to completely close it. Notice that this works cleanly.

Now, go back to your code and change the line that says "XLApp.Workbooks.add" to "Workbooks.add".

Run the same test, and you'll see that Excel has not really been closed, all because you have an "orphaned" reference to Excel still open.

Does this make sense???

eer3 - thanks a heap! Once I ammended the code so that every excel operation was done under an app object, everything worked perfectly. If anyones interested, the code now reads:

Dim objExcel As Object
Set objExcel = New Excel.Application

objExcel.Workbooks.Add
objExcel.Application.Visible = False
'Get the field names
For icount = 0 To Data2.Recordset.Fields.Count - 1
objExcel.Cells(1, icount + 1).Value = Data2.Recordset.Fields(icount).Name
Next
objExcel.Range("A2").CopyFromRecordset Data2.Recordset
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Sheets("Sheet1").Name = strReportName
objExcel.ActiveWorkbook.SaveAs FileName:= _
"\\Nacwks60\Share\zz_TSG Share\TUC\" & _
strReportName & " - " & Format((datMostRecentDate), "ddmmyy")
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing

Thanks again - you have saved me the cost of a new PC and window pane.

Same problem, same frustration. In our case the memory on a PC was getting blown with multiple Excel sessions running. Our problem was with the .quit option, (was missing for a specific data result). But as unaware that we had the rest of it working. Wow.....

Glad to know I'm not the only one that comes across these frustrating problems.

Thanks eer3

I have tried what you mentioned:(
but it didnt work.
I even tried it on a virgin app and it still didnt work.
I have reference to excel8 for my apps I tried excel5 but it still occurs.
I dont really need to access any saved sheets until the vb app is closed anyway. But it is still frustrating to know the problem exists.

thnx for you replies ppl.
much appreciated
:D

This may help you out. What I did was start with the basics.

Make sure Excel is closed.
Open Excel from VB.
Verify Excel is in the running programs list.
Close Excel from VB. Do not run any additional code.
Make sure Excel is no longer in the list of running programs.

If this worked. Then add some more code. Does Excel still open and close when expected? If so then add more code until you find the specific code that causes Excel to not close properly.

I hope this helps.

Try setting the visible property to true, and look if there are any message boxes popup up.

This oughta do the trick, let me know if you have any questions with the coding :

Code:

---

    Dim IntCount As Integer, ObjExcel As Object
    Set ObjExcel = GetObject(, "Excel.application")
   
    With ObjExcel
        .Workbooks.Add
        .Workbooks(2).Worksheets(1).Select
        'You'll need to select the wotrkbook before carrying out work on it
        '.Visible = False
        'you won't need this, it's automatically set to be hidden
        'by default, and you have to enter this as true for otherwise
        For IntCount = 0 To Int(Data2.Recordset.Fields.Count - 1)
            DoEvents
            Cells(1, IntCount + 1).Value = Data2.Recordset.Fields(IntCount).Name
        Next
       
        Range("A2").CopyFromRecordset Data2.Recordset
        .Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
        .Sheets("Sheet1").Name = strReportName
        .displayalerts = False
        'This will stop any messages about overwiting and file being saved
        'from popping up on your screen and may be to do with your problem.
        .ActiveWorkbook.SaveAs FileName:="C:\Windows\Temp\XlFileName.Xls"
        'Course, this is the 2nd part of the error - your old code just shows
        'a path : ActiveWorkbook.SaveAs FileName:="C:\Windows\Temp\" and doesn't
        'specify a filename to save this as !!!!
        .Workbooks.Close
        .Quit
    End With
   
    Set ObjExcel = Nothing

---

I am trying to run a ADO query and then populate excel. My app works fine the first time but if I select another query I get the following error:
Run-time error '1004';
Method 'Range' of object '_Global' failed

Anyone seen this before?

Here is my code in my command button:
Private Sub cmdQuery_Click()
'=======================================================
'
'=======================================================
Dim adoCon As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim xl As Excel.Application
Dim xlw As Excel.Workbook
Dim strCon As String
Dim strSQL As String
Dim intCnt As Integer
Dim iCnt As Integer
Dim irCnt As Integer
Dim strDate As String
Dim strTime As String

'Set Global Timer Count to zero
giTimerCnt = 0

strDate = Format(Date, "mm-dd-yy")
strTime = Format(Time, "h-m-s")

If cboQry.Text = "" Then
lblStatusOutput.Caption = "No query selected"
Exit Sub
End If

cmdQuery.Enabled = False
'Fill array with field names
'also populate giCnt indicating number of fields starting at 1
sbFillArray

lblStatusOutput.Caption = "Query selected"

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBLocation & ";Persist Security Info=False"

Set adoCon = New ADODB.Connection

With adoCon
.ConnectionString = strCon
.Open
End With

Set adoRs = New ADODB.Recordset

strSQL = "SELECT * FROM [" & cboQry.Text & "]"

With adoRs
.CursorLocation = adUseClient
.ActiveConnection = adoCon
.Open strSQL
If .EOF = True And .BOF = True Then
frmQry.lblStatusOutput.Caption = "No records found."
irCnt = 0
Else
.MoveLast
.MoveFirst
frmQry.lblStatusOutput.Caption = "Records found."
irCnt = .RecordCount
End If
End With

'Open excel workbook
Set xl = New Excel.Application
Set xlw = xl.Workbooks.Add
xl.Visible = True

xlw.Sheets("Sheet1").Select

'Enter Field/Column Names on first row in excel file
For iCnt = 1 To giCnt
xlw.Application.Cells(1, iCnt).Value = saFields(iCnt)
Next iCnt

For intCnt = 2 To irCnt + 1
For iCnt = 1 To giCnt
xlw.Application.Cells(intCnt, iCnt).Value = (adoRs.Fields(saFields(iCnt)))
Next iCnt
adoRs.MoveNext
Next intCnt

'Makes top row bold
If giCnt = 1 Then
Range("A1").Select
Selection.Font.Bold = True
Range("A1").Select
Else
'This always errors on the second query run in a row.
'Error is:
'Run-time error '1004';
'Method 'Range' of object '_Global' failed
Range("A1:" & gsaAlphabet(giCnt - 1) & "1").Select
Selection.Font.Bold = True
Range("A1").Select
End If

'Autoresize all entered fields
For iCnt = 1 To giCnt
Columns(gsaAlphabet(iCnt - 1) & ":" & gsaAlphabet(iCnt - 1)).EntireColumn.AutoFit
Next iCnt

ChDir "C:\"
strXLSFile = "C:\QryResults" & strDate & "-" & strTime & ".xls"
xlw.SaveAs strXLSFile, xlNormal

xlw.Close
xl.Quit

Set xlw = Nothing
Set xl = Nothing

adoRs.Close
adoCon.Close

'Send email
'sbSendMail

frmQry.lblStatusOutput.Caption = "Finished processing."
frmQry.cmdQuery.Enabled = True

End Sub

I believe I found the problem. If I comment out the following code it will close just fine (from the code above). The first part makes the headings bold. Second part is autoresizing the fields.
Has anyone seen an issue with this before?


''Makes top row bold
'If giCnt = 1 Then
' Range("A1").Select
' Selection.Font.Bold = True
' Range("A1").Select
'Else
' 'This always errors on the second query run in a row.
' 'Error is:
' 'Run-time error '1004';
' 'Method 'Range' of object '_Global' failed
' Range("A1:" & gsaAlphabet(giCnt - 1) & "1").Select
' Selection.Font.Bold = True
' Range("A1").Select
'End If

''Autoresize all entered fields
'For iCnt = 1 To giCnt
' Columns(gsaAlphabet(iCnt - 1) & ":" & gsaAlphabet(iCnt - 1)).EntireColumn.AutoFit
'Next iCnt

I think you missed a few explicit references to the Excel object. Every time you reference anything in Excel from VB, do it from the top level object down. Notice that this line of code (and many others in your example), do not do this.

For example...

Range("A1").Select
Selection.Font.Bold = True

should be...

Xl.Range("A1").Select
Xl.Selection.Font.Bold = True

See what I mean?

Fix all of those orphaned references first, then let us know how it comes out! Good luck!

This syntax is also valid, probably a quicker way to fix your code and easier follow too...

With Xl
.Range("A1").Select
.Selection.Font.Bold = True
End With

eer3, you da bomb!

Thank you for showing me the light. I can see clearly know!!

Thanks!

It's really nice to see that some folks actually use the search feature to see if they can resolve their problem. Kudos to you!

Cheers lleemon!!!

Just wanted to thank everyone on this thread... i've been working for 2 weeks on getting excel to close properly and havent been able to find any help until i came across this thread. I cant believe thats what it was, and that practically all my code was good except for one thing. I'm so deliriously happy i think i'm just going to go ahead and go to lunch now.

-tony