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

sspatriots

Active Member
Joined
Nov 22, 2011
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,963
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Solution

sspatriots

Active Member
Joined
Nov 22, 2011
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 22, 2011
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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.

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

Forum statistics

Threads
1,176,096
Messages
5,901,375
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top