Open & Copy From Multiple Text Files

cyclone_aero · Mar 13th 2008

My VBA code needs to import text files and put each one into a new worksheet. I'm currently using the following code to accomplish this:

Code

Do
    Application.DisplayAlerts = False
    Sheets.Add
    ChDir Path
    Workbooks.Open Filename:=Path & Name
    ActiveSheet.UsedRange.Copy
    ActiveWorkbook.Close
    Workbooks("Project.xls").Worksheets(1).Activate
    ActiveSheet.Paste
    i = i + 1
    Application.CutCopyMode = False
Loop

Display More

For some reason, it won't run in runtime mode, but it does work in break mode when I step through it. When I run it in runtime mode, I get a "Run-time error '1004': Microsoft Office Excel cannot paste the data." message displayed.

If someone could explain why that little procedure won't work in runtime and why it does in break mode, I'd appreciate it.

Thanks!

Simon Lloyd · Mar 13th 2008

Re: Code Works In Break Mode , But Not Runtime

Try it this way round:

Code

Dim MyBook As String
Do
Application.DisplayAlerts = False
    Sheets.Add
    ChDir Path
    Workbooks.Open Filename:=Path & Name
    MyBook = ThisWorkbook.Name
    ActiveSheet.UsedRange.Copy
    Workbooks("Project.xls").Worksheets(1).Activate
    Workbooks(MyBook).Close
    ActiveSheet.Paste
    i = i + 1
    Application.CutCopyMode = False
Loop

Display More

Dave Hawley · Mar 14th 2008

If you wish to continue using this free service.

Please read the This Page which you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.

[fa]*[/fa]

Open & Copy From Multiple Text Files

Participate now!

Share