PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VB6 extremely slow creating excel file.-VBForums
Results 1 to 8 of 8

Thread: VB6 extremely slow creating excel file.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2008
    Posts
    23

    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:
    1. Dim oExcelApp   As Excel.Application
    2. Dim oWs         As Excel.Worksheet
    3. Dim oWb         As Excel.Workbook
    4. Dim ExcelFileName As String
    5. Dim X As Long
    6.  
    7. ExcelFileName = "text.xls"
    8.  
    9.     If Trim(ExcelFileName) <> "" Then
    10.         If Dir(ExcelFileName) <> "" Then Kill (ExcelFileName)
    11.         Set oExcelApp = CreateObject("EXCEL.APPLICATION")
    12.         oExcelApp.Visible = False
    13.         Set oWb = oExcelApp.Workbooks.Add
    14.         Set oWs = oExcelApp.Worksheets(1)
    15.        
    16.         oExcelApp.Columns("A").ColumnWidth = 15
    17.         oExcelApp.Columns("B").ColumnWidth = 15
    18.         oExcelApp.Columns("C").ColumnWidth = 25
    19.         oExcelApp.Columns("D").ColumnWidth = 40
    20.         oExcelApp.Columns("E").ColumnWidth = 15
    21.        
    22.         oWs.Cells(1, 1).Value = "Field1"
    23.         oWs.Range("A1:A1").Select
    24.         oWs.Range("A1:A1").Activate
    25.         oWs.Range("A1:A1").Font.Bold = True
    26.        
    27.         oWs.Cells(1, 2).Value = "Field2"
    28.         oWs.Range("B1:B1").Select
    29.         oWs.Range("B1:B1").Activate
    30.         oWs.Range("B1:B1").Font.Bold = True
    31.    
    32.         oWs.Cells(1, 3).Value = "Field3"
    33.         oWs.Range("C1:C1").Select
    34.         oWs.Range("C1:C1").Activate
    35.         oWs.Range("C1:C1").Font.Bold = True
    36.        
    37.         oWs.Cells(1, 4).Value = "Field4"
    38.         oWs.Range("D1:D1").Select
    39.         oWs.Range("D1:D1").Activate
    40.         oWs.Range("D1:D1").Font.Bold = True
    41.        
    42.         oWs.Cells(1, 5).Value = "Field5"
    43.         oWs.Range("E1:E1").Select
    44.         oWs.Range("E1:E1").Activate
    45.         oWs.Range("E1:E1").Font.Bold = True    
    46.    
    47.         For X = 1 To (MSFlexGrid1.Rows - 1)
    48.             oWs.Cells(X + 1, 1).Value = MSFlexGrid1.TextMatrix(X, 0)
    49.             oWs.Cells(X + 1, 2).Value = MSFlexGrid1.TextMatrix(X, 1)
    50.             oWs.Cells(X + 1, 3).Value = MSFlexGrid1.TextMatrix(X, 2)
    51.             oWs.Cells(X + 1, 4).Value = MSFlexGrid1.TextMatrix(X, 3)
    52.             oWs.Cells(X + 1, 5).Value = MSFlexGrid1.TextMatrix(X, 4)
    53.         Next X
    54.        
    55.         oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Select
    56.         oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).Activate
    57.         oWs.Range("E2:E" & (MSFlexGrid1.Rows - 1)).NumberFormat = "$#,##0.00"
    58.        
    59.         oWb.SaveAs ExcelFileName
    60.        
    61.         oExcelApp.Quit
    62.         Set oWs = Nothing
    63.         Set oWb = Nothing
    64.         Set oExcelApp = Nothing
    65.     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.
    Last edited by Hack; Apr 1st, 2009 at 07:25 AM. Reason: Added Highlight Tags

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: VB6 extremely slow creating excel file.

    Ok two Suggestions...

    1) This

    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

    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....
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2008
    Posts
    23

    Re: VB6 extremely slow creating excel file.

    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!

  4. #4
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: VB6 extremely slow creating excel file.

    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.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2008
    Posts
    23

    Re: VB6 extremely slow creating excel file.

    Quote Originally Posted by Hack View Post
    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?

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: VB6 extremely slow creating excel file.

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  7. #7
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: VB6 extremely slow creating excel file.

    Quote Originally Posted by guht View Post
    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.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,935

    Re: VB6 extremely slow creating excel file.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width