Automate import from excel or csv into Access

Anguscat

Board Regular
Joined
Jul 17, 2007
Messages
53
Does anyone know of an easy way to import other than the manual way of file-get external data-import.

I would like to be able to set something up that has a file location saved and when a new file is put in that directory I can just click a button and pull it into Access.

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Just to give an alternate perspective, I have a macro I created to do this, that is fairly straightforward and can be used to link tables as well if you choose a different transfer type:

1) Create new macro
2) add the following action: TransferText
3) add action arguments at the bottom:
transfer type Import Delimited (assuming that's the type you want)
table name Enter the name of the table that will result
file path C:/Import/test.csv (enter your path here)
has field names yes/no depending on what applies

4) save and run macro
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Denis,

Is there a way to code the file path to ask you for the file name or open a dialog box to explore and grab the file you want...

I see that what I posted doesn't really help with automatically pulling newly saved files...you would still have to add the file path.

Max
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Hi Max,

This post shows how to use the FileDialog object to browse for a file. You need Office XP or higher.

Denis
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77

ADVERTISEMENT

Hi All,

Sydney, Im looking for something really similar to the code you have done above. Although i think mine will be a little simpler.

I'm trying to use a Command Button within a form to copy data from within an excel file (All data in Columns A and B) into a specific Table within the Database i already have.

What i dont know how to do is,

A) put something in the code which lets me pick the specified .CSV file i want to import into my table.

B) Only copy data from within the .CSV file from columns A and B and put them into an exsisting Table within my database.

If you could provide any help on this i could probably adapt your code to suit.

Many Thanks
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
Hi All,

Sydney, Im looking for something really similar to the code you have done above. Although i think mine will be a little simpler.

I'm trying to use a Command Button within a form to copy data from within an excel file (All data in Columns A and B) into a specific Table within the Database i already have.

What i dont know how to do is,

A) put something in the code which lets me pick the specified .CSV file i want to import into my table.

B) Only copy data from within the .CSV file from columns A and B and put them into an exsisting Table within my database.

If you could provide any help on this i could probably adapt your code to suit.

Many Thanks
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
OK -- the ingredients are in the 2nd post I referenced.

First, go to that post and go through the steps to create an import spec. Here you can define which fields to import or skip, and their datatypes.

The code in the 2nd post lets you select a file, then import it. You will need to adjust for the name of your destination table.

Denis
 
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,918
Members
431,907
Latest member
RNN

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