VB6 extremely slow creating excel file.

I have a msflexgrid that im pulling data from to create an excel file, and it is running extremely slow when I have thousands of rows. In one example I had around 33,000 rows and it took almost 30 minutes to create the excel file! Completely unacceptable.

Here is the code I am using to populate the excel file:

vb Code:

Dim oExcelApp   As Excel.Application
Dim oWs         As Excel.Worksheet
Dim oWb         As Excel.Workbook
Dim ExcelFileName As String
Dim X As Long
 
ExcelFileName = "text.xls"
 
    If Trim(ExcelFileName) <> "" Then
        If Dir(ExcelFileName) <> "" Then Kill (ExcelFileName)
        Set oExcelApp = CreateObject("EXCEL.APPLICATION")
        oExcelApp.Visible = False
        Set oWb = oExcelApp.Workbooks.Add
        Set oWs = oExcelApp.Worksheets(1)
        
        oExcelApp.Columns("A").ColumnWidth = 15
        oExcelApp.Columns("B").ColumnWidth = 15
        oExcelApp.Columns("C").ColumnWidth = 25
        oExcelApp.Columns("D").ColumnWidth = 40
        oExcelApp.Columns("E").ColumnWidth = 15
        
        oWs.Cells(1, 1).Value = "Field1"
        oWs.Range("A1:A1").Select
        oWs.Range("A1:A1").Activate
        oWs.Range("A1:A1").Font.Bold = True
        
        oWs.Cells(1, 2).Value = "Field2"
        oWs.Range("B1:B1").Select
        oWs.Range("B1:B1").Activate
        oWs.Range("B1:B1").Font.Bold = True
    
        oWs.Cells(1, 3).Value = "Field3"
        oWs.Range("C1:C1").Select
        oWs.Range("C1:C1").Activate
        oWs.Range("C1:C1").Font.Bold = True
        
        oWs.Cells(1, 4).Value = "Field4"
        oWs.Range("D1:D1").Select
        oWs.Range("D1:D1").Activate
        oWs.Range("D1:D1").Font.Bold = True
        
        oWs.Cells(1, 5).Value = "Field5"
        oWs.Range("E1:E1").Select
        oWs.Range("E1:E1").Activate
        oWs.Range("E1:E1").Font.Bold = True    
    
        For X = 1 To (MSFlexGrid1.Rows - 1)
            oWs.Cells(X + 1, 1).Value = MSFlexGrid1.TextMatrix(X, 0)
            oWs.Cells(X + 1, 2).Value = MSFlexGrid1.TextMatrix(X, 1)
            oWs.Cells(X + 1, 3).Value = MSFlexGrid1.TextMatrix(X, 2)
            oWs.Cells(X + 1, 4).Value = MSFlexGrid1.TextMatrix(X, 3)
            oWs.Cells(X + 1, 5).Value = MSFlexGrid1.TextMatrix(X, 4)
        Next X
        
        oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Select
        oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Activate
        oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).NumberFormat = "$#,##0.00"
        
        oWb.SaveAs ExcelFileName
        
        oExcelApp.Quit
        Set oWs = Nothing
        Set oWb = Nothing
        Set oExcelApp = Nothing
    End If

I assume the slow part is looping through each row and manually populating it, but I am unsure of a better way to do it, and my google searches havent turned up anything.

Please help!

Thanks.

Ok two Suggestions...

1) This

Quote:

---

oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Select
oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Activate
oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).NumberFormat = "$#,##0.00"

---

can also be written as

Code:

---

oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).NumberFormat = "$#,##0.00"

---

Similarly this

Quote:

---

oWs.Range("A1:A1").Select
oWs.Range("A1:A1").Activate
oWs.Range("A1:A1").Font.Bold = True

---

can be written as

Code:

---

oWs.Range("A1:A1").Font.Bold = True

---

and so on....

2) Instead of exporting to excel, export to a comma de-limited text file and then import it in excel... I feel that would be faster... I could be wrong... but give it a shot...

We will take it from there.... :)

Koolsid, Thanks for the reply! I like the idea about exporting to a text delimited file. It is a simple solution, and a quick fix that I over looked!

I am still curious why excel is so fricking slow, and would like to know if there is a better way to actually work with excel in this manner, so if anyone knows please satisfy this curious cat! :)

I have a report that is generated off of a query run against SQL Server 2000. The query itself runs instantanously and brings back less than 1000 records.

Dumping the resulting recordset, formatting and displaying the result report in Excel takes anywhere between 4 to 5 minutes.

Quote:

---

Originally Posted by Hack

I have a report that is generated off of a query run against SQL Server 2000. The query itself runs instantanously and brings back less than 1000 records.

Dumping the resulting recordset, formatting and displaying the result report in Excel takes anywhere between 4 to 5 minutes.

---

Are you doing A LOT of formatting? It sounds like you are suggesting that it is the nature of excel to be slow in this regard, and I assume by your status, and amount of posts that you know what you are talking about. ;-) It doesnt take all that long to export a thousand rows to excel for me, under a minute usually, but 30000 is breaking me.

I wonder if it has more to do with the formatting than the actual populating of individual cells?

I have found obscure references of directly assigning an array to an excel spreadsheet, but have not been able to find any clear examples that apply to my situation.

Does anyone know how to do this, and would this help in my situation?

Quote:

---

33,000 rows

---

Is a hell lot of rows for Excel when you are trying to write it via code from a Flex... When I said importing the textfile, I mean opening the text file from Excel and then using the Text to Columns property to rearrange data, which I feel is much much faster....

For 33,000 rows, when you are trying to write it via code from a Flex, I am not aware of a faster solution than what I suggested... I am sorry :)

Quote:

---

Originally Posted by guht

Are you doing A LOT of formatting? It sounds like you are suggesting that it is the nature of excel to be slow in this regard,

---

Yes, I am....records are being broken down into individual categories and subtotals run on those as well as a running total on the whole in for 4 different columns of numbers.

Selecting and Activating objects is slow, so changing that as koolsid suggested should improve things a bit (but not a huge amount, because you only do it 6 times in total!).


Putting data into a cells one at a time is also slow (presumably due to checks for formulas etc that refer to the cell), and switching to the array based method should make it noticeably faster - perhaps 10 times faster.

You can find an explanation and example of how to do that in my Excel Tutorial - link in my signature.