Import from Excel file

[ifotos988]

Dear all,
Please advise on what is the best way to import Excel file into Access database. I have a Excel file that need to update everyday and import to access database(by using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9) method. The problem i have is:
1) There is always empty records imported from the Excel file into the table.
2) Sometime Access will detected extra colume whereby is invisible in Excel file.

Please advise any good solution or any other method i can take in order to solve this import from excel problem?

Thanks in advance.

 

[Pat Hartman]

You can define an Access table with the columns you want. Link to the spreadsheet rather than importing it. Then run an append query that selects the non-blank rows from the linked spreadsheet and appends them to the Access table.

You could do some types of updating directly to the linked spreadsheet. You can update rows and append rows but you cannot delete rows. So, you may not need to import the data at all.

 

[commandolomo]

A further related question for the Access Gods.........

I am in a similar predicament, where I have two linked excel files which require refreshing in Access.

My question is that is it possible, via VB or a macro, to automatocally run the Update Linked Tables function in Access, without having to go through the Tools>Database Utilies>Linked Table Manager?

Can this be done with the magic of some VB sitting behind a command button? Or even as an event on "Form Load", ie when the Switchboard is opended?

 

[Pat Hartman]

You can use the TransferSpreadsheet Method/Action to link spreadsheets as well as import/export them.