Can't shut down Excel with VBA

[JimReid]

Program creates an Excel spreadsheet and populates it. It ask operator if they want to save or not. Close down everything but still get an error if I try to run it again without closing the form.



This is the error on the first reference to a new spreadsheet. Every thing I have been able to find is that Excel doesn't shut down if you use a WITH/ENDWITH clause and I don't. Someone mentioned to run DOEVENTS to pause before ending Excel and that didn't help either. Don't know what to do because the client would like to run several version of the spreadsheet with different options in the form. Any help would be appreciated.

[Welshgasman]

You could at least show the code?

[ranman256]

is the command: application.Quit

[JimReid]

Thanks, ranman256 but that didn't work. It closed the Excel but it is still running somewhere. According to MS it is still there although it doesn't show with the Task Manager. I have to actually close the program down (in VBA) and then rerun it. Going to the End Sub doesn't do it.

[JimReid]

This is some of what I've tried:
# Set XL = New Excel.Application XL.Visible = True
Set WB = XL.Workbooks.Add
Set WKS = WB.Worksheets(1)

DO SOMETHING

Set WB = Nothing
XL.Quit
DoEvents
Set XL = Nothing
If Me.Dirty Then Me.Dirty = False
End Sub

[Welshgasman]

According to MS it is still there although it doesn't show with the Task Manager.

Where is that stated?, as little point having a Task manager if it cannot find all the tasks, surely?

[JimReid]

I don't remember where I saw it but it was they were trying to say it was a With/WithEnd that got out without hitting the WithEnd. That was when they said Excel will sometimes leave some of it there although it doesn't show on taskmaster. If I see it again while I'm looking around I'll quote it next time.

Jr

[Micron]

Sometimes what you're looking for isn't listed in the Apps listing, but rather in the Background Processes list. If you have Excel or Access open, IMO it has to be listed there somewhere.

[Welshgasman]

Are you writing bad code as per this link?
https://stackoverflow.com/questions/...e-on-this-code

Look at these others if not.
https://www.google.com/search?q=cann...hrome&ie=UTF-8

[JimReid]

I guess I'm writing 'bad code' because I didn't put in the full references. According to you link you only have to do that because it is Access/Excel VBA code and would not have to do it in pure VB. I'm going through my code now and filling out the full references (what a pain) with the hopes that it will work then. Thanks.....I think Welshgasman.

[Welshgasman]

Normally when not using full references in access for Excel VBA, it works the first time, and then falls over when you try to run it again?
That is what I have noticed, from members posts on forums.

[JimReid]

I started going through my code and putting in full references and hit another problem. My first sheet works perfect. When I create the second sheet it appears to work setting up the formats until I start putting in headings. (Worked perfect on first sheet) and they don't show up. Even though they are referenced as "Sheet1". In Immediate windows if I display cells(1,2) after moving a heading to it it will display the correct heading but it isn't in the "Sheet"? Very weird. I display (in Immediate windows) the sheet and it says 'Sheet1'. and I display cell(1,2) and it shows the heading but on the excel spreadsheet there is nothing!! I check to make sure the application was 'Visible' and it is. Don't know where to turn now since the same code worked on the first spreadsheet and only stopped on the second. The only changes I made was to complete full references on all the code. HELP!!!!

[Micron]

Since we can't see the code, a guess is about all one can do. You turned off screen updating for the Excel session somewhere in there?
Like

xlx.ScreenUpdating = False

Did you get code from here?
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

[JimReid]

Mine seems to be the opposite. I started putting in full references and they work the first time and the second time.....well see my other post. It shows in the immediate window but not on the actual excel spreadsheet:

[JimReid]

No I did NOT turn off ScreenUpdateing and I did NOT get it from your reference. All I did was start putting in full referencing to all the fields in the spreadsheet and all the sheets. Don't know why this started happening. Here is where the code fails:
# WS.Range("a1:L1").WrapText = True WS.Range("A1:L1").HorizontalAlignment = xlCenter
WS.Range("a1").Font.Size = 20
WS.Range("J1:L1").Merge
'******************** Setup Headings ************************************
WS.Cells(1, 1).Value = "test"
WS.Cells(1, 2) = "Avg total hours"
WS.Cells(1, 3).Value = "Avg site time"

Guess I can't paste pictures of the results but cells(1,1) is empty as is 1,2 and 1,3 on the spreadsheet (sheet1)
If I display them in the Immediate windows they are there.