VBA not seeing file in folder

[grabrail]

OK, getting a little frustrated now with VBA.

I have some code that is assigned to a button, when the code runs it looks in a folder on my computer, looks for any .xlsm files in there, and if there is any opens them up and imports the data into the main file. This works.

So, I have copied the code and assigned to a second button, changed the folder path, as there are different files to import, and this is not working. Updated code is here

Private Sub CommandButton2_Click()
    Dim folderPath As String
    folderPath = "D:\Documents\lmg\Database\Import\GCSheets"
    Application.ScreenUpdating = False
    Dim fileName As String
    fileName = Dir(folderPath & "*.xlsm")
    
    
    MsgBox (fileName) ' <---- added this line to display the path and filename but is blank

    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet6 As Worksheet
    Dim sourceSheet7 As Worksheet
    Dim targetSheet1 As Worksheet
    Dim targetSheet2 As Worksheet

    Set targetWorkbook = Workbooks("database.xlsm")
    Set targetSheet1 = targetWorkbook.Sheets("GateChecks")
    Set targetSheet2 = targetWorkbook.Sheets("GateCheckDefects")

    Dim nextRow1 As Long
    Dim nextRow2 As Long

    While fileName <> ""  '<-----Stepping through using debug, gets to this line and then jumps to end of sub,
                            'effectively not seeing teh file in the folder
    
    
        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
        Set sourceSheet6 = sourceWorkbook.Sheets("GateChecks")
        Set sourceSheet7 = sourceWorkbook.Sheets("GCDefects")

        nextRow1 = targetSheet1.Cells(targetSheet1.Rows.Count, "B").End(xlUp).Row + 1
        nextRow2 = targetSheet2.Cells(targetSheet2.Rows.Count, "B").End(xlUp).Row + 1

        sourceSheet6.Range("B3:BO2").Copy targetSheet1.Range("B" & nextRow1)
        sourceSheet7.Range("B3:F" & sourceSheet7.Cells(sourceSheet7.Rows.Count, "B").End(xlUp).Row).Copy targetSheet2.Range("B" & nextRow2)

        sourceWorkbook.Close False

        targetSheet1.Range("A" & nextRow1).Value = nextRow1 - 1

        targetSheet2.Range("A" & nextRow2 & ":A" & targetSheet2.Cells(targetSheet2.Rows.Count, "B").End(xlUp).Row).Value = nextRow1 - 1

        targetSheet1.Range("B" & nextRow1 & ":AO" & nextRow1).HorizontalAlignment = xlCenter
        targetSheet2.Range("B" & nextRow2 & ":F" & targetSheet2.Cells(targetSheet2.Rows.Count, "B").End(xlUp).Row).HorizontalAlignment = xlCenter

        'fileName = Dir()
        
        'Move the source file to the "imported" folder
        Dim importedPath As String
        importedPath = "D:\Documents\lmg\Database\Import\GCSheets\Imported"
        Name folderPath & fileName As importedPath & fileName

        fileName = Dir()
    Wend
    
    MsgBox "Import Complete", vbInformation
     Application.ScreenUpdating = True
End Sub

I have a file in the folder, called import.xlsm, so i would expect the code to run, see the file, open it and select and import the data, but it is getting to the line of the code where it says While filename <> "" and using teh debugger to step through, immediately goes to the end of the sub. i dont understand why it is not seeing the file that is in the folder, any ideas?

 

[Joe4]

Perhaps you are missing the "slash" after the last folder, i.e. try changing this line:

folderPath = "D:\Documents\lmg\Database\Import\GCSheets"

to this:

folderPath = "D:\Documents\lmg\Database\Import\GCSheets\"

 

[grabrail]

OMG, thank you. Something so simple. Right I either need coffee or sleep now.

 

[Joe4]

You are welcome.
Glad I was able to help!