Transferspreadsheet - file open vs closed

[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

 

[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.

 

[ryguy7272]

Take a look at this when you have a chance:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

HTH,
Ryan---