• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

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

  • 4
  • 3
1 Solution
NorieVBA ExpertCommented:
Which version of Excel are you using?
dougfosterNYCAuthor Commented:
NorieVBA ExpertCommented:
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.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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.
NorieVBA ExpertCommented:

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 ))
dougfosterNYCAuthor Commented:
I forgot to close this question.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now