Weird problem I hope someone can help with:

Last few days I have created a macro (combination of recording and editing the VBA code) in Workbook A which copies a load of data from Workbook B to Workbook C. This worked (and still works) fine.

Today I wanted to create a similar macro, this time macro in Workbook D, copying data from Workbook E to Workbook F.

So I copied the VBA code into the new workbook D, stated to edit it by copying and modifying the bits I wanted, and putting an Exit Sub at the end of the new bit. This all worked fine for while, with data being copied into Workbook F. However, after a bit more editing I may have mistyped something and when running the macro again it seemed to get stuck in some loop and I had to terminate Excel by using the Task Manager.

Now, when I try and step though the macro it fails when I test for the existence of Workbook E:

Sub MakeData()
' MakeData Macro
'
     Dim InFile As String
     InFile = "Workbook E.xls"
'
' (more code)
'
' Check if Workbook E.xls exists
    If Dir(MEMFile) <> "Workbook E.xls" Then
       MsgBox "Workbook E.xls missing from current folder...."
        Exit Sub
    End If
'
' (more code)
'
    End Sub
The file is there, in the right place, and not already open.

I've since tried creating (recording) a test simple macro in workbook Test1, which opens workbook Test2 via windows explorer (it's in same folder as Test1) and select cell A1. The macro is recorded is:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
End Sub
there's just a blank line where I would have expected to see something like :
Workbooks.Open Filename:="Test2.xls"

If I add the line to the code and step through it and error message Run-time error '1004' file cannot be found is displayed

and if I modify the code to:

Sub Macro1()
'
' Macro1 Macro
'
Dim Folder As String
Folder = Application.ActiveWorkbook.Path
MsgBox (Folder)
'
    Range("A1").Select
End Sub
the MsgBox does display the correct path, C:\Data and Test2.xls is definitely in there and not already open!

I've tried rebooting the PC, no different.

Using Office W7 Prof 64b, Office 2010, macros enabled. Using .xls format cos I need the backwards compatibility, but as said above yesterdays macros and files all still work fine.

Can anyone help please, happy to run more tests, but this is my first time of using macros/VBA so a bit of a newbie in this area.

Thanks.