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

How to reduce the time

chandru8
100+
P: 145
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
Jun 5 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Dim LastRow as Long
  2. ...
  3. LastRow = xlsheet.Range("A1").SpecialCells(xlLastCell).Row
  4. FOR intI = 2 to LastRow
  5. ...
  6. 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.

Expand|Select|Wrap|Line Numbers
  1. Const NDataCells = 7
  2. DIM ColumnRef as Integer
  3. DIM Celldata(NDataCells) as Variant
  4. ...
  5. FOR ColumnRef = 1 to NDataCells
  6.     Celldata(ColumnRef) = xlsheet.cells(intI, ColumnRef)
  7. 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
Jun 5 '08 #2

Expert 100+
P: 487
You can make all assignment statements as a single statement for strSql.
1. Get and filter files in first command button.

Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlbook As Excel.Workbook
  3. Dim xlsheet As Excel.Worksheet
  4. Dim path
  5. Dim intI As Integer
  6. cdFiles.Filter = "Only Excel files (*.xls)|*.xls"
  7. cdFiles.ShowOpen
  8. path = cdFiles.FileName
  9. If Len(cdFiles.FileName) = 0 Then
  10. cmdExcelupload.Enabled = False
  11. optExcel.Value = False
  12. Exit Sub
  13. End If
2. Then validate and create work book object there.
Expand|Select|Wrap|Line Numbers
  1. Set xlApp = New Excel.Application
  2. Set xlbook = xlApp.Workbooks.Open(path)
  3. Set xlsheet = xlApp.Worksheets("sheet1")
3. Get conformation to collect data from excel in another Command button.
Expand|Select|Wrap|Line Numbers
  1. lblProcessing.Visible = True
  2. ssDebitcardInformation.Enabled = False
  3. intI = 2
Here use for loop instead Do loop
Expand|Select|Wrap|Line Numbers
  1. Do Until xlsheet.Cells(intI, 1) = ""
  2.  
‘Do All this as a single assignment statement.
Expand|Select|Wrap|Line Numbers
  1.  
  2. strSql = "Insert into InputExcelData values ("
  3. strSql = strSql & "'" & intI - 1 & "' ," 
  4. strSql = strSql & "'" & xlsheet.Cells(intI, 1) & "' ," 
  5. strSql = strSql & "'" & xlsheet.Cells(intI, 2) & "' ," 
  6. strSql = strSql & "#" & xlsheet.Cells(intI, 3) & "# ," 
  7. strSql = strSql & "'" & xlsheet.Cells(intI, 4) & "' ," 
  8. strSql = strSql & "'" & xlsheet.Cells(intI, 5) & "' ," 
  9. strSql = strSql & "#" & xlsheet.Cells(intI, 6) & "# ," 
  10. strSql = strSql & "'" & xlsheet.Cells(intI, 7) & "' ," 
  11. Set rs = objCon.Execute(strSql)
  12. intI = intI + 1
  13. Debug.Print intI
‘Use the keyword DoEvents to alow the user to do some other operation instead hang
Expand|Select|Wrap|Line Numbers
  1. DoEvents
  2.  
then complite your (for) loop
Expand|Select|Wrap|Line Numbers
  1. Loop
  2. xlbook.Close
  3. Set xlApp = Nothing
  4. ERRORHANDLER:
  5. If Err.Number <> 0 Then
  6. If Err.Number = 9 Then
  7. MsgBox "", vbCritical
  8. Else
  9. Call err1
  10. End If
  11. '
  12. End If
Jun 5 '08 #3

kadghar
Expert 100+
P: 1,295
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):

Expand|Select|Wrap|Line Numbers
  1. dim a as variant
  2. with xlApp.worksheets("sheet1")
  3.     a = .range(.cells(2,1),.cells(2,1).end(-4121))
  4. end with
  5. for intI = 1 to ubound(a)
  6.     strSql = "Insert into InputExcelData values ("
  7.     strSql = strSql & "'" & intI & "' ," 
  8.     strSql = strSql & "'" & a(intI, 1) & "' ," 
  9.     strSql = strSql & "'" & a(intI, 2) & "' ," 
  10.     strSql = strSql & "#" & a(intI, 3) & "# ," 
  11.     strSql = strSql & "'" & a(intI, 4) & "' ," 
  12.     strSql = strSql & "'" & a(intI, 5) & "' ," 
  13.     strSql = strSql & "#" & a(intI, 6) & "# ," 
  14.     strSql = strSql & "'" & a(intI, 7) & "' ," 
  15.     Set rs = objCon.Execute(strSql)
  16.     Debug.Print intI
  17. 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)
Jun 5 '08 #4

Post your reply

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