Excel File Won't Import into Access

[rconverse]

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?

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

 

[Joe4]

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]

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]

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]

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:

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]

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]

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:

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]

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!

 

[rconverse]

Excellent.

Thank you very much for your assistance.