By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

how to quick the time to upload data from excel

chandru8
100+
P: 145
hi to all

iam uploading data from excel to msaccess using vb6.0.
it taking nearly more than 40 min for uploading 30000 records to the access.
is there any way to reduce the time

thanks
Apr 28 '08 #1
Share this Question
Share on Google+
3 Replies


kadghar
Expert 100+
P: 1,295
hi to all

iam uploading data from excel to msaccess using vb6.0.
it taking nearly more than 40 min for uploading 30000 records to the access.
is there any way to reduce the time

thanks
Perhaps this might be of help
Expand|Select|Wrap|Line Numbers
  1. dim a
  2. a = range(cells(1,1), cells(1,1).end(-4121).end(-4161)) 
Now the whole data in excel in in an array called 'a'.

Excel is very slow while working cell by cell, so don't ever do that, instead, create a variant. Then when you asign a range to a variant, the variant wont become a Range, but an array of data, which is very handy.

May be, uploading the array to the access will be faster if you do it from the array instead.
Apr 28 '08 #2

chandru8
100+
P: 145
Hi
Thanks For Your Reply
I Need To Copy Data From One Excel To Another Through Vba If That Is The Case How I Have To Use Your Code

Can Explain Me
Thanks
Apr 29 '08 #3

kadghar
Expert 100+
P: 1,295
Hi
Thanks For Your Reply
I Need To Copy Data From One Excel To Another Through Vba If That Is The Case How I Have To Use Your Code

Can Explain Me
Thanks
same thing, just use a variant
lets say you have your inf from cell A1 to cell J30000, then just do something like this:

(note in this example, i'll create a new workbook, but you can use the OPEN method)

Expand|Select|Wrap|Line Numbers
  1. dim a
  2. with activeworksheet
  3.     a = range(.cells(1,1),.cells(30000,10)
  4. end with
  5. 'You can use END to find the edges of your tables
  6. workbooks.add
  7. with workbooks(workbooks.count).worksheets(1)
  8.      range(.cells(1,1),.cells(30000,10)) = a
  9. end with
HTH
Apr 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.