Detecting open Excel file 1

[alpder]

I have written a large number of routines for generating reports for our agents in all parts of the country. The data is sourced from an I-series IBM using ODBC. The data for the report is generated and exported to an Excel spreadsheet which then needs to be formatted. I do that with macros in another Excel file which is called from the Access code. The reports are then sent out with a dos-based email program. It all works fine, but at times there are some problems. I currently have the report generation and emailing routines controlled by Task Scheduler. Because there are a large number of reports to be sent, I have tested all the reports and allow a few minutes gap between initiating the generation of the report and the emailing. The problem I have is that if a particular report is very large one day, then the formatting may not be complete before the email program runs.
What I have done is to run the generation of reports and emailing from an Access module.
What I need to be able to do is to test to see if the Excel file has been formatted. I decided to test to see if the Excel file was still open using something like this:

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = True
XL.Workbooks.Open "P:\ndaily\invoice.xls"

I assumed that if the file was still open I would get an error, but I don't.

I would be very grateful for any assistance you may be able to give.

 

[PHV]

Perhaps something like this ?
Dim XL As Object
On Error Resume Next
Set XL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
MsgBox "Excel still running"
Err.Clear
End If
On Error GoTo 0
Set XL = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alpder]

Thanks PHV - so simple but does the job.

Thanks again

alpder