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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

pooratlas

Board Regular
Joined
Apr 17, 2007
Messages
166
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

Board Regular
Joined
Jun 19, 2007
Messages
200
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

Board Regular
Joined
Aug 21, 2006
Messages
77
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
 
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,208
Messages
5,857,920
Members
431,908
Latest member
Marjie174

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