Help with slow running XL Automation Code
in [VB.NET]
|
From: Bill Schanks on 11 Mar 2008 17:14 I have this code that takes ~3 Mins to run while automating an XL Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection. So it is adding Named ranges on 8,000+ Cells. Is this just as good as it's going to get? Imports Excel = Microsoft.Office.Interop.Excel <<Snip>> With XL .Application.Calculation = Excel.XlCalculation.xlCalculationManual .Application.ScreenUpdating = False <<Snip>> Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion, Excel.Range) .Range("B3").Select() For iRow = 2 To CShort(rngSelection.Rows.Count) For iColumn = 2 To CShort(rngSelection.Columns.Count) .ActiveWorkbook.Names.Add(Name:="_" _ & Trim(Replace(CType(.ActiveCell.Value, String), "-", "_")) & "_" _ & CType(.Range("O2").Offset(0, - rngSelection.Columns.Count + iColumn + 1).Value, String) _ , RefersToR1C1:=.ActiveCell.Offset(0, iColumn - 1)) Next iColumn .ActiveCell.Offset(1, 0).Select() Next iRow <<Snip>>
From: aaron.kempf on 12 Mar 2008 12:21 I'm sorry did you say you're making 8,000 named ranges? -Aaron On Mar 11, 2:14 pm, Bill Schanks <wscha...(a)gmail.com> wrote: > I have this code that takes ~3 Mins to run while automating an XL > Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection. > So it is adding Named ranges on 8,000+ Cells. Is this just as good as > it's going to get? > > Imports Excel = Microsoft.Office.Interop.Excel > <<Snip>> > With XL > .Application.Calculation = > Excel.XlCalculation.xlCalculationManual > .Application.ScreenUpdating = False > <<Snip>> > Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion, > Excel.Range) > .Range("B3").Select() > For iRow = 2 To CShort(rngSelection.Rows..Count) > For iColumn = 2 To > CShort(rngSelection.Columns.Count) > > .ActiveWorkbook.Names.Add(Name:="_" _ > & Trim(Replace(CType(.ActiveCell.Value, > String), "-", "_")) & "_" _ > & CType(.Range("O2").Offset(0, - > rngSelection.Columns.Count + iColumn + 1).Value, String) _ > , RefersToR1C1:=..ActiveCell.Offset(0, > iColumn - 1)) > > Next iColumn > .ActiveCell.Offset(1, 0).Select() > Next iRow > <<Snip>>
From: Bill Schanks on 12 Mar 2008 12:58 Yes ... It's a long story. I took over support of this process and the end result is that are multiple spreadsheets that link to this spreadsheet via a named range. It's not perfect and needs to be re- written. With my current set of projects it's just going to happen any time soon. So for now, I just need to make the current process work. On Mar 12, 11:21 am, "aaron.ke...(a)gmail.com" <aaron.ke...(a)gmail.com> wrote: > I'm sorry did you say you're making 8,000 named ranges? > > -Aaron > > On Mar 11, 2:14 pm, Bill Schanks <wscha...(a)gmail.com> wrote: > > > I have this code that takes ~3 Mins to run while automating an XL > > Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection. > > So it is adding Named ranges on 8,000+ Cells. Is this just as good as > > it's going to get? > > > Imports Excel = Microsoft.Office.Interop.Excel > > <<Snip>> > > With XL > > .Application.Calculation = > > Excel.XlCalculation.xlCalculationManual > > .Application.ScreenUpdating = False > > <<Snip>> > > Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion, > > Excel.Range) > > .Range("B3").Select() > > For iRow = 2 To CShort(rngSelection.Rows.Count) > > For iColumn = 2 To > > CShort(rngSelection.Columns.Count) > > > .ActiveWorkbook.Names.Add(Name:="_" _ > > & Trim(Replace(CType(.ActiveCell.Value, > > String), "-", "_")) & "_" _ > > & CType(.Range("O2").Offset(0, - > > rngSelection.Columns.Count + iColumn + 1).Value, String) _ > > , RefersToR1C1:=.ActiveCell.Offset(0, > > iColumn - 1)) > > > Next iColumn > > .ActiveCell.Offset(1, 0).Select() > > Next iRow > > <<Snip>>
From: Steve Gerrard on 12 Mar 2008 21:36 Bill Schanks wrote: > I have this code that takes ~3 Mins to run while automating an XL > Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection. > So it is adding Named ranges on 8,000+ Cells. Is this just as good as > it's going to get? > That's 8,000 inter-process marshals and calls. It's going to stay slow. Any chance you can set this up so the code runs as a macro within the Excel workbook? It would go much faster there. Maybe You could even inject the macro into the workbook, then call it.
From: Bill Schanks on 13 Mar 2008 13:44
<mynameh...(a)comcast.net> wrote: > That's 8,000 inter-process marshals and calls. It's going to stay slow. > > Any chance you can set this up so the code runs as a macro within the Excel > workbook? It would go much faster there. Maybe You could even inject the macro > into the workbook, then call it. Actually that's were it is now, and I am pulling it out for other reasons. But I can setup new code within the book and then call that macro.Thanks. |