p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB Databases Basics (http://p2p.wrox.com/forumdisplay.php?f=82)
-   -   Very slow Import XLSX file to access MDB (http://p2p.wrox.com/showthread.php?t=93954)

dsgdsg December 30th, 2014 04:33 PM

Very slow Import XLSX file to access MDB
 
Hello there !
I wrote the following code to import an excel table containing some 25 columns and thousands of records.

The import time is extremely long with a few thousands records ... and since the xlsx files might grow and reach 100.000 records ... it will not be acceptable !

Would you have any better method to access the xlsx records and copy them to the MS-access table ?! Many thanks !!



cfile = xPath + "sales.xlsx"
objExcel.Workbooks.Open cfile
nindex = 1

StatusBar.Panels(1) = "Importing from excel ..."

Do While Not (IsNull(objExcel.Cells(nindex, 1)) Or IsEmpty(objExcel.Cells(nindex, 1)))

T_ECSALES.Recordset.AddNew

For X = 1 To UBound(aFields)
If IsNull(objExcel.Cells(nindex, aFields(X).ColumnNumber + 1)) Or _
IsEmpty(objExcel.Cells(nindex, aFields(X).ColumnNumber + 1)) Or _
Len(Trim(objExcel.Cells(nindex, aFields(X).ColumnNumber + 1))) = 0
Then
T_ECSALES.Recordset(aFields(X).AccessFieldName) = 0
Else
T_ECSALES.Recordset(aFields(X).AccessFieldName) = objExcel.Cells (nindex, aFields(X).ColumnNumber + 1)
End If
Next

T_ECSALES.Recordset.Update

nindex = nindex + 1
ProgressBarSALESImport.Value = nindex

Loop

objExcel.Quit
Set objExcel = Nothing


All times are GMT -4. The time now is 08:04 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.