Large Excel xls Import to Access

ralmanza

New Member
Joined
Mar 12, 2019
Messages
4
I am trying to import a 14 tab Excel xls document into Access and it is taking over two hours. Each tab has the same type data and only the first tab has headers, there are 15 columns and the Excel document is 253kb.

I need help to get the time to import dramatically reduced. Any suggestions?

I have tried a macro and also vba.

Thanks for any help you can give me,

Ralmanza
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
How do you import the file? VBA or Access import wizard?
Which operation is taking too long?
How is your database structured? Is it split? Do you have a separate back-end and where is it - local HDD or network? If network - how fast is the connection?

Give some more info.
 
Upvote 0
Thanks for replying, in answer to your questions:
I have tried vba and macro using import excel workbook.
The actual import portion of the 14 tabs is taking too long. I isolated this portion into one macro and it ran for 2 - 2 1/2 hours.
In this database I only have the queries and tables needed for this one report. It is not split and I am pulling the data through our company network.
Sorry, I have not idea how fast our connection is here at work. I run a similar report with nine tabs and it takes about 10-15 minutes.

I hope this helps.
 
Upvote 0
have you tried to import manually with the import wizard? sheet by sheet to see if there is a particular one that is slower than the others?
can you post the macro that imports the data?
is the data in the sheets static or is coming in from somewhere else?
 
Upvote 0
I will try to import each sheet manually. I did put the report and the database on my C drive and it cut almost an hour. But this is still too long.
 
Upvote 0
Just curious, have you tried just linking the Excel tabs to Access tables instead of actually importing them?
It is often not necessary to physically import the Excel files into Access in order to work with them (espcially if you are not planning on updating the underlying data in the tables).
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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