Import hangs if file is open

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

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?
 
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?
Click to expand...
 
Bill Sturdevant said:
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?
Click to expand...

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.

--
 
Back
Top