Determine if excel file is open

[edluke]

I am trying to automate an existing manual task that has been done for several years. I need to import excel data into access, but the data must be first formatted in excel. I have automated the formatting in excel, and set access up to open the excel file and do the formatting.
My problem is knowing when the excel file is done running through its formatting routine so i can import the excel data.

So basically need to
1. Open excel file (Excel macro does formatting)
2. Determine when excel file is done formatting and closed.
3. Import data into access.

Thanx

 

[jimmythegeek]

Why not put code at the end of your Excel macro, that closes Excel, and opens your particular Access db. Then use an autoExec macro in Access to run whatever it is you need to do? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[B827]

I don't know about later versions of Access but 97 requires the spreadsheet to be OPEN when importing from spreadsheets with more than 32768 rows. Sandy

 

[edluke]

The database would already be running. This whole procedure would be an update feature of the database.
There won't be more than 32768 records.
I'm just looking for a way to check and see if the excel file is open.

thanks

 

[B827]

Just before I go home, two bits of code which might help this checks for Excel and opens it if it(Excel) is not running

Set XLApp = GetObject(, "Excel.Application&quot
If Err <> 0 Then
Set XLApp = CreateObject(&quot;Excel.Application&quot
End If


This code loops round while an ODBC connection is established. Whether you can combine them to see if Excel continues running I don't know.

Do While conODBC.StillExecuting
If MsgBox(&quot;No connection yet--keep waiting?&quot;, vbYesNo) = vbNo Then ' If the connection has not been made, ask the user
conODBC.Cancel ' if he/she wants to keep waiting. If the user does not, cancel
MsgBox &quot;Connection cancelled!&quot; ' the connection and exit the procedure.
wrkODBC.Close
Exit Function
End If
Loop


Sandy

 

[edluke]

I was able to determine when the excel file closed by using the following function in a loop:

Function IsExcelRunning() As Boolean
Dim xlApp As Object

On Error Resume Next

Set xlApp = GetObject(, &quot;Excel.Application&quot
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear

End Function