-
October 15th, 2009, 09:36 PM
#1
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.
-
October 16th, 2009, 12:10 AM
#2
Re: When a machine slows down ...
Try setting the ScreenUpdating property of the Excel Application object
to FALSE before loading
-
October 16th, 2009, 05:32 AM
#3
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.
-
October 16th, 2009, 06:33 AM
#4
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
-
October 16th, 2009, 08:12 AM
#5
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.
-
October 16th, 2009, 09:11 AM
#6
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.
-
October 16th, 2009, 12:47 PM
#7
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
-
October 16th, 2009, 07:42 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|