Jay Williams
asked on
Access automated and manual Import error.
Import fails with the error "Unexpected error from external database driver (1)," Other posted solutions don't seem to apply. Excel file location and import range is correct; file and import table structure and formatting is consistent. Here is the code:
There is other code below that is not yet edited, but I'm not there yet. The Excel file and database are attached.
MB25.XLSX
Pick.accdb
Public Sub ImportSourceData()
Dim sPath As String
sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
Src1 = sPath & "MB25.XLSX"
Src2 = sPath & "MB52Inventory.XLSX"
Src3 = sPath & "PKMC.XLSX"
WrhsTrns = sPath & "WrhsTrns.xls"
Debug.Print xlFile
'Import and delete the data files
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MB25T", Src1, True, "Sheet1!A1:Q5000"
Note: this import is the first of three into three separate tables. The import data is exported from SAP in .xlsx format.There is other code below that is not yet edited, but I'm not there yet. The Excel file and database are attached.
MB25.XLSX
Pick.accdb
acSpreadsheetTypeExcel9 is imho the Excel 2000 format, while you're using an .xlsx file which is a newer format.
ASKER
Thanks for that. What other arguments might be correct?
replace this
acSpreadsheetTypeExcel9
by this
acSpreadsheetTypeExcel12
or
acSpreadsheetTypeExcel14
depending on Excel version in your system
gowflow
acSpreadsheetTypeExcel9
by this
acSpreadsheetTypeExcel12
or
acSpreadsheetTypeExcel14
depending on Excel version in your system
gowflow
use this
DoCmd.TransferSpreadsheet acImport,10, "MB25T", Src1, True, "Sheet1!A1:Q5000"
DoCmd.TransferSpreadsheet acImport,10, "MB25T", Src1, True, "Sheet1!A1:Q5000"
then its
acSpreadsheetTypeExcel10
gowflow
acSpreadsheetTypeExcel10
gowflow
ASKER
Got the same thing, Rey. I did notice something else weird; when I try to open the MB25.xlsx file, I either get a blank screen (no sheet) or an error saying excel has stopped working. Earlier this week I had a "cannot read '~$Book.xltm" error that I had to fix. If I open Excel with a new blank sheet, MB25.xlsx opens just fine. Think I have something else going on?
Just to understand where is your macro sitting in an Excel or Access ?
gowflow
gowflow
ASKER
This is in Access.
@goflow
there is no "acSpreadsheetTypeExcel10"
there is no "acSpreadsheetTypeExcel10"
@Jay Williams
can you create a new db and try the codes.
can you create a new db and try the codes.
tried to open the DB you attached but no tables there nothing !
"MB25T" is supposed to be a table in the DB is it ?
gowflow
"MB25T" is supposed to be a table in the DB is it ?
gowflow
ASKER
Yes, I just have the navigation pane shut off F11 will show you everything.
ASKER
I'll give that a whirl, Rey. FYI, I did also try your trick of importing it into a brand new created target table, and that failed too, with the same error.
<FYI, I did also try your trick of importing it into a brand new created target table, and that failed too, with the same error. >
then, the problem is with your excel file.
then, the problem is with your excel file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Once again, you nailed it.