Running out of memory during script execution

[boznian]

I am running a script to capture data, format, concatenate and paste to new worksheet. The methods I am using have always worked for me in other scripts but in this one after a few iterations I get a crash with an Excel "out of memory" notice. My computer has 4GB of RAM and plenty left.

The offending code is this:

' create HTML output string

Range("L101:L285").Select
Dim cl As Range
Dim myString As String
For Each cl In Selection
myString = myString & cl
Next cl

It will work all day on single iterations or even a couple times through. How or what do I need to clear out? Or use a different variable type?

Thanks

 

[VoG]

Maybe

myString = Join(Application.Transpose(Range("L101:L285")))

 

[boznian]

Thanks for the reply.

There must be more to it for your solution - if I insert that line I get "Runtime Automation Error 440" "The object invoked has disconnected from its clients."

    ' create HTML output string
    
    Range("L101:L285").Select
    Dim cl As Range
    Dim myString As String
    For Each cl In Selection
     myString = Join(Application.Transpose(Range("L101:L285")))
'    myString = myString & cl
    Next cl
    Range("G99") = myString

 

[Norie]

Do you have HTML in the cells you are concatenating or are you concatenating them to create HTML?

 

[boznian]

It is bits and pieces of HTML. For instance many cells are results of a "=E102" type formula, some are results of a "=CONCATENATE(E107,G107,H107)" type formula. I have 185 rows of various pieces of static and dynamic HTML that are combined to create the code for an entire webpage. These have always been combined using the

' create HTML output string

Range("L101:L285").Select
Dim cl As Range
Dim myString As String
For Each cl In Selection
myString = myString & cl
Next cl

code. As I say, it works fine every time but I have always been running it one iteration at a time, manually. Now I need to run it on a couple thousand lines and in my attempt to automate it, I get the out of memory message after 2 - 3 runs.

 

[Norie]

Usually a String can hold 2 billion and one characters (or something close to that) and an out of memory error would suggest you are exceeding some limit.

I can't imagine concatenating 180ish strings from cells would break the limit for a string.

What exactly are you trying to do?

If you are trying to create the HTML for an entire page have you considered writing directly to a text/HTML file.

Or are you already doing that?

 

[boznian]

I suppose it is possible that something else is causing my issue, but if I comment out the aforementioned code it seems to work fine. Again, the purpose of the script is to take individual pieces of information, paste to a table, format, copy and insert the appropriate parts into HTML to create the webpage. Here is the entire script:

Sub Macro83()
'
' Macro83 Macro
' Macro Created 03/29/11 by Mark
' Update and Publish All Item Descriptions
' FROM "BY MODEL" SHEET FOR YourWatchConnection.com

    Sheets("By Model").Select
    Range("B3").Select
    crow = ActiveCell.Row
    ' Skip check for blank row since this is first run
    GoTo FirstRun:
            
Do While ActiveCell.Value <> ""
' Get Model # from next row
    crow = ActiveCell.Row

FirstRun:   'for first iteration

' Set Variables
    Dim Brand As String
    Dim Model As String
    Dim Desc As String
    Dim Gender As String
    Dim List As String
    Dim Shape As String
    Dim Crystal As String
    Dim Clasp As String
    Dim Dial As String
    Dim Case1 As String
    Dim Case2 As String
    Dim CaseD As String
    Dim CaseT As String
    Dim BandM As String
    Dim BandL As String
    Dim BandW As String
    Dim BandC As String
    Dim Bezel As String
    Dim Crown As String
    Dim DateW As String
    Dim Special As String
    Dim Weight As String
    Dim Move As String
    Dim WR As String
    Dim Alarm As String
    Dim Jewels As String

' Populate variables
    Brand = ActiveCell.Offset(0, -1)
    Model = ActiveCell.Offset(0, 0)
    Desc = ActiveCell.Offset(0, 1)
    Gender = ActiveCell.Offset(0, 13)
    List = ActiveCell.Offset(0, 14)
    Shape = ActiveCell.Offset(0, 16)
    Crystal = ActiveCell.Offset(0, 17)
    Clasp = ActiveCell.Offset(0, 18)
    Dial = ActiveCell.Offset(0, 19)
    Case1 = ActiveCell.Offset(0, 20)
    Case2 = ActiveCell.Offset(0, 21)
    CaseD = ActiveCell.Offset(0, 22)
    CaseT = ActiveCell.Offset(0, 23)
    BandM = ActiveCell.Offset(0, 24)
    BandL = ActiveCell.Offset(0, 25)
    BandW = ActiveCell.Offset(0, 26)
    BandC = ActiveCell.Offset(0, 27)
    Bezel = ActiveCell.Offset(0, 28)
    Crown = ActiveCell.Offset(0, 29)
    DateW = ActiveCell.Offset(0, 30)
    Special = ActiveCell.Offset(0, 31)
    Weight = ActiveCell.Offset(0, 32)
    Move = ActiveCell.Offset(0, 33)
    WR = ActiveCell.Offset(0, 34)
    Alarm = ActiveCell.Offset(0, 35)
    Jewels = ActiveCell.Offset(0, 36)
    
    Sheets("~Publish to YWC~").Select
    Range("L3").Select
    
' Populate cells on Publish to YWC Page
    Range("L3") = Brand
    Range("L4") = Model
    Range("L6") = Desc
    Range("L24") = Gender
    Range("L7") = List
'    Range("L4") = Shape
    Range("L21") = Crystal
    Range("L11") = Clasp
    Range("L23") = Dial
    Range("L18") = Case1
    Range("L16") = Case2
    Range("L17") = CaseD
    Range("L19") = CaseT
    Range("L14") = BandM
    Range("L13") = BandL
    Range("L15") = BandW
    Range("L12") = BandC
    Range("L10") = Bezel
    Range("L20") = Crown
    Range("L22") = DateW
    Range("L28") = Special
    Range("L29") = Weight
    Range("L26") = Move
    Range("L27") = WR
    Range("L9") = Alarm
    Range("L25") = Jewels

    ' create HTML output string
    
    Range("L101:L285").Select
    Dim cl As Range
    Dim myString As String
    For Each cl In Selection
'    myString = Join(Application.Transpose(Range("L101:L285")))
    myString = myString & cl
    Next cl

    Range("G99") = myString
    Range("G99").Select

    ' paste HTML to col AP of By Model
    Selection.Copy
    Sheets("By Model").Select
    Cells(crow, "AP:AP").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False
    
    GoTo Again:
    
Again:
    
    Sheets("By Model").Select
    Cells(crow, "B:B").Activate
    ActiveCell.Offset(1, 0).Activate

    Loop
    
MsgBox "Finished!"

End Sub

 

[Norie]

Sorry, I'm really not following here.

Do you have data in Excel that you want to update/insert into an existing webpage?

 

[boznian]

The above script creates all the HTML code necessary, into a single cell, to generate the webpage for 1 particular product. It is part of a long process that updates and creates our product pages on our website.

I don't want us to get to distracted by what it does because as I said it works as is if I run it item by item, manually. It is just getting to be too cumbersome for that.

So all I need to do is to figure out where the memory leak or overflow or whatever is causing the memory error in "batch" mode.

 

[Norie]

I think the problem might be that a cell can only contain 32767 characters.

When it works how are you updating the webpage from what the code creates?

Couldn't you just write everything to a text/HTML file rather than a cell on a worksheet?

Actually if you did that it might be possible to use that file as an external source for the webpage.