Have to resave Excel files when importing w/ DoCmd.TransferSpreadsheet 1

[leicklda]

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

 

[PHV]

You may consider something like this ?
strFolder = "\\Officehp\my documents\Customers\Requested CDR's\Viewed\"
Set XL = CreateObject("Excel.Application")
strFile = "*.xls"
strFile = Dir(strFolder & strFile)
Do While Len(strFile) > 0
MsgBox strFile 'temp error checking to verify loop is working
XL.Workbooks.Open strFolder + strFile
XL.ActiveWorkbook.Close True
DoEvents
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel5, "tempNew", _
strFolder + strFile, True
strFile = Dir
Loop
XL.Quit
Set XL = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244