Excel seems to stay "open" even though use set obj

[Amileaux]

From Access I am trying to open an excel file and save it as "score.xls" before importing into access. The following code is what I've come up with. Two issues:
1. If I totally close down access and restart is the first part of the code works, and if excel is not open it will go to the appropriate line. However, if I run it a second time, it does not recognize that Excel is not open and proceeds with the first line of code as if it is open.
2. Since either way works - I apparently don't need to have a worksheet open to perform the SAVE AS function?
Here's the code I've come up with:
Sub DelayedOrderSave()
Dim appXL As Object

On Error Resume Next
'if the application is not running, an error occurs
Set appXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set appXL = CreateObject("Excel.Application")
appXL.Visible = True
End If
'open file
appXL.Workbooks.Open ("C:\WINNT\Profiles\mamicuc\Desktop\Score.xls")

Excel.Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:="C:\WINNT\Profiles\mamicuc\Desktop\Score.xls" _
, FileFormat:=xlText, CreateBackup:=False

appXL.Quit
Excel.Application.DisplayAlerts = True
Set appXL = Nothing

End Sub

Any thoughts? Marie[/code]

 

[Philem]

Hullo. I do something similar, with a main point to consider. At the beginning of your code, you should have a procedure that checks to see if Excel is running. I point you to http://www.mvps.org/access/ for some excellent information.

HTH

P

 

[Amileaux]

Re: Excel seems to stay "open" even though use set

Thanks! I have been to that site many times and had printed out the code for "fIsAppRunning" - but found it very confusing - however, upon your recommendation I will reconsider and put on my "figure this out" hat and do it. Thanks again. Marie

 

[Amileaux]

Re: Excel seems to stay "open" even though use set

I've loaded the fIsAppRunning function and the same problem still happens. However, I believe it is related to my use of DisplayAlerts. I get two messages, which I suppress by setting the DisplayAlerts to False (then back to True at the end). However, if I remove those lines from my code, and answer the message boxes - excel shuts down like it should. The two messages I get are:
A file name "the path and filename" already exists in this location. Do you want to replace it? The answer is yes.

"filename' is not in Microsoft Excel 97 format. Do you want to save your changes?

Any thoughts around that or am I barking up the wrong tree?

Marie

 

[Russell Hauf]

Re: Excel seems to stay "open" even though use set

I have a couple of suggestions:

1) Save your file with a .txt extension if you really want a text file. This should suppress the first warning. If you are saving over a previous text file, then use Kill to delete the text file first (to not have the warning come up).

2) After the SaveAs, use

ActiveWorkbook.Close False

If you do these, you shouldn't need to set DisplayAlerts to False.

Also, be aware that when you set DisplayAlerts to False, the default button is chosen when a message box comes up. So if you don't know what the default is, then you may get unwanted results.

HTH,

Russell