Excel File Won't Import into Access

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Hello,

I reference an object (Monarch) to create a table that I then want to import into my Access Database. The file is outputted by default to a .xls extension and Microsoft Excel 2.1 Worksheet (I am using Office 2003). When the below command runs, I receive an error indicating that the file is not in expected format. I have tried all the "TypeExcel#"'s and haven't had any luck. Any suggestions?

Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblVendBO", strExportTable, -1

I also tried to rename the file into a .tab file, however, when I import that file, it is all jibberish.

Thank you,
Roger
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows
I have had numerous problems in importing Excel files into Access. I think that Excel and Access try to think too much and "guess" the format of each field, and mixed fields (or fields with blanks) will often wreak havoc and cause the import to fail.

Do you have the option of exporting the file to some sort of Text file rather that an Excel file? Then you can use the TransferText command. That is much more stable, from my experiences, and you can save and use the Import Sepcification so you do not need to go through it every time (and use it in a Macro or VBA Code).
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
I have had numerous problems in importing Excel files into Access. I think that Excel and Access try to think too much and "guess" the format of each field, and mixed fields (or fields with blanks) will often wreak havoc and cause the import to fail.

Do you have the option of exporting the file to some sort of Text file rather that an Excel file? Then you can use the TransferText command. That is much more stable, from my experiences, and you can save and use the Import Sepcification so you do not need to go through it every time (and use it in a Macro or VBA Code).

I tried to rename the file to a .tab extension. When I open it manually, everything appears tobe in tact. Unfortunately when I then attemp to import, the wizard doesn't recognize the file at all.

I can export the file as its own database, which is an option that I am exploring. If I go that route, will the DB be replaced everytime this routine runs?

Thanks,
Roger
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows
Roger,

I am a bit confused. I don't know if this what you are doing, but if you have an Excel file, you can't change it to a text file simply by changing its extension. It will still be an Excel file (you can call it anything you like, but changing the name/extension of any existing file does not change the nature of the file). You would need to change how it is exported.

If you are able to export to some sort of text file (tab, CSV, fixed width, etc), you will no longer be using the TransferSpreadsheet command, you will be using the TransferText command. So you will need to change your code.

Another option for an Excel option is instead of importing it, just linking it into Access. You can do this with other database files also. The advantage there is that you do not need to re-import it every time. As long as you keep the same name, the link should be intact and reflect your latest data file (database file).
 

emady

Board Regular
Joined
Feb 19, 2003
Messages
228

ADVERTISEMENT

Hi,

I just noticed a small error in your syntax, the constant is acSpreadsheetTypeExcel97 and the filename that you are imposrting should be declared with its extension. The correct way to use the statement is:

Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblVendBO", "strExportTable.xls", -1

I am assuming that strExportTable is the excel file name? If it is then you should also include its path otherwise Access will look for it in the directory where your access file is.

Hope this resolves it for you.
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Roger,

I am a bit confused. I don't know if this what you are doing, but if you have an Excel file, you can't change it to a text file simply by changing its extension. It will still be an Excel file (you can call it anything you like, but changing the name/extension of any existing file does not change the nature of the file). You would need to change how it is exported.

If you are able to export to some sort of text file (tab, CSV, fixed width, etc), you will no longer be using the TransferSpreadsheet command, you will be using the TransferText command. So you will need to change your code.

Another option for an Excel option is instead of importing it, just linking it into Access. You can do this with other database files also. The advantage there is that you do not need to re-import it every time. As long as you keep the same name, the link should be intact and reflect your latest data file (database file).

Exellent idea. My application would not link with the Excel file. But, I changed the export from Monarch to Access and link into that newly created DB and the newly created table. This seems to have worked for me. My only question (and I think I know the answer) is that since this db will be replaced every week, the link will always be in tact as long as the filename and path stay the same.

App1 references a table in App2. As long as at the time of reference the table exists in App2, the routine in App1 should run fine. It won't matter if at some point App2 was deleted and replaced as long as the db, table and filepath all are the same as when the link was created, right?

Thanks,
Roger
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187

ADVERTISEMENT

Hi,

I just noticed a small error in your syntax, the constant is acSpreadsheetTypeExcel97 and the filename that you are imposrting should be declared with its extension. The correct way to use the statement is:

Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblVendBO", "strExportTable.xls", -1

I am assuming that strExportTable is the excel file name? If it is then you should also include its path otherwise Access will look for it in the directory where your access file is.

Hope this resolves it for you.

I tried using the acSpreadsheetTypeExcel 97 with no luck. Earlier in my code, I set the strExportTable variable to the correct filepath with a ".xls" extension. I also tried to link right to the file and that did not work either. My app just doesn't like the format for some reason.

Thanks,
Roger
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows
This seems to have worked for me. My only question (and I think I know the answer) is that since this db will be replaced every week, the link will always be in tact as long as the filename and path stay the same.

App1 references a table in App2. As long as at the time of reference the table exists in App2, the routine in App1 should run fine. It won't matter if at some point App2 was deleted and replaced as long as the db, table and filepath all are the same as when the link was created, right?

You got it!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,204
Messages
5,857,907
Members
431,907
Latest member
RNN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top