When a machine slows down ...

[George1111]

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,

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

[dglienna]

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

[George1111]

False alarm

Best I have been able to save is about 5 seconds out of 65 seconds

[vb5prgrmr]

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

[sotoasty]

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.

[George1111]

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

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

[sotoasty]

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.

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.

    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....

                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)"

[George1111]

Thanks sotoasty - really neat !