PDA

View Full Version : 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
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



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


with



With Range(Range("A1"), Range("Z1").End(xlDown))
.Copy
End With


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. :dunno

Bob Phillips
03-17-2009, 12:18 PM
Malcolm,

Did you get the Cannot Shift Objects message?

mdmackillop
03-17-2009, 01:21 PM
No, it was just "Out of memory". Data/formulae only. No lines/colours etc. in whole rows or columns. I've not tried it since upgrading memory in the PC. I'll dig it out again.

macro_man
03-18-2009, 01:27 PM
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?

mdmackillop
03-18-2009, 01:33 PM
Subject to your Normal Style not containing offending items

With Selection.EntireRow
.Style = "Normal"
.ClearContents
End With

mdmackillop
03-18-2009, 01:35 PM
You could also try including

.Hidden = True

macro_man
03-19-2009, 09:08 AM
You could also try including

.Hidden = True


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.

mdmackillop
03-19-2009, 10:29 AM
Activating or Selecting a spreadsheet is not normally neccessary, unless it is the final part of the code to open a specific sheet. What is your code that requires you to Activate/Select a sheet?

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:



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

mdmackillop
03-19-2009, 12:01 PM
This has me going in circles.

Range(ActiveCell, ActiveCell.Offset(0, 21).End(xlDown).Offset(0, -21).End(xlUp).Offset(0, 20)).Select

Is there one colum that contains "solid" data to the end of your import?

mdmackillop
03-19-2009, 12:16 PM
Avoiding selection, the first part should be something like

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

macro_man
03-19-2009, 12:29 PM
This has me going in circles.

Range(ActiveCell, ActiveCell.Offset(0, 21).End(xlDown).Offset(0, -21).End(xlUp).Offset(0, 20)).Select

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
Avoiding selection, the first part should be something like

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



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.

mdmackillop
03-19-2009, 12:44 PM
Re post 16, try

Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 20).Select

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:


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

mdmackillop
03-19-2009, 01:27 PM
I was a bit doubtful about that one!
Try

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
Set Download = Activeworkbook

macro_man
03-19-2009, 02:36 PM
Avoiding selection, the first part should be something like

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



With the correction from #20 added to the code this section of the macro worked great! Thanks!

Now I need to analyse what you did.