transfer data from excel to access,

[reza_doang]

hi all,

i have more than 20,000 excel files with same structure/format.
and i want to make database from all excel become one.
can someone provide me a link or vba to import from excel to access?
in access i can only do one by one, if i do that, it will take much time.
maybe using vba, can short time.

thanks

reza

[OBP]

Are the fiels all in the same folder?
What Version of Access & Excel are you using?
I would first of all create a table in Access of all the File names & Locations.
Then loop through the table records to import each one in to a temporary table and then append that with a query to the Final table, repeat for each file.
I think it will still take a while though.
It might be marginally faster to append directly to the Final table, but I prefer the first method.

[reza_doang]

i'm using office 2010.
all files not in same folder, there is sub folder.
i'm not an IT, and i don't clearly understand with you said before.

thanks

[OBP]

OK, What suffix do your Excel files have .xls or .xlsx?

[reza_doang]

all files .xlsx?

[OBP]

Try using this Excel sheet to get a list of all your Excel files.
Before clicking the "Get List of Excel Files" Command Button please note that you will have to set the VBA Library References to your version of Office & Excel
To do this open the worksheet enable macros, or whatever you do in Excel 2007/2010 and then press Alt+f11 to open the VBA Editor.
On the editor's Main Menu click References and untick any references that say "Missing", use the drop down list to find and tick your version of the missing references like Microsoft Excel 11.0 Object Library instead of Microsoft Excel 10.0 Object Library, which is for Excel 2000-2002.