[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - Split Write Once - Memory Issues

Posted on 2012-09-12
11
Medium Priority
?
343 Views
Last Modified: 2012-09-18
Below is some code that was provided by EE, for a "Write Once" to a Range of Data that was pulled from a SQL Server.

Dim aData As Variant
 aData = oSQLDataTbl.DataBodyRange.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

Open in new window

Due to the size of the data within the worksheet (80,000 rows, 62 cols) when the code gets to:
aData = oSQLDataTbl.DataBodyRange.Value , a memory error occurs.  It has excecuted up to around 40,000 - 50,000 and work okay.  80,000 must exceed my available RAM.

Is there a way to split this, so the first half gets completed and then it moves to the second half?  A couple of ways have been tried, but with unsuccessful results.
0
Comment
Question by:Cook09
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 38392639
Why not do things in manageable chunks by splitting up oSQLDataTbl.DataBodyRange?
0
 

Author Comment

by:Cook09
ID: 38392857
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?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38392999
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

Open 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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Cook09
ID: 38394860
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 

Open 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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38394966
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.
0
 

Author Comment

by:Cook09
ID: 38396991
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.
0
 

Author Comment

by:Cook09
ID: 38399273
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
0
 
LVL 35

Accepted Solution

by:
Norie earned 1500 total points
ID: 38399856
The code I posted does split the dataset into smaller datasets.

Can I ask how exactly it's not working?

How exactly did you incorporate it in your code?

One thing I now realise that's missing from the original code you posted is anything to return the array to the worksheet.

If this code is meant to be altering values on the worksheet I think that might be an important part of the whole thing.

I've found the code you use for that in your working code:
oSQLDataTbl.DataBodyRange.Value = aData

Open in new window

This is essential to the whole thing, and if it wasn't there it would appear that the code wasn't actually doing anything.

I've added something into the code to return the values to the worksheet.
Option Explicit
Sub RsizeTblRng()
Dim rng As Range
Dim wks As Worksheet
Dim oSQLDataTbl As ListObject
Dim aData As Variant
Dim wksName As String
Dim r As Long, c As Long

    Application.ScreenUpdating = False           'Increases Performance
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    wksName = SheetName                           'See Function Below
    
    Set wks = ActiveWorkbook.Worksheets(wksName)

    Set oSQLDataTbl = wks.ListObjects(1)

    With oSQLDataTbl
        .ShowAutoFilter = False
        .TableStyle = "TableStyleLight1"                   '   1=Light Gray   ="" is no Style 8 = Black Headings 9 = Blue
    End With


    Set rng = oSQLDataTbl.DataBodyRange.Resize(100)

    While rng.Cells(2, 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

       ' return values from array to worksheet
        rng.Value = aData

        Set rng = rng.Offset(100)
        
    Wend

End Sub


Function SheetName() As String
  SheetName = Sheet3.Name
End Function

Open in new window

0
 

Author Comment

by:Cook09
ID: 38399988
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 38400058
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
0
 

Author Closing Comment

by:Cook09
ID: 38409433
After a couple of tries, it works as inticiapted.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question