Reading Excel slowly

[AOLBoy]

I have a vb application that opens and reads a worksheet in Excel.

The process is quite slow. I was wondering if there are any tips or pointers on speeding up the process.

Thanks

 

[HarleyQuinn]

Could you show us how you've coded this process and an idea of the volumes of data we're dealing with?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AOLBoy]

Here is the code I am using

briefly - i m reading the xl sheet to determine the number of records so that I can use a progress bar, then I am reading the xl sheet to derive information from selected rows and then populating the arrays in the sub.

The excel file I am accessing has about 10 worksheets. The one I am reading has about 50,000 rows. In total uncompressed the file is about 30 megabytes.

Dim Synd(5000) As String
Dim Year(5000) As Integer
Dim Ccy(5000) As String
Dim SDate(5000) As Date
Dim Amt(5000) As Double
Dim Mtc(5000) As String

Dim ArrCnt As Long
Dim cmatch As Long
Dim Totalmm1 As Long
Dim Totalmm2 As Long

Dim totxlrecs As Long

Dim cnx As New ADODB.Connection
Dim RS1 As ADODB.Recordset
Dim SQL1 As String

Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Dim ARow As Long

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWorkbook = ExcelApp.Workbooks.Open("CashBook")
ExcelApp.Visible = False
Set ExcelSheet = ExcelWorkbook.Worksheets("WS")

totxlrecs = 0
ArrCnt = 0
'
' get a count of records required
'
Lp1:
totxlrecs = totxlrecs + 1
If Trim(ExcelSheet.Cells((totxlrecs + 7), 1).Value) = "" Then ' finished reading XL
GoTo lpz
End If
GoTo Lp1
lpz:

ProgressBar1.Visible = True
ProgressBar1.Max = totxlrecs

ARow = 7

LoopA:
ARow = ARow + 1
ProgressBar1.Value = ProgressBar1.Value + 1
ProgressBar1.Refresh
End If


If Trim(ExcelSheet.Cells(ARow, 1).Value) = "" Then ' finished reading XL
GoTo LoopAEnd
End If

If Trim(ExcelSheet.Cells(ARow, 10).Value) <> "755" Then 'not reqd charge type, ignore
GoTo LoopA
End If

ArrCnt = ArrCnt + 1
Amt(ArrCnt) = ExcelSheet.Cells(ARow, 9).Value
Synd(ArrCnt) = ExcelSheet.Cells(ARow, 1).Value
Year(ArrCnt) = ExcelSheet.Cells(ARow, 5).Value
Ccy(ArrCnt) = ExcelSheet.Cells(ARow, 3).Value
SDate(ArrCnt) = ExcelSheet.Cells(ARow, 2).Value
GoTo LoopA
LoopAEnd:

GoTo Terminate

ErrorHandler1:
Select Case Err.Number
Case Else
MsgBox (Err.Number & " - " & Err.Description)
End Select

Terminate:

ExcelWorkbook.Close savechanges:=False
ExcelApp.Quit
Set ExcelApp = Nothing
Set ExcelWorkbook = Nothing
Set ExcelSheet = Nothing

 

[JoeAtWork]

Instead of looping through each row until you find an empty cell, you can probably figure out the row count with the worksheet's UsedRange property. To quote my Office VB Programmer's Guide, "The used range is bounded by the farthest upper-left and farthest lower-right nonempty cells on a worksheet".

So you could probably do something like:

totxlrecs = ExcelSheet.UsedRange.Rows.Count

Also, since you already know how many rows are used, there is no need for this part:

If Trim(ExcelSheet.Cells(ARow, 1).Value) = "" Then       ' finished reading XL
        GoTo LoopAEnd
End If

You are doing an extra read that is not necessary. Just have it loop until you get to row = totxlrecs + 1.