Excel loop through cell range slow in SSIS

I have an Excel vba app that I have used for years, which builds reports from data it retrieves from a Sql Server warehouse.  It has run tens of thousands of reports.

Now I'm pulling the core vb code over to SSIS and building a script to do the same operation.  It runs well overall but one routine is very slow.  It works, but is super slow on big files, and I have some with hundreds of thousands of rows..

I am using VB script 2012, and it is essentially the exact same subroutine as the vba app, and can't figure out why it is so much slower.  I am turning off screen refresh and calculations, so that isn't the issue.

Here is the core loop that is slower.  I'm alternating background color on a column value, so when the value changes, it toggles back to the background color, or turns it off, until it hits a blank value.

Can anybody guess why this is slower in SSIS? I am referencing Microsoft.Office.Interop.Excel...

                    If UCase(.Cells(iRow, iTriggerCol).Value) <> UCase(.Cells(iStartRow, iTriggerCol).Value) Then
                        'change, so switch
                        bHasChanged = True
                        oRng = .Range(oWS.Cells(iStartRow, 1), oWS.Cells(iRow - 1, iLastCol))
                        If bColorMe Then
                            oRng.Interior.Color = 16772300  'light blue
                            bColorMe = False
                            bColorMe = True
                        End If
                        With oWS.Range(oWS.Cells(iRow, 1), oWS.Cells(iRow, iLastCol)).Borders(-4160)
                            .LineStyle = 1 'xlContinuous
                            .Color = RGB(0, 0, 0)
                            .Weight = 2 'xlThin
                        End With

                        iStartRow = iRow
                    End If

                    'drop out if there are no changes after a thousand rows...
                    If bHasChanged = False And iRow > 1000 Then Exit Do

                    'If .Cells(iRow, iTriggerCol).Value = "" Then Exit Do
                    If iRow = iLastRow Then Exit Do

                    iRow = iRow + 1

                    If .Cells(iRow, iTriggerCol).Value = "" Then Exit Do

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Which version of Excel are you using?
dougfosterNYCAuthor Commented:
NorieAnalyst Assistant Commented:
Was just wondering if Excel communicating with the printer could be what's slowing things down

If that was the case in later versions it's possible to turn off printer communication.
Application.PrinterCommunication = False
 ' your code
Application.PrinterCommunication = True

Open in new window

Not sure if that would be the problem but it might be something to try.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

dougfosterNYCAuthor Commented:
Hi Norie.

No, that's not it.  I'm not doing anything that would try to communicate with the printer.  That's a new one to me...

I was looking for a more efficient way to approach the loop.  I don't know if there is.  I read somewhere something about saving the range object as a variant, but that seems odd and not obvious how that would help.  

But a big clue is that in VBA it is very fast, but in VB it isn't.
NorieAnalyst Assistant Commented:

If you were only dealing with the values in the range, eg doing calculation, then you could use a variant array but since you are applying formatting that's not possible.

You say when the code is in VBA it runs faster, have you considered keeping it there and calling it from the VB/VBScript?

Assuming you had the code in an Excel workbook then you could easily do that using Application.Run.
dougfosterNYCAuthor Commented:
hmmm.  that is an interesting thought.  

This is for a large corporation, and will end up on a production server, so it has strict rules.  But, since this is an automated process, perhaps I could save an unformatted file and then launch a file and run the vba script....

but that is a lot of contortions.  Why would it be slower in this case?  It doesn't really make sense to me.  I'm opening a workbook and running the vb just as I would be in vba... odd....
Aleksandr M.EconomistCommented:
Although I'm not specializing in VB, but only in VBA, I suppose that the main ways of optimization are mostly common for both of them. And the only way of improving performance which i can see now is smth you have written already - load all the values of iTriggerCol column into a variant like this:
With oWS
    vTrigger = .Range(.Cells(iStartRow, iTriggerCol), .Cells(iLastRow, iTriggerCol)).Value
End With

Open in new window

And then I would go through it (2dimmensional array), but not through a complex Excel object structure.
Also i would put "Until iRow = iLastRow" after "Loop" keyword. it shouldn't affect productivity i suppose (possibly will - i'm not aware of VB compiler nuances), but should make code a couple of lines shorter ))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
I forgot to close this question.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.