Need some new eyes for refactoring....

[Zmcpherson]

I wrote a bunch of VB/VBA and it seems to be running a little slow. The following process takes 20ish seconds to complete and I was wondering if anyone see's any areas that could be improved for better performance.

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim datenow As String = DateTime.Now.ToString("MMM dd")
        xlApp = New Excel.Application
        xlbook = xlApp.Workbooks.Open(xlspath)
        xlsheet1 = xlbook.Worksheets("Sheet1")
        xlbook.Sheets("Sheet1").Name = "Summary"
        xlbook.Sheets("Sheet2").delete()
        xlbook.Sheets("Sheet3").delete()
        With xlsheet1
             .Columns("K:J").delete()
             .Columns("A:A").delete()
             .Columns("B:B").insert()
             .Cells(1, 1).Copy()
             .Cells(1, 2).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
             .Cells(1, 2) = "Pic"
             .Cells(1, 8) = "APP"
             .Cells(1, 9) = "APP Version"
        End With
 
        Dim lastsheet = "Summary"
        For x = 2 To xlsheet1.Rows.Count
            If xlsheet1.Cells(x, 1).value = Nothing Then
                Exit For
            Else
                xlbook.Worksheets.Add(After:=xlbook.Worksheets(lastsheet))
                lastsheet = CStr(xlsheet1.Cells(x, 1).value)
                xlbook.Sheets(CStr("Sheet" & CStr(x - 1))).Name = lastsheet
                For y = 1 To xlsheet1.Columns.Count
                    If xlsheet1.Cells(1, y).value = Nothing Then
                        Exit For
                    Else
                        xlsheet1.Cells(1, y).Copy()
                        With xlbook.Sheets(lastsheet)
                            .cells(1, y).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
                            .cells(1, y) = xlsheet1.Cells(1, y).value
                            .cells(2, y) = xlsheet1.Cells(x, y).value
                        End With
                    End If
                Next
                With xlbook.Sheets(lastsheet)
                    .columns("B:B").delete()
                    .columns("A:k").HorizontalAlignment = Excel.Constants.xlLeft
                    .columns("A:k").VerticalAlignment = Excel.Constants.xlTop
                    .columns("A:k").wraptext = True
                    .columns("A:k").autofit()
                    .columns("i:i").columnwidth = 20
                    .columns("j:j").columnwidth = 60
                    .columns("K:K").columnwidth = 60
                    .rows("1:100").autofit()
                End With
                xlsheet1.Hyperlinks.Add(Anchor:=xlsheet1.Cells(x, 1), Address:="", SubAddress:="'" & lastsheet & "'!A1", TextToDisplay:=(lastsheet))
                Dim pix = 100
                Dim txt = 215
                For Each foundFile As String In My.Computer.FileSystem.GetFiles(ComboBox1.SelectedItem, FileIO.SearchOption.SearchTopLevelOnly, "*" & lastsheet & "*")
                    Dim extension = Path.GetExtension(foundFile).ToLower.ToString
                    If extension = ".log" Or extension = ".txt" Then
                        xlbook.Sheets(lastsheet).OLEObjects.Add(FileName:=foundFile, Link:=False, DisplayAsIcon:=True, IconFileName:=Environment.GetFolderPath(Environment.SpecialFolder.Windows) & "\Notepad.exe", IconIndex:=0, IconLabel:=Path.GetFileName(foundFile), Left:=265, Top:=txt, Width:=75, Height:=75)
                        txt += 80
                        xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
                    ElseIf extension = ".png" Or extension = ".jpg" Or extension = ".jpeg" Or extension = ".gif" Then
                        xlbook.Sheets(lastsheet).Shapes.AddPicture(foundFile, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue, 25, pix, 215, 350)
                        pix += 350
                        xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
                    End If
                Next
            End If
        Next
        With xlsheet1
            .Columns("D:D").delete()
            .Columns("J:L").delete()
            .Columns("C:C").delete()
            .Columns("A:K").autofit()
            .Columns("A:G").HorizontalAlignment = Excel.Constants.xlCenter
            .Cells(1, 8).HorizontalAlignment = Excel.Constants.xlCenter
            .Columns("A:H").VerticalAlignment = Excel.Constants.xlTop
        End With
        Dim sheet As Excel.Worksheet
        Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
        Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
        For Each sheet In xlbook.Worksheets
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = "&D &T"
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesTall = 2
            sheet.PageSetup.FitToPagesWide = 1
        Next
        xlsheet1.Activate()
        xlbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) & "\" & title.ToString)
CloseExcel()
End Sub

What the program does is load an exported excel report from another program and reconfigures it. First thing it does is it makes a new tab for each row, then it looks through a file for pictures or text documents containing the same name and attaches them. Finally, it changes the print settings and saves it to the desktop. I seem to have hit a small wall, and other then some syntax here and there Im not seeing much else.

Any and all suggestions are appreciated.

Zach

*edit - If anyone would like to see a sample unedited report or the finished product, let me know.

[kareninstructor]

Hello,

To the naked eye the code looks fine. One thing to try is ripping one part out, retry and see if that speeds up the process. First try without doing the image then try anything that does a copy or paste. If possible work on one chunk at a time.

[Zmcpherson]

alright I did that, and it seems that the part that is taking 18 of the 20ish seconds is this chunk...

Dim sheet As Excel.Worksheet
        Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
        Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
        For Each sheet In xlbook.Worksheets
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = "&D &T"
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesTall = 2
            sheet.PageSetup.FitToPagesWide = 1
        Next

Is there a better way than this?

[Zmcpherson]

*edit*

deleted, I solved this unrelated issue.

[kareninstructor]

alright I did that, and it seems that the part that is taking 18 of the 20ish seconds is this chunk...

Dim sheet As Excel.Worksheet
        Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
        Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
        For Each sheet In xlbook.Worksheets
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = "&D &T"
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesTall = 2
            sheet.PageSetup.FitToPagesWide = 1
        Next

Is there a better way than this?

Okay this is all guessing since when I work this type of operation it is with a third party library called Aspose Cells where there is no automation thus lighting fast.

I would try first setting the variable title and header to an empty string, does this speed this up? If so I think there is a bottle-neck with how each or one of the variables (title or header) are gotten. You could cut to the chase and do one at a time as per above and find out only one is the issue.

Second, get rid of the for/each for a second, do only one sheet. If there are say three sheets does cutting it down to one sheet take a third of the time?

Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
For Each sheet In xlbook.Worksheets
    sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
    sheet.PageSetup.LeftHeader = "&D &T"
    sheet.PageSetup.CenterHeader = header.ToString
    sheet.PageSetup.LeftFooter = title.ToString
    sheet.PageSetup.RightFooter = "&P/&N"
    sheet.PageSetup.Zoom = False
    sheet.PageSetup.FitToPagesTall = 2
    sheet.PageSetup.FitToPagesWide = 1
Next

Also I am assuming you have Option Strict set to Off since xlsheet1.Cells(2, 6).value returns an Object, not a String which means there is lating binding which when added up every time the run time engine needs to translate will slow things down. The trade off is when turning Option Strict On your code will need to change so it will compile. Also some code may very well not easily be made to work as before so if you do this I highly recommend backing up your project to another location before starting.

[Zmcpherson]

Alright, I ran a bunch of tests and here is what I have come up with.

To come up with the seconds I did...
dim EndTime = TimeNow.subtract(StartTime)


It takes 42.78 seconds to run through this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 3.53 seconds to run through this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 27.26 seconds to run though this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

and 15.23 seconds for this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It looks like most of the time is spent in the Date and time in the left header, the right footer, and the page orientation. The date and time are already a fixed string, why is it taking so long to do that? Are there anyways to cut any of the above down?

Its true, I usually do not keep option strict on when I am working with VBA. How would you write this with option strict on?:

Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value

or this?

With xlbook.Sheets(lastsheet)
                    .columns("B:B").delete()
                    .columns("i:i").columnwidth = 20
                    .columns("j:K").columnwidth = 60
                End With

[kareninstructor]

Alright, I ran a bunch of tests and here is what I have come up with.

To come up with the seconds I did...
dim EndTime = TimeNow.subtract(StartTime)


It takes 42.78 seconds to run through this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 3.53 seconds to run through this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 27.26 seconds to run though this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

and 15.23 seconds for this:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It looks like most of the time is spent in the Date and time in the left header, the right footer, and the page orientation. The date and time are already a fixed string, why is it taking so long to do that? Are there anyways to cut any of the above down?

Its true, I usually do not keep option strict on when I am working with VBA. How would you write this with option strict on?:

Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value

or this?

With xlbook.Sheets(lastsheet)
                    .columns("B:B").delete()
                    .columns("i:i").columnwidth = 20
                    .columns("j:K").columnwidth = 60
                End With

When in doubt not knowing how to do something with Excel use Google. I don't know what columns("B:B").delete() does for sure but would guess it deletes a column. So I type the following into Google vb.net excel delete column and look for this forum, StackOverflow or MSDN forums. As fate would have it the second link was MSDN forum with C Sharp code which at least to me seems good other than tunnelling (using more than one ".") so I converted it and ran the code as shown below, worked great. If I were to use this in my app I would de-tunnel the code.

Dim MyVar = xlWorkSheet.Range("A1", "B1")
MyVar.EntireColumn.Delete(Nothing)

To ensure disposal of objects

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

Crap, of course I cannot let the code above go with tunneling so here is a clean version meaning all objects are properly disposed.

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    Dim InitialPath As String = "C:\DotnetLand2010\Excel\ExcelDemo\bin\Debug"

    OpenFileDialog1.Title = "Please select a file to open"
    OpenFileDialog1.FileName = ""

    If IO.Directory.Exists(InitialPath) Then
        OpenFileDialog1.InitialDirectory = InitialPath
    Else
        OpenFileDialog1.InitialDirectory =
            System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
    End If

    OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"

    If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)

        xlApp.Visible = False

        If CheckBox1.Checked Then
            xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)
        Else
            xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
        End If

        Dim xlCells As Excel.Range = Nothing

        Dim xlRangeToRemove = xlWorkSheet.Range("A1:B1")
        Dim MyRange = xlRangeToRemove.EntireColumn
        MyRange.Delete(Nothing)
        xlWorkBook.SaveAs(OpenFileDialog1.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook)
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        If Not MyRange Is Nothing Then
            Marshal.FinalReleaseComObject(MyRange)
            MyRange = Nothing
        End If

        If Not xlRangeToRemove Is Nothing Then
            Marshal.FinalReleaseComObject(xlRangeToRemove)
            xlRangeToRemove = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
        MessageBox.Show("Done")
    End If
End Sub

One last thought, sometimes you will not find code for VB.NET, instead code such as this link. That site is a good one, if you look at his example for use in Excel and examine the VB.NET version they are close, we are using a Range and calling the same method, just in a different way.

Sub DeleteEmptyColumns(DeleteRange As Range)
' Deletes all empty columns in DeleteRange
' Example: DeleteEmptyColumns Selection
' Example: DeleteEmptyColumns Range("A1:Z1")
Dim cCount As Integer, c As Integer
    If DeleteRange Is Nothing Then Exit Sub
    If DeleteRange.Areas.Count > 1 Then Exit Sub
    With DeleteRange
        cCount = .Columns.Count
        For c = cCount To 1 Step -1
            If Application.CountA(.Columns(c)) = 0 Then 
                .Columns(c).EntireColumn.Delete
            End If
        Next c
    End With
End Sub

With all the above this is why I avoid using Excel automation but tinker with it for fun. Any time you have the $$$'s to spend for working with Excel libraries such as Aspose Cells pays for itself quickly and to boot does not require Excel to be installed.

So now you have a thought process of figuring out the other methods.

Hope this helps.

[Zmcpherson]

man this is going to look stupid but... i figured it out... needless to say, im downloadind aspose right now to give it a whirl...

turns out

With xlbook.Sheets(lastsheet)
                        .columns("B:B").delete()
                        .columns("i:i").columnwidth = 20
                        .columns("j:K").columnwidth = 60
                    End With

can be turned into this without Option Strict errors:

With xlsheetDef
                    .Range("B:B").Delete()
                    .Range("i:i").ColumnWidth = 20
                    .Range("j:K").ColumnWidth = 60
                End With

and ...

lastsheet = xlsheet1.Cells(x, 1).value

is going to be this....

lastsheet = Convert.ToString(CType(xlsheet1.Cells(x, 1), Excel.Range).Value)

I cant see this being any faster : P

[Zmcpherson]

Can I run multiple threads for this process?

Say I open 3 new threads that run different threadtasks()

and then I split the work (everything in the "for each" loop) up between the main thread and the three new threads. Would they complete the entire task faster?

[kareninstructor]

Can I run multiple threads for this process?

Say I open 3 new threads that run different threadtasks()

and then I split the work (everything in the "for each" loop) up between the main thread and the three new threads. Would they complete the entire task faster?

Only way to know is to try.

[Zmcpherson]

lol yeah it just freezes the computer to the point where a restart is necessary. Not a very useful program at that point : P .

Explain how this aspose thing works. I imported the dll like a com reference, Im guessing at the top of the class I use "Imports Aspose.cells". Now what? do I use the same syntax as Microsoft.Interop? ...

what would I need to change, say:

xlApp = New Excel.Application
xlbook = xlApp.Workbooks.Open(OpenFileDialog1.FileName)

to get it to work?

[kareninstructor]

You add Aspose Cells library as a reference to your project. Make sure Copy Local is set to True which means the dll will be placed into the same folder as your executable.

Example projects should be under C:\Program Files\Aspose\Aspose.Cells\Demos while help is under C:\Program Files\Aspose\Aspose.Cells\Help although when I need help I use online help at the Aspose web site.

If you purchase Aspose Cells then you need to include a license file with your app placed usually in the app executable folder. I use the following code to activate the license but you will not have that for the eval.

Module AsposeSupport
    Private LicenseFile As String = IO.Path.Combine(Application.StartupPath, "Aspose.Cells.lic")
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Sub PrepareCellsLibrary()
        If IO.File.Exists(LicenseFile) Then
            Dim license As Aspose.Cells.License = New Aspose.Cells.License
            license.SetLicense(LicenseFile)
        End If
    End Sub
End Module

I went with the cells library after working with their PDF kit library for creating PDF and extraction of data from existing PDF files.

[Zmcpherson]

Do you truly believe that this will compile that bit of code faster?

I really respect you and your opinion, and I appreciate everything you do on these boards, but I would hate to learn a whole new set of objects and then rewrite all of my code for an equal or for a very marginal increase in speed.

However, if your telling me this will change the way I do vb and vba forever, then Im on board for sure.

[kareninstructor]

I can guarantee Aspose Cells library is used properly you will see a difference in speed from opening an Excel file to work done within the file. With that said, the opposite is equally true, use Aspose Cells incorrectly and you will not see operations anything but marginally faster than automation.

One tip to working with Aspose Cells is not to simply jump into your project and code away. Instead, take time to learn working with the library, which is best done prior to purchasing the library, and when you cannot figure something out ask in their forums. This is great being able to work with this library to ensure it does what you want. This is also why having done project requirements and design prior to coding so there are no surprises.

[Ashaelon]

Just as a note on this, something I have run into with performance with VBA and Excel. If you are doing any type of cell decorating (ie changing text alignment, formats, colors, etc) you will see a major performance drop as Excel needs to make each change and repaint after each change. Something to think about is to set the Excel Application's ScreenUpdating property to false when making these types of changes. This will increase the performance quite a bit.

I had this same problem in an Excel tool I built. When I changed the text alignment in the cells, the code seemed to bog down, but when I set screenupdating to false, it seemed to fly right through it.

Just a thought.

[Zmcpherson]

I just tried using the ScreenUpdating property, I didnt see any increase in the over all speed of the code. That would of been awesome though, thanks for the thought.

[kareninstructor]

I just tried using the ScreenUpdating property, I didnt see any increase in the over all speed of the code. That would of been awesome though, thanks for the thought.

I have not used ScreenUpdating in .NET automation but did with VB6 with Excel and did seem to make a difference. You might take a look at this thread where several post suggest using Application.calculation=xlManual before doing your code then application.Calculation=xlAutomatic when done (check out the next to last reply).

[Zmcpherson]

Yeah, from everything Im reading on both .calculation and .ScreenUpdating it should make the code run faster. But my times are almost identical with both of those things on and and with both of those things off.

I did some more research and found this:

Dim bEvents As Boolean
Dim bAlerts As Boolean
Dim CalcMode As Long
Dim bScreen As Boolean
 
' save current settings
bEvents = Application.EnableEvents
bAlerts = Application.DisplayAlerts
CalcMode = Application.Calculation
bScreen = Application.ScreenUpdating
 
' disable events, alerts, automatic calculation & screen updating
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
 
' your code here
 
Then right before the End Sub, paste this:
    
' restore previous settings
With Application
    .EnableEvents = bEvents
    .DisplayAlerts = bAlerts
    .Calculation = CalcMode
    .ScreenUpdating = bScreen
End With

But I am still right around 45 seconds with no improvements. I guess that's just how long it takes to create headers, footers, and change page orientation.