When a machine slows down ...
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: When a machine slows down ...

  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,853

    When a machine slows down ...

    I have a process which selects data from an Access database, organises it, then populates some Excel worksheets

    This has always been a reasonably slow process but lately it has become slower and slower

    Take 2: ....

    The SQL Selections and opening of the Excel Objects are not the big bottle necks


    The big delay occurs when I populate the Excel Form with a large number of items

    eg,

    Code:
    msExcelWorksheet.Cells(SheetRow, SheetCol) = StockCode
    I know Excel is not a Flexgrid, but I can recall the joy when I discovered FlexGrid.Redraw = False (which speeded things up incredibly)

    Appreciate any ideas
    Last edited by George1111; October 15th, 2009 at 10:32 PM.

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: When a machine slows down ...

    Try setting the ScreenUpdating property of the Excel Application object
    to FALSE before loading
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,853

    Re: When a machine slows down ...

    False alarm

    Best I have been able to save is about 5 seconds out of 65 seconds
    Last edited by George1111; October 16th, 2009 at 05:36 AM.

  4. #4
    Join Date
    Apr 2009
    Posts
    394

    Re: When a machine slows down ...

    How about not showing the application until after you are done with adding the information...

    what is it application.visible = false or something like that (me old addled brain can't remember now???)



    Good Luck

  5. #5
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    Re: When a machine slows down ...

    I don't know if this is possible for what you are doing, but could you format a string of text, put it on the clipboard, then paste it into Excel. I once had an excel process that took about 2 minutes, setting each cell, when I started generating a string out of the data and pasting into excel, the time went down to seconds.

  6. #6
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,853

    Re: When a machine slows down ...

    Have tried Visible / Not Visible - no difference unfortunately

    I'm intrigued with the Clipboard approach

    I know how to put something on the clipboard and then paste it

    How would I select the excel cell ?

    Or do you select the first cell of the row, using
    Code:
    msExcelWorksheet.Cells(SheetRow, SheetCol) = StockCode
    then Paste the rest of the cells in that row. (also doing a Tab between columns ?)

    This sounds interesting, thanks
    Last edited by George1111; October 16th, 2009 at 09:13 AM.

  7. #7
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    Re: When a machine slows down ...

    Here is the code I used to generate my string. I was displaying the info on a MSFlexGrid so it was easy to generate a string and port it to excel.

    Code:
    Private Sub CopyData()
        Dim strData As String
        Dim nRowIndex As Integer
        Dim nColIndex As Integer
        
        strData = ""
        Clipboard.Clear
        For nRowIndex = 0 To grdDisplay.Rows - 1
            For nColIndex = 0 To grdDisplay.Cols - 1
                strData = strData & grdDisplay.TextMatrix(nRowIndex, nColIndex) & vbTab ' Delimit with tabs
            Next nColIndex
            strData = Mid(strData, 1, Len(strData) - 1) ' Delete last tab char
            strData = strData & vbCrLf ' Delimit new rows with new line
        Next nRowIndex
        Clipboard.SetText strData ' Copy it to clipboard
    
    End Sub
    Unfortunately, the code for the paste I don't have now. however, I have it in .NET.


    Code:
        Public Sub PasteData(ByVal Value As String)
            mvarExcel.ActiveSheet.Range("A1").Select()
            mvarExcel.ActiveSheet.Paste()
        End Sub
    Where mvarExcel is...

    Private mvarExcel As Excel.Application

    You might not even have to make changes for it. Just start excel, select the cell you want to paste into and do the paste. VBTab should put it in different columns, and vbcrlf for new rows.


    ETA: You can also do excel functions. like....

    Code:
                    ExcelString &= "TOTALS" & vbTab & "=SUM(B8:B30)" & vbTab & "=SUM(C8:C30)" & vbTab & "=SUM(D8:D30)" & vbTab & "=SUM(E8:E30)" & vbTab & "=SUM(F8:F30)" & vbTab & vbTab & vbTab & "=SUM(I8:I30)" & vbTab & "=SUM(J8:J30)" & vbTab & "=SUM(K8:K30)" & vbTab & "=SUM(L8:L30)" & vbTab & "=SUM(M8:M30)"
    Last edited by sotoasty; October 16th, 2009 at 12:52 PM. Reason: Add additional stuff

  8. #8
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,853

    Re: When a machine slows down ...

    Thanks sotoasty - really neat !

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center