How to reduce the time

[chandru8]

Hi to all,
Iam using vb6.0,excel 2003 and access , by vb6.0 iam retrieve the data from excel and store it in the msaccess 2003.

the problem is i need to upload about 20000 or above it take about 10 min and more.User asking me to reduce the time .

can any one help me in this...,

here is my code......................................

Private Sub cmdExcelupload_Click()

On Error GoTo ERRORHANDLER

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim path
Dim intI As Integer
cdFiles.Filter = "Only Excel files (*.xls)|*.xls"
cdFiles.ShowOpen
path = cdFiles.FileName
If Len(cdFiles.FileName) = 0 Then
cmdExcelupload.Enabled = False
optExcel.Value = False
Exit Sub
End If

Set xlApp = New Excel.Application
Set xlbook = xlApp.Workbooks.Open(path)
Set xlsheet = xlApp.Worksheets("sheet1")

'Processing label box
lblProcessing.Visible = True
ssDebitcardInformation.Enabled = False

intI = 2
Do Until xlsheet.Cells(intI, 1) = ""
strSql = "Insert into InputExcelData values ("

strSql = strSql & "'" & intI - 1 & "' ,"
strSql = strSql & "'" & xlsheet.Cells(intI, 1) & "' ,"
strSql = strSql & "'" & xlsheet.Cells(intI, 2) & "' ,"
strSql = strSql & "#" & xlsheet.Cells(intI, 3) & "# ,"
strSql = strSql & "'" & xlsheet.Cells(intI, 4) & "' ,"
strSql = strSql & "'" & xlsheet.Cells(intI, 5) & "' ,"
strSql = strSql & "#" & xlsheet.Cells(intI, 6) & "# ,"
strSql = strSql & "'" & xlsheet.Cells(intI, 7) & "' ,"
Set rs = objCon.Execute(strSql)
intI = intI + 1
Debug.Print intI
Loop


xlbook.Close
Set xlApp = Nothing

ERRORHANDLER:
If Err.Number <> 0 Then

If Err.Number = 9 Then

MsgBox "", vbCritical

Else
Call err1
End If
'
End If
End Sub

[Stewart Ross]

Hi. The speed losses could be to do with the loop itself, the references to the worksheet cells within the loop, and the repeated running of the INSERT INTO statement. Hard to tell, because otherwise you have minimum interaction with the Excel sheet, and you are not killing off the speed by using unnecessary sheet or cell selections (which would dramatically affect performance).

Some suggestions:

* If the Excel sheet does not have multiple datasets separated by blank rows you could replace the DO loop with a FOR loop, as follows:

Dim LastRow as Long
...
LastRow = xlsheet.Range("A1").SpecialCells(xlLastCell).Row
FOR intI = 2 to LastRow
...
Next intI

FOR loops can be proven to run nearly twice as fast as DO loops, so this may help. Won't work if you have non-contiguous blocks of data - which I was thinking you might have from your DO Until checking for a blank cell.

Next suggestion:

* use a FOR loop to read the seven cells from your worksheet into an array in memory instead of doing so within the build string of the SQL statement.

Const NDataCells = 7
DIM ColumnRef as Integer
DIM Celldata(NDataCells) as Variant
...
FOR ColumnRef = 1 to NDataCells
    Celldata(ColumnRef) = xlsheet.cells(intI, ColumnRef)
NEXT ColumnRef

All row interactions with Excel are then done in one small loop without any overhead introduced by the string building. This might be a small effect overall, but even so it may be worth trying. You can then use the Celldata array in memory as the source for your SQL build string.

Final suggestion:

* instead of building a SQL Insert string which you run as many times as there are rows you could add the values directly within the loop by opening the recordset first (using an ADO recordset if you are using VB outside of Access), then using the .addnew method of the recordset within the loop. For each row you then set the field values directly instead of building the SQL string element, and use .update to store those values. You close the recordset again when the loop closes. If you look for Help on the Addnew method of an ADO recordset you will find a VB exemplar which shows how it is done.

I have a feeling that this approach could yield a substantial speed improvement over repeated use of INSERT INTO, as you are opening and closing the recordset just once, whereas the database engine, highly efficient though it is, has to open and close the recordset repeatedly when handling the INSERTs over and over again.

Hope one or more of these suggestions helps.

-Stewart

[CyberSoftHari]

You can make all assignment statements as a single statement for strSql.
1. Get and filter files in first command button.

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim path
Dim intI As Integer
cdFiles.Filter = "Only Excel files (*.xls)|*.xls"
cdFiles.ShowOpen
path = cdFiles.FileName
If Len(cdFiles.FileName) = 0 Then
cmdExcelupload.Enabled = False
optExcel.Value = False
Exit Sub
End If

2. Then validate and create work book object there.

Set xlApp = New Excel.Application
Set xlbook = xlApp.Workbooks.Open(path)
Set xlsheet = xlApp.Worksheets("sheet1")

3. Get conformation to collect data from excel in another Command button.

lblProcessing.Visible = True
ssDebitcardInformation.Enabled = False
intI = 2

Here use for loop instead Do loop

Do Until xlsheet.Cells(intI, 1) = ""
 

‘Do All this as a single assignment statement.

 
strSql = "Insert into InputExcelData values ("
strSql = strSql & "'" & intI - 1 & "' ," 
strSql = strSql & "'" & xlsheet.Cells(intI, 1) & "' ," 
strSql = strSql & "'" & xlsheet.Cells(intI, 2) & "' ," 
strSql = strSql & "#" & xlsheet.Cells(intI, 3) & "# ," 
strSql = strSql & "'" & xlsheet.Cells(intI, 4) & "' ," 
strSql = strSql & "'" & xlsheet.Cells(intI, 5) & "' ," 
strSql = strSql & "#" & xlsheet.Cells(intI, 6) & "# ," 
strSql = strSql & "'" & xlsheet.Cells(intI, 7) & "' ," 
Set rs = objCon.Execute(strSql)
intI = intI + 1
Debug.Print intI

‘Use the keyword DoEvents to alow the user to do some other operation instead hang

DoEvents
 

then complite your (for) loop

Loop
xlbook.Close
Set xlApp = Nothing
ERRORHANDLER:
If Err.Number <> 0 Then
If Err.Number = 9 Then
MsgBox "", vbCritical
Else
Call err1
End If
'
End If

[kadghar]

hi

actually it takes too long because reading data from excel takes too much time each time it's done

The trick is to read the data only once. To do this, have in mind that when you store a Range into a Variant, the Variant wont become a Variant/Range, but a 2 dimensioned Array with the Range's values!!

^.^

So, try something like this (it'll for sure make your code run in less than 1 minute):

dim a as variant
with xlApp.worksheets("sheet1")
    a = .range(.cells(2,1),.cells(2,1).end(-4121))
end with
for intI = 1 to ubound(a)
    strSql = "Insert into InputExcelData values ("
    strSql = strSql & "'" & intI & "' ," 
    strSql = strSql & "'" & a(intI, 1) & "' ," 
    strSql = strSql & "'" & a(intI, 2) & "' ," 
    strSql = strSql & "#" & a(intI, 3) & "# ," 
    strSql = strSql & "'" & a(intI, 4) & "' ," 
    strSql = strSql & "'" & a(intI, 5) & "' ," 
    strSql = strSql & "#" & a(intI, 6) & "# ," 
    strSql = strSql & "'" & a(intI, 7) & "' ," 
    Set rs = objCon.Execute(strSql)
    Debug.Print intI
next

HTH

Note: I used the Excel's VBA method 'END' with the parameter xlDown... but since there're no xl constants in VB6, i used its numeric value (-4121)