how to quick the time to upload data from excel

[chandru8]

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

[kadghar]

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

dim a
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.

[chandru8]

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

[kadghar]

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)

dim a
with activeworksheet
    a = range(.cells(1,1),.cells(30000,10)
end with
'You can use END to find the edges of your tables
workbooks.add
with workbooks(workbooks.count).worksheets(1)
     range(.cells(1,1),.cells(30000,10)) = a
end with

HTH