Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reading Excel slowly

Status
Not open for further replies.

AOLBoy

IS-IT--Management
May 7, 2002
68
GB
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
 
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.
 
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


 
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:
Code:
totxlrecs = ExcelSheet.UsedRange.Rows.Count

Also, since you already know how many rows are used, there is no need for this part:
Code:
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.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top