Capture file name and file path in a column while looping thru worksheets

[sspatriots]

Wondering if there is a way to make the code below capture the file name and file path in a column as it loops through each worksheet of each workbook in a folder. Let's say I want to put his out in column "J". I have no clue where to start.

Sub ImportInfo()
    Dim sPath As String 'path of folder containing info
    Dim sFileName As String '
    Dim wsSummary As Worksheet 'worksheet to paste data to in this workbook
    Dim wsData As Worksheet 'sheet with data to copy
    Dim wb As Workbook 'workbooks to loop through
    Dim nr As Long 'next row to add the data
    Dim cbValue As Variant
    
    'Get the worksheet to add the info to
    Set wsSummary = ThisWorkbook.Worksheets("Sheet1")
    
        
    'first row is 2
    nr = 2
    
    sPath = "C:\Users\SShockley\OneDrive - Delaware Elevator\Documents\FACTORY RELEASE FORMS\" '[COLOR=#ff0000][B]Change as required[/B][/COLOR]
    
    sFileName = Dir(sPath & "*.xlsm")
    
    
    Application.ScreenUpdating = False
    
    
    Do While sFileName <> ""
        'open workbook
        Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
        'get the sheet to copy from
        
        
        Set wsData = wb.Sheets("DEM Release Form")
        'get the data
        
        wsSummary.Range("A" & nr).Value = wsData.Range("C3").Value
        wsSummary.Range("B" & nr).Value = wsData.Range("C4").Value
        wsSummary.Range("C" & nr).Value = wsData.Range("H4").Value
        wsSummary.Range("D" & nr).Value = wsData.Range("B8").Value
        wsSummary.Range("E" & nr).Value = wsData.Range("F8").Value
        wsSummary.Range("F" & nr).Value = wsData.Range("C10").Value
        wsSummary.Range("G" & nr).Value = wsData.Range("G10").Value
        wsSummary.Range("H" & nr).Value = wsData.Range("B9").Value
        wsSummary.Range("I" & nr).Value = wsData.Range("I9").Value
        
    
    If cbValue = ActiveSheet.OLEObjects("CheckBox1").Object.Value = "True" Then
        
        wsSummary.Range("K" & nr).Value = "YES"
        
    Else
            
        wsSummary.Range("K" & nr).Value = ""

    End If
        
        
    If cbValue = ActiveSheet.OLEObjects("CheckBox2").Object.Value = "True" Then
        
        wsSummary.Range("J" & nr).Value = "YES"
        
    Else
            
        wsSummary.Range("J" & nr).Value = ""

        
    End If
        
     
        'get next row
        nr = nr + 1
        'close the workbook
        wb.Close
        'get next workbook name
        sFileName = Dir
    
    
    Loop
    
    
    Application.ScreenUpdating = True
    
    
    
End Sub

 

[JoeMo]

Untested, but you can try expanding the output to the Summary sheet by adding the bold lines below to your existing code.
This will put the filenames in col J and the file paths in col K.


wsSummary.Range("A" & nr).Value = wsData.Range("C3").Value

wsSummary.Range("B" & nr).Value = wsData.Range("C4").Value

wsSummary.Range("C" & nr).Value = wsData.Range("H4").Value

wsSummary.Range("D" & nr).Value = wsData.Range("B8").Value

wsSummary.Range("E" & nr).Value = wsData.Range("F8").Value

wsSummary.Range("F" & nr).Value = wsData.Range("C10").Value

wsSummary.Range("G" & nr).Value = wsData.Range("G10").Value

wsSummary.Range("H" & nr).Value = wsData.Range("B9").Value

wsSummary.Range("I" & nr).Value = wsData.Range("I9").Value

wsSummary.Range("J" & nr).Value = wb.Name

wsSummary.Range("K" & nr).Value = wb.Path

 

[sspatriots]

Thank you. I'll give it a try in the morning. I don't have access to that file from home. I will let you know how it goes though.

 

[sspatriots]

That seemed to do the trick, except I replaced "wbName" with "sFileName". Thanks for the suggestion, exactly what I needed. I think I was overthinking it a bit.

wsSummary.Range("J" & nr).Value = sFileName