Run-time error '1004': Method 'Open' of object 'Workbooks' failed - HELP!

[B-Lass]

Hi, I would be incredibly grateful if anyone could help me with a problem I am having with a macro on excel 2010. I'm in my final year of my PhD and this is really causing me a lot of delays that I fear could have a detrimental effect on being able to submit my thesis on time. Data processing could take months instead of weeks at this rate - scary! Any suggestions would be very gratefully received (sorry this is such a long message).
Regards, Donna

Brief background
I've been using this macro for many months. The macro compiles a single column of data from upto 1104 .csv files into a single .xls file. For months it has worked perfectly, taking no more than 15 minutes.

Recent trouble
In past couple of weeks it has slowed considerably for no apparent reason. It does still sometimes eventually finish running as previous albeit taking longer. However most of the time it seems to take forever and when I press Esc this error always appears - Run-time error '1004': Method 'Open' of object 'Workbooks' failed. And when selecting Debug the same line of code is always highlighted yellow. On hitting continue it will run for a few more .csv files and the same occurs again and AGAIN!

And here is the code (I've highlighted the line of code it stops on)

Sub compiledata_U_velocity()
    Dim MyFile As String
    Dim MyCurrFile As String
    Dim myformula
    Dim ColumnTitle As String
    Dim MyLocation As String

    MyCurrFile = ThisWorkbook.Name
    'You will need to change the file path to match the
    'location of where your csv files are stored
    MyLocation = "G:\Single phase trials 3.30\Export - hd90 Single Phase Trials\deltaT100\32 50%\-1rd U\"
    MyFile = Dir(MyLocation & "*.csv")
    ColumnTitle = MyFile

    'This part of the code will open the first csv file in the folder
    'and copy two columns, the distance and the v value and paste it
    'into the Compiled document
    Application.ScreenUpdating = False
    Workbooks.Open MyLocation & MyFile
    Range("F9:G105").Select
    Selection.Copy
    Workbooks(MyCurrFile).Worksheets(1).Activate
    Range("a1").Select
    ActiveSheet.Paste
    Range("b1").Select
    Range("b1") = ColumnTitle
    With Selection
        .WrapText = True
        .ColumnWidth = 16.71
    End With

    'this section will convert the values to the
    'absolute value i.e. the value without the sign
    Range("b2").Select
    myformula = ActiveCell.Value * -1
    ActiveCell = myformula
    ActiveCell.Offset(1, 0).Select

    Do Until ActiveCell = ""
        myformula = ActiveCell.Value * -1
        ActiveCell = myformula
        ActiveCell.Offset(1, 0).Select
    Loop

    Workbooks(MyFile).Close savechanges:=False
    MyFile = Dir
    ColumnTitle = MyFile

    'this section will look for the next csv file in the folder
    'and perform the same actions
    Do Until MyFile = ""
        [B][COLOR="#FF0000"]Workbooks.Open MyLocation & MyFile[/COLOR][/B]
        Range("G9:G105").Select
        Selection.Copy
        Workbooks(MyCurrFile).Worksheets(1).Activate
        Range("a1").Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        ActiveSheet.Paste
        ActiveCell = ColumnTitle
        With Selection
            .WrapText = True
            .ColumnWidth = 16.71
        End With
        ActiveCell.Offset(1, 0).Select
        myformula = ActiveCell.Value * -1
        ActiveCell = myformula
        ActiveCell.Offset(1, 0).Select

        Do Until ActiveCell = ""
            myformula = ActiveCell.Value * -1
            ActiveCell = myformula
            ActiveCell.Offset(1, 0).Select
        Loop

        Workbooks(MyFile).Close savechanges:=False
        MyFile = Dir
        ColumnTitle = MyFile
    Loop

End Sub

 

[JLGWhiz]

The line above the highlighted line is the answer of why.

Do Until MyFile = ""
Workbooks.Open MyLocation & MyFile

You will need to re-establish the value of MyFile.

 

[B-Lass]

Hi JLGWhiz,
Thank you for your response, I really appreciate any help. I'm afraid I'm a bit of a novice with macros and I'm stumped at how to solve this problem. I'll have a look at how I can re-establish the value of MyFile. I didn't write this macro and to be honest it's not clear to me how I would do that yet, VB is like a foreign language to me at the moment.
The fact that the macro runs sometimes and not others is what I think I am finding most confusing. Why it would perfectly run fast for some time and then, what appears to be randomly, slow to a snail's pace? While it is running, looking at the VBA window it looks like it is keeping each of the .csv files open for longer and longer. I only get this Run-time error when I hit Esc. Does this make sense to you?
Again, I appreciate any help, I'm very much learning as I go

 

[JLGWhiz]

Hi JLGWhiz,
Thank you for your response, I really appreciate any help. I'm afraid I'm a bit of a novice with macros and I'm stumped at how to solve this problem. I'll have a look at how I can re-establish the value of MyFile. I didn't write this macro and to be honest it's not clear to me how I would do that yet, VB is like a foreign language to me at the moment.
The fact that the macro runs sometimes and not others is what I think I am finding most confusing. Why it would perfectly run fast for some time and then, what appears to be randomly, slow to a snail's pace? While it is running, looking at the VBA window it looks like it is keeping each of the .csv files open for longer and longer. I only get this Run-time error when I hit Esc. Does this make sense to you?
Again, I appreciate any help, I'm very much learning as I go

To be truthful, I have never worked with a .csv file, but I have read many postings dealing with inporting data from vairious places. The efficiency for the import is dependent on the capacity of your system, the amount of mega bytes you are importing and whether you have built in breaks in the controlling procedure when there is a substantial amount of files to be imported. I have seen the complaints of the system slowdown after a lot of data has been copied over. I believe that has to do with how the system memory handles the import and how it cycles through from memory to storage on the hard drive. But I am by no means an expert in this type of activity. You might try clicking on the FAQ and searching for related posts from there, or even googling on the web for it. You can probably find better info than I can give you.

 

[xenou]

Are these two columns of data the only thing in these csv files? -- I guess not since you are copying F9:G105 - what's the deal with copying these cells out of CSV's? Is there a lot of other information in the files or is this just a kind of oddity about how the CSV's are structured? Or did they come from Excel originally?

This also may sound odd but -- just in case -- have you rebooted your computer anytime in the last few months?

ξ