Trouble with TransferSpreadsheet

[kevinh2320]

I'm importing an .xls file into my MSAccess DB from a known trusted source but, get a Run-time error "3170" Could not file installlable ISAM. When I open the .xls file directly I'm prompted with the following message:

If I click "Yes" the file opens fine. Is there a way to get my VBA code to answer this prompt so the file will import? The code I'm using is shown below:

Private Sub importDayUseData_Click()

Dim varRetVal As Variant

'This code imports Registration data
If MsgBox("This action will load all Day Use registration data. Do you want to continue?", _
vbYesNo + vbQuestion) = vbYes Then
DoCmd.SetWarnings False
DoCmd.Close acForm, "f_commRec_day_use"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel08, "t_website_registrants", "F:\commrec_reminder_ltr\Data\CommRecDayUseWebData\commrec_export.xls", False
varRetVal = ClearClipboard
vForm = "f_commRec_day_use"
DoCmd.OpenForm vForm, acNormal

Else

Exit Sub

End If

MsgBox ("Download Complete!")

End Sub

 

[JonXL]

Try a different value for acSpreadsheetTypeExcel08.

Maybe 9 instead of 8?

AcSpreadSheetType enumeration (Access)

Office VBA reference topic

docs.microsoft.com

 

[ranman256]

its usually the: acSpreadsheetTypeExcel08, format.
try another.