VBA Code Help - Excel Errors out

[bnkeane]

Hi -

I am trying to write a macro that will open a series of text files in excel, perform text to columns based on fixed width columns (different for each file) and paste the data after the formatting into various tabs of a seperate workbook. I have written the following code that performs as intended but if I paste the code multiple times and update to open different text files and paste to a different tab, eventually excel errors out and says "there are not enough resources to complete this task."

The text files I am opening are not extremely big (maybe 10 columns by 1500 rows at the largest) so I am thinking that my code is inefficient and is leaving something "hanging" or in the clipboard that is eating up way too many resources. If I perform these exact same steps in succession manually I do not get the same error from excel.

Sub Open_Files()
 
Workbooks.OpenText FileName:= _
        "[URL="file://ttcfile10/invacct$/Control/Processing&Monitoring/2011/Item_ACR/Reports/Zero"]\\ttcfile10\invacct$\Control\Processing&Monitoring\2011\Item_ACR\Reports\Zero[/URL] Retail with Cost.txt" _
        , Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(5, 1), Array(13, 1), Array(20, 1), Array(60, 1), Array(69, 1), Array(76, _
        1), Array(91, 1), Array(106, 1), Array(116, 1), Array(126, 1)), TrailingMinusNumbers _
        :=True
    Cells.Select
    Selection.Copy
    Windows("Item ACR Combined Template.xlsm").Activate
    Sheets("0 Retail with Cost").Select
    Cells.Select
    ActiveSheet.Paste
    Windows("Zero Retail with Cost.txt").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close

The above code is repeated a total of 8 times for each text file and usually by about the 3rd or 4th iteration excel fails.

Please help identify the flaw in my code. Thanks!

 

[Jerry Sullivan]

Hi and Welcome,



I'm not positive where the problem is in your code, but the code below removes a few potential culprits.

• Copies the data range only instead of all Cells on the sheet
• Eliminates activating and selecting windows
• Uses Copy.. (Destination:=) instead of Copy... Paste
• Closes the .txt workbook directly instead of its Window

Sub Open_Files2()
    Application.ScreenUpdating = False
 
    Workbooks.OpenText Filename:= _
         "\\ttcfile10\invacct$\Control\Processing&Monitoring\2011\Item_ACR\Reports\Zero Retail with Cost.txt" _
        , Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(5, 1), Array(13, 1), Array(20, 1), Array(60, 1), Array(69, 1), Array(76, _
        1), Array(91, 1), Array(106, 1), Array(116, 1), Array(126, 1)), TrailingMinusNumbers _
        :=True
    With ActiveSheet.Cells
        Range("A1", Cells(.Find(What:="*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row, _
            Cells.Find(What:="*", after:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column)).Copy _
            Destination:=Workbooks("Item ACR Combined Template.xlsm") _
                .Worksheets("0 Retail with Cost").Range("A1")
    End With
    Application.CutCopyMode = False
    Workbooks("Zero Retail with Cost.txt").Close
End Sub

Hope this helps!

 

[bnkeane]

That code worked perfectly and was much more efficient with no errors. Thank you for your assistance!!!