excelvbaruntime-error

Run-time error '7': Out of memory when copying data with leading text === from another workbook

We run reports on a customer facing database. We use the data from those reports in various other reports, all done through Excel and macros.

These reports have been running for about two years until this past Saturday when we started getting:

Run-time error '7':
Out of memory when copying data from another workbook

A customer prefaced an entry with three equals signs (===) before any text and my assumption is Excel was trying to solve the equation instead of copying the data.

It happens if I pare the data down to just that row.

I would like to build something to account for that.

The code that is called.

With ActiveWorkbook.Sheets(1).UsedRange
    RawDataMR.Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value
End With

I tried setting calculation to manual.
I tried turning off calculation for that particular sheet.

I'm hoping there is a way to keep it from trying to read that as a formula.

My only other thought is to build a routine that inspects the contents of each cell and strips out any character other than an alphanumeric character but that seems problematic as there are typically 7k lines and I'm pretty sure it would still hiccup when it hit that cell.

Solution

  • The fix I was looking for was more simple than I hoped. Thanks to BigBen for the reply.

    RawDataMR.Range("A1").Resize(.Rows.Count, .Columns.Count).NumberFormat = "@"

    , before transferring the value. – BigBen