0

I am receiving a message stating excel cannot find a file located in the path noted below. The file is in the folder and I have no idea how to fix this.

This is my first macro, any help would greatly be appreciated.

Sub CodeDateData()
Dim MyFile As String
    MyFile = Dir("I:\MAR_Public\LOGISTICS CDA\LCL Data\Reference Lists PERM99\LCL Inventory Reports for Spotfire\")
Dim lastrow As Long, lastcolmn As Long

    Do While Len(MyFile) > 0
        If MyFile = "ZZ Spotfire Inv File.xlsm" Then
            Exit Sub
        End If
        Workbooks.Open (MyFile)
        Sheets("Code Date").Select

        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.Close

        erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("Code Date").Range(Cells(erow, 1), Cells(erow, 4))

        MyFile = Dir

    Loop

    Application.DisplayAlerts = True

End Sub
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
IJC
  • 1
  • 1
  • 1
  • Why are you exiting your sub if your filename is equal to the filename you want to process? Have you tried Debug.Print MyFile to see what's coming through your loop? Does DIR return the full file path? – dbmitch Mar 29 '18 at 22:13
  • Funny, this code looks very similar to a previous one (https://stackoverflow.com/questions/49535883/sub-or-function-error/49542567#49542567) – AJD Mar 29 '18 at 22:34
  • 99% of the time the problem is a typo in the path or file name, or you really don't have the file where you think it is. Double-check your work, and take dbmitch's Debug.Print advice. – kismert Mar 29 '18 at 23:01
  • There is an issue with Office that will produce this problem. Run a repair on office to see if it resolves the issue. – Sorceri Apr 02 '18 at 17:03
  • Thanks for all your insight. It turns out I had to include the file path on Line 11 just after workbook.open. Example below: Workbooks.Open ("I:\MAR_Public\LOGISTICS CDA\LCL Data\Reference Lists PERM99\LCL Inventory Reports for Spotfire\") & (MyFile) – IJC May 01 '18 at 17:46

0 Answers0