Import Excel Data Into Table in Access Using Button

[SamBo1234]

Hi All,

Im kind of looking for a way to automate (make it easyier) something that i have to do on a regular daily basis.

I have to import data from an Excel file(s) to a specific table in my database.

I would like to be able to do this through a command button within a form. So when i click on the button it would ask me to locate the excel file i would like to import and then copy only the Columns A and B data from the excel file to a table called tblTEST in my database automatically.

Is this easy enough to do? I have tried playing with some code but im not having any luck.

Can someone help me?

Regards
Sam

 

[pooratlas]

Ideally, the name of the daily Excel file would never change (overwrite or add rows in yesterday's xls), and you could create a linked Table to the file and query the rows & columns you need.

Next best, the source folder path remains constant. In this case, create a TransferSpreadsheet macro with the Field Name argument set to filepath & formname.textboxname. Open the form, type the xls file name in the textbox, then run the macro.

 

[NumbersMax]

Question for pooratlas...I was testing your second suggestion out of curiousity:

Syntax for the transfer spreadsheet macro mentioned...I've tried several iterations but Access doesn't seem to be getting the idea.

Where the form name is Form1 and the text box is Text0
In the filename box, I've tried:

C:/Data/Form1.Text0
C:/Data/[Form1].[Text0]
C:/Data/&Form1.Text0
C:/Data/&[Form1].[Text0]
C:/Data/ & Form1.Text0
C:/Data/ & [Form1].[Text0]

Suggestions to correct the syntax?
Thanks,
Max

 

[SamBo1234]

Hi Guys,

Sorry im kind of new to access i do have some limied knowledge but what im looking for in summary is,

Vba Code to import a pre-defined list of data from an excel file of which i can pick from a "Browse" option rather than use the import option within Access. This is because i will have around 20 excel files to do a day, and having a button to click within a form would be much easier.

Regards
Sam