vba to import worksheets from folder was working yesterday but not today

[reece1984]

Hi All,

Any help would be gratefully received on this.

I have a macro which loops through various worksheets that are contained in a directory and copies them into a master workbook.

The problem is that yesterday my importloop macro was working perfectly but now I am receiving Run-time error '1004':

'name_of_the_first_file.xlsx' could not be found.....

The vba is managing to read the filename perfectly so it must be finding it but something is clearly not working. Does anybody know why this is?


Here is the code for my macro.

 Sub ImportLoop()
    Dim MyObj As Object, MySource As Object, file As Variant
    If MsgBox("Are you sure that you want to import sheets?", vbYesNo, "Confirmation... ") = vbNo Then Exit Sub
    ChDir _
        "X:\Import files directory"
   
   SourceDir = "X:\Import files directory"
   
   file = Dir(SourceDir)
   
   While (file <> "")
    
    Workbooks.Open Filename:=file, UpdateLinks:=0
    
         
    Sheets.Copy Before:=Workbooks("Master Workbook.xlsm").Sheets(4)
    Windows(file).Activate
    ActiveWindow.Close False
     file = Dir

  Wend
End Sub

Thank you

 

[p45cal]

From Help:
***********
The ChDir statement changes the default directory but not the default drive. For example, if the default drive is C, the following statement changes the default directory on drive D, but C remains the default drive:

<code>ChDir "D:\TMP"
************

So you may have to use the CurDir function too.


</code>

 

[reece1984]

P45cal, Thank you for your response that is very useful.

THREAD CLOSED