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
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,404
Messages
6,130,378
Members
449,578
Latest member
TT123

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