Import Excel Data Into Table in Access Using Button

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,505
Messages
6,137,027
Members
450,040
Latest member
vincentywh

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
Back
Top