Excel - Split Write Once - Memory Issues

Why not do things in manageable chunks by splitting up oSQLDataTbl.DataBodyRange?

I'm not quite sure how to break up the DatabodyRange as its an internal command?  Is there a table range of TableX[Row 1] to [Row 30000] to [HeaderName] or something like that?

Neither am I because I'm not 100% sure what DataBodyRange is.

If it was an Excel range you could try something like this.

Dim rng As Range
Dim aData As Variant

    Set rng = oSQLDataTbl.DataBodyRange.Resize(1000)

    While rng.Cells(1, 1).Value <> ""
        aData = rng.Value

        For r = 1 To UBound(aData, 1)
            For c = 1 To UBound(aData, 2)
                If Len(Trim$(aData(r, c))) = 0 Then
                    ' the cell is blank
                    aData(r, c) = "Null"
                Else
                    If IsDate(aData(r, c)) Then
                        If IsDate(Cells(r, c)) Then Cells(r, c).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
                        If aData(r, c) < DateSerial(2011, 1, 1) Then
                            aData(r, c) = "Null"
                        End If
                    End If

                End If
            Next c
        Next r

        Set rng = rng.Offset(1000)
    Wend

Select allOpen in new window

This starts of with the first 1000 rows, then moves (offsets) down to the next 1000 rows and so on until the end of the data.

Well, that doesn't seem to work at all.  

DatabodyRange is an array for the entire Excel Listobject(1), that begins with A2 or (2,1) and ends with the last cell of the Range (82000, 62).  Maybe just turning it into an array of some kind where the FinalRow / 2 is ROW or FinalCol / 2 is Column (it didn't seem to work that easy). The issue is a memory one, so I just need either half of the rows or half of the columns, at one time, then the other half.

To get the FinalRow and Column it is:

    Dim oSQLDataTbl As ListObject
  oSQLDataTbl.DataBodyRange.Rows.Count 
   oSQLDataTbl.DataBodyRange.Columns.Count 

Select allOpen in new window

There is even a variable, something like oSQLDataTbl.DataBodyRange.Rows.Show that will return just the Final Row starting with the first column, (A80000) and ending with the Last Column (BJ80000).

Not quite sure how to split an array to work on one part, then have it work on the next one.  I tried two seperate loops but that didn't really work either.

How exactly does it not work at all?

Does the code run?

As far as I can see oSQLDataTbl.DataBodyRange is a range object not an array, see here.

If it was an array it wouldn't have Rows and Columns property.

In the code the only array is aData.

The code I posted should go through oSQLDataTbl.DataBodyRange 1000 rows at a time.

So that would start with A2:BJ1001, then A1002:BJ2001 and so on.

I just now saw your post and will test it again tomorrow.  When I ran it the first time I had a couple of blank cells in row 2 columns 5 and 6, and it went right over them.  Maybe I had something else going on.
Or, it acts like an array, but performs differently.

Attached is an adbidged version of the type of data that I'm dealing with.  I did keep it to 1000 rows and 62 columns to make it easier.  There are two subs also included, as well as a backup worksheet, to recopy the original data back during a testing phase.  The VBA code recommended above (the resize was moved down to 100), and a working version of the read then write once code, which does work, are also included; did try to keep most things, apples to apples.

Of course I would just use the Write Once, but for whatever reason, when running it on 80,000 rows, I get a memory error when it gets to aData = oSQLDataTbl.DataBodyRange.Value.

As the Write Once is even faster that performing a SQL Query in SSMS (by 1%-2%), this is obviously the code of choice. And that doesn't even include the user having to perfom a second step of copying it into Excel, which had its own memory issues.  In fact, I have to put another one together, as the Copy and Paste from SSMS to Excel is again throwing errors, when on tries to copy the entire dataset.

Surely with all the experience at EE, there has to be a way of splitting an Excel dataset into two or three arrarys to consume less memory.
DataFormat-Sandbox.xlsm

That's the reason I posted the workbook with the code.  It does run, the table does change its format but it doesn't perform what the code is asking it to do. The other Sub posted does perform the changes.  That way you can see and / or step through the code. I did notice a change in the List Object Name, When I would run the code,  in testing the one that worked changed the Table Name, when I copied the other data into it,  and it was formatted as ListObject(1), so it didn't matter. Both are the same in this one being posted. When running the first I had it stop at Wend, but it never changed the r, c values as it went through the second time, or the third time....  It also did not recognize that a cell was blank, it stepped over the "If" each time.
DataFormat-Sandbox.xlsm

Which code are you referring to exactly?

In the workbook in the sub where you had incorporated the code I posted earlier there was not code to return the array to the worksheet.

I've added that to the code I just posted and as far as I can see it does work.

I stepped through it to check.

I'm not sure what you mean about the r and c values not changing.

Why would they?

They are being used to loop through the array not the range.

The array is always the same size so the values that r and c loop through will always be the same.

What changes are the values in the array.

On the first pass they contain the values from the first 100 rows, then it will be the values from the next 100 rows, then the next 100 rows and so on.

I've attached a workbook with 2 worksheets and no code.

All it has is a before and an after worksheet.

The after worksheet is the result of running the code I posted above.
DataFormat-BeforeAfter.xlsx

After a couple of tries, it works as inticiapted.