Have to Re-Save excel files for DoCmd.TransferSpreadsheet

[Darleen]

I'm trying to append the data from multiple Excel spreadsheets into an
access table. The Excel files are version 5, and new ones are
constantly being downloaded from a remote site - so there is nothing I
can do to change each one's format on the creation end. The code I'm
using is as follows:

Private Sub ImportCDR()
Dim strFile As String
Dim strFolder As String

strFolder = "\\Officehp\my documents\Customers\Requested
CDR's\Viewed\"
strFile = "*.xls"
strFile = Dir(strFolder & strFile)

Do While Len(strFile) > 0
MsgBox strFile 'temp error checking to verify loop is working
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel5, "tempNew", _
strFolder + strFile, True
strFile = Dir
Loop
End Sub

This code works fine if the Excel file I'm trying to import from has
been opened and re-saved on my computer - it doesn't matter if I
resave it in the version 5 format, or even the current version 2003
format. But if I just download the files and try to have Access
import them, it finds no data.

When I resave the downloaded Excel file - I don't change anything (not
even the version format). I just open it and hit save. So why is
Access NOT able to read the original file, but it IS able to read the
unchanged, resaved file? I don't want to have to open and save each
file I download - would defeat the whole purpose of this automated
procedure.

I have experimented changing the acSpreadsheetTypeExcel5 argument to
various different versions all to no avail.

Any ideas what could be wrong with my original Excel files that Access
won't read them??

thanks in advance

 

[david epsom dot com dot au]

access table. The Excel files are version 5, and new ones are

Are they? Are they the same size after you save them?
I'd guess that they are ???BIF??? files instead - they load
silently into Excel and save silently as real Excel files.

??Binary Interchange Format??? - an old open spread sheet
format, used by Access when you SendTo, but unreadable
by Jet/TransferSpreadSheet.

(david)