Solved: Infamous "Excel cannot complete this task" message.

**macro_man** · 03-17-2009, 09:33 AM

I have a macro that quits and displays the infamous "Excel cannot complete this task with available resources. Choose less data or close other applications." The file was originally an Excel 2003 (.xls) but has been converted to an .xlsm format after our recent Office 2007 upgrade.

Intent of selected portion of VB Code: Prepares a master workbook for new data to parse from our company's enterprise system (SAP). Parses this download and copies it to the master workbook. Removes blank rows and other garbage that results from the download (heading dividers, non-pertinent data).

The VB Code:

Sub Macro1()

'prepare for new data:

Sheets("From_Download").Select
Rows("3:65536").Select

'parse data:

Workbooks.OpenText Filename:="L:\Downloads\dlextract.xls", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 9), Array(1, 2), Array(20, 2), Array(46, 3), Array(56, 3), Array(67, 2), _
Array(78, 2), Array(81, 1), Array(90, 1), Array(106, 1), Array(122, 1), Array(131, 1), _
Array(140, 1), Array(149, 1), Array(158, 1), Array(167, 1), Array(176, 1), Array(185, 1), _
Array(196, 1), Array(203, 2), Array(214, 2), Array(219, 2), Array(221, 2), Array(224, 2), Array(229, 2), _
Array(238, 3), Array(249, 2), Array(253, 9)), TrailingMinusNumbers:=True

'copy data and close dl file:

Range("A1").Select
Range(ActiveCell, ActiveCell.Offset(0, 26).End(xlDown).Offset(0, -1).End(xlUp)).Select
Selection.Copy
Windows("COST_ESTIMATE_MASTER.xlsm").Activate
Sheets("From_Download").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Windows("dlextract.xls").Activate
ActiveWorkbook.Close
Windows("COST_ESTIMATE_MASTER.xlsm").Activate
Sheets("From_Download").Select

'input test formula & Delete:

Range("AZ3").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-43]),1,2)"
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, -51).End(xlUp).Offset(0, 51)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A3:AZ65536").Select
Selection.Sort Key1:=Range("AZ3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("AZ3").Select
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range(ActiveCell, ActiveCell.End(xlDown).Offset(5, 0)).Select

Selection.EntireRow.Delete

--------------------------------------------------------

When the macro attempts to delete rows in the resorted range I receive the error message. There is much more to this macro but wanted to isolate only the portion that abends.

System:

XP Professional Version 2002 SP3
Intel Core 2 Duo CPU T1800 @ 2.6GHz
1.99GB RAM

Office 2007
VB version 6.5

**Bob Phillips** · 03-17-2009, 09:47 AM

Get rid of all the selecting, that should help.

**macro_man** · 03-17-2009, 10:00 AM

Originally Posted by xld

Get rid of all the selecting, that should help.

Is there a better way to choose the worksheets and ranges that I want to manipulate than .select?

**Bob Phillips** · 03-17-2009, 10:30 AM

Yes, you don't need select. For instance, replace

[vba]

Range("A1").Select
Range(ActiveCell, ActiveCell.Offset(0, 26).End(xlDown).Offset(0, -1).End(xlUp)).Select
Selection.Copy
[/vba]

with

[vba]

With Range(Range("A1"), Range("Z1").End(xlDown))
.Copy
End With
[/vba]

and so on.

**mdmackillop** · 03-17-2009, 11:59 AM

After finding that 2007 would not insert a column into my data (200 cols x 3000 rows) I gave up on mega spreadsheets I didn't really need. Saving the file in 2003 format, my macros worked fine.

**Bob Phillips** · 03-17-2009, 12:18 PM

Malcolm,

Did you get the Cannot Shift Objects message?

**macro_man** · 03-18-2009, 01:27 PM

Originally Posted by xld

Get rid of all the selecting, that should help.

Thanks a lot. By reducing the amount of Selections my macro runs much faster. It still has the error message as a result of deleting the rows. I applied the With/End With to much of my macro and replaced Delete with ClearContents. For some reason as was stated by someone else this mega file cannot handle an adjustment to the entire worksheet (via Delete).

Is there a command that clears formatting, fonts, and borders without deleting the row?

**macro_man** · 03-19-2009, 09:08 AM

Originally Posted by mdmackillop

You could also try including
[vba]
.Hidden = True
[/vba]

Thanks!

Does .Activate take less processing memory than .Select?

Example:

Sheets("Valuation_Report").Select

My goal is to select a specific worksheet (tab) in the content of a macro that moves from one tab to another.

**macro_man** · 03-19-2009, 11:42 AM

The following code takes an Excel file that is downloaded (#1) from an enterprise system in text format, parses it, copies it to a template file (#2), closes the downloaded file, removes garbage (header lines, extra stuff) at the template, and resorts it for later processing.

#1 enterprise system download file name: Download_Valuation.xls
#2 master template file: VALUATION_REPORT.xlsm

Here is the code:

[vba]
Sub Macro_Parse()
'prepare template for new data and keep headings in rows 1-3:
Windows("VALUATION_REPORT.xlsm").Activate
Sheets("Sheet1").Select
Range("A4:AZ65536").Select
Selection.ClearContents

'parse download from enterprise system:
Workbooks.OpenText Filename:="C:\Downloads\Download_Valuation.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(19 _
, 2), Array(59, 1), Array(77, 2), Array(82, 1), Array(96, 1), Array(115, 2), Array(127, 2), _
Array(131, 2), Array(142, 2), Array(143, 2), Array(146, 2), Array(148, 2), Array(152, 2), _
Array(157, 2), Array(163, 1), Array(184, 2), Array(188, 1), Array(195, 1), Array(204, 2), Array(208, 2)), _
TrailingMinusNumbers:=True
'copy download to main file and close download:
Windows("Download_Valuation.xls").Activate

Range("A1").Select
'ensures that the macro range is selected beginning with A1

Range(ActiveCell, ActiveCell.Offset(0, 21).End(xlDown).Offset(0, -21).End(xlUp).Offset(0, 20)).Select
'The reason this Range is scripted this way is to ensure that all of the enterprise system's download
' is captured. The data range varies in length each time the enterprise systems program is executed.

Selection.Copy
Windows("VALUATION_REPORT.xlsm").Activate
Sheets("Sheet1").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

'closes file that was previously downloaded:
Windows("Download_Valuation.xls").Activate
ActiveWorkbook.Close

'input and copy test formula for garbage elimination:
Sheets("Sheet1").Select
Range("AZ4").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-49]),1,2)"
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, -51).End(xlUp).Offset(0, 51)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'sort for deletion:
Range("A4:AZ65536").Select
Selection.Sort Key1:=Range("AZ4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("AZ4").Select
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.EntireRow.Delete
'resort:
Range("A4:AZ65536").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

[/vba]

**macro_man** · 03-19-2009, 12:29 PM

Originally Posted by mdmackillop

This has me going in circles.
[vba]
Range(ActiveCell, ActiveCell.Offset(0, 21).End(xlDown).Offset(0, -21).End(xlUp).Offset(0, 20)).Select
[/vba]
Is there one colum that contains "solid" data to the end of your import?

Thanks for the reply.

No, unfortunately there are a few blank rows - that vary in quantity. Therefore, I had opted to "go around" the data and "sneek up" from underneath to capture all of it. What it does within the one line is as follows:

1. highlights row from A1 to one cell beyond data (this is blank).
2. goes down to end of worksheet
3. goes back up but one column to the left from #1 above. This stops the highlighted (selected) range to copy to include all data regardless of original download size.

There is probably a better way to do this. Some of my thought processes come from my days of working with macros in Lotus 1-2-3 (cursor movement recording).

**macro_man** · 03-19-2009, 12:32 PM

Originally Posted by mdmackillop

Avoiding selection, the first part should be something like
[vba]
Sub Macro_Parse()
Dim ValReport As Workbook
Dim Download As Workbook
Dim Rw As Long
Set ValReport = Workbooks("VALUATION_REPORT.xlsm")
'parse download from enterprise system:
Set Download = Workbooks.OpenText(Filename:="C:\Downloads\Download_Valuation.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(19 _
, 2), Array(59, 1), Array(77, 2), Array(82, 1), Array(96, 1), Array(115, 2), Array(127, 2), _
Array(131, 2), Array(142, 2), Array(143, 2), Array(146, 2), Array(148, 2), Array(152, 2), _
Array(157, 2), Array(163, 1), Array(184, 2), Array(188, 1), Array(195, 1), Array(204, 2), Array(208, 2)), _
TrailingMinusNumbers:=True)

'prepare template for new data and keep headings in rows 1-3:
ValReport.Sheets("Sheet1").Range("A4:AZ65536").ClearContents
With Download.Sheets(1)
'Get last row
Rw = .Range("A:U").Find(What:="*", After:=Cells(1, 1), LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Range(.Cells(1, 1), .Cells(Rw, 21)).Copy
ValReport.Sheets("Sheet1").Range("A4").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
'closes file that was previously downloaded:
Download.Close False

[/vba]

Thanks. I see I have a lot to learn. I learned much of what I know using the QUE books which are obviously beginner level. Last week I purchased John Walkenbach's Excel 2007-Power Programming with VBA so am trying to cram to catch up.

**macro_man** · 03-19-2009, 01:20 PM

I'm getting a window: Compile error: Expected Function or variable at the following code in bold:

[vba]
Set Download = Workbooks.OpenText (Filename:="C:\Downloads\Download_Valuation.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(19 _

[/vba]

Thread: Solved: Infamous "Excel cannot complete this task" message.

Thread Tools

Display