Import hangs if file is open

[Bill Sturdevant]

I am using a query to import only the usable records in an
excel file:
SELECT
MyKeyCol, MyDataCol
INTO
MyImportTable
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls].[MySheet$]
WHERE
MyKeyCol IS NOT NULL
;

When I execute it, if the file specified is already open
by Excel, the query just sits there. How can I detect
that the file is open and throw up a message to go close
it?

 

[John Nurick]

Hi Bill,

One way is to attempt to rename the file and trap the resulting error.
Air code follows

Dim strFileSpec As String

strFileSpec = "C:\MyWorkbook.xls"

On Error Resume Next
Name strFileSpec As strFileSpec & ".$$$"
Select Case Err.Number
Case 75
MsgBox "Cannot access " & strFileSpec _
& ". Probably another program has opened it. " _
& "Close it and try again" , vbOkOnly + vbInformation
Exit Sub
Case > 0
MsgBox "Some other problem"
Exit Sub
End Select
Err.Clear
On Error Goto 0
Name strFileSpec & ".$$$" As strFilespec
'continue with the import

I am using a query to import only the usable records in an
excel file:
SELECT
MyKeyCol, MyDataCol
INTO
MyImportTable
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls].[MySheet$]
WHERE
MyKeyCol IS NOT NULL
;

When I execute it, if the file specified is already open
by Excel, the query just sits there. How can I detect
that the file is open and throw up a message to go close
it?

 

[Jamie Collins]

I am using a query to import only the usable records in an
excel file
<<snip>>
When I execute it, if the file specified is already open
by Excel, the query just sits there. How can I detect
that the file is open and throw up a message to go close
it?

I know no way of using SQL to detect whether the file is open. There
are various ways of doing so using VBA and I've found the following to
be very fast:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209189

In comparison, automating Excel and opening the workbook in its native
app is very slow.

Jamie.

--