Transferspreadsheet - file open vs closed


J

Jessica

Hi, Does anyone know why this code works when the file is open but when it's
closed I get the error: Run-time error 3274: External table isn't in the
expected format.

I'm using Access 97 and Excel 2002

Public Sub ImportFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String

strTable = "timpData"

'files
strPath = "X:\AttendanceGroupOperations\Administrator\IMPORTTEST\"
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, 8, strTable, strPathFile, True,
"data"
strFile = Dir()
Loop


strTable = "timpDataHours"

'files
strPath = "X:\AttendanceGroupOperations\Administrator\IMPORTTEST\"
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, 8, strTable, strPathFile, True,
"DataHours"
strFile = Dir()
Loop

Beep
MsgBox ("Import Complete")

End Sub
 
Ad

Advertisements

K

Klatuu

I'm surprised it works at all because the version of Excel is newer than the
version of Access. That is causing the error message, but I don't know why
it works if you open the file.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top