Kopier filnavn fra flere workbooks til celler i en anden workbook - excel, vba, excel-vba

Jeg har en mappe med mange arbejdsbøger, hvor jeg skal kopiere filnavne (og nogle andre data) til en mesterarbejdsbog. Jeg fandt en kode til at importere dataene, men det kan ikke synes at importere filnavnet.

Efter den "" >>>>>> Tilpas denne del "Jeg forsøgte at skrive nogle kode for at kopiere og indsætte filnavnet, men det synes ikke at virke.

Jeg bruger delen uden for "" >>>>>> Tilpas denne del "for at kopiere nogle andre data, så jeg behøver kun en kode til at passe ind i stedet for min ikke-fungerende kode :)

Sub Import_to_Master()
Dim sFolder As String
Dim sFile As String
Dim wbD As Workbook, wbS As Workbook

Application.ScreenUpdating = False
Set wbS = ThisWorkbook
sFolder = wbS.Path & ""

sFile = Dir(sFolder)
Do While sFile <> ""

If sFile <> wbS.Name Then
Set wbD = Workbooks.Open(sFolder & sFile)

" >>>>>> Adapt this part

WName = ActiveWorkbook.Name
WName.Copy
Sheets("Combined").Range("N" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

" >>>>>>

wbD.Close savechanges:=True "close without saving

End If

sFile = Dir "next file
Loop
Application.ScreenUpdating = True
End Sub

svar:

Sub Import_to_Master()

Dim sFolder As String
Dim sFile As String
Dim wbD As Workbook, wbS As Workbook

Application.ScreenUpdating = False
Set wbS = ThisWorkbook
sFolder = wbS.Path & ""

sFile = Dir(sFolder)
Do While sFile <> ""

If sFile <> wbS.Name Then
Set wbD = Workbooks.Open(sFolder & sFile)

" >>>>>> Adapt this part

wbS.Sheets("Combined").Range("N" & wbS.Sheets("Combined").Rows.Count).End(xlUp).Offset(1, 0).Value = sFile

" >>>>>>

wbD.Close savechanges:=True "close without saving

End If

sFile = Dir "next file
Loop
Application.ScreenUpdating = True
End Sub

Du kan direkte bruge objektet wbD og dens ejendom .Name.

Jeg har også tilføjet en henvisning til arket ("Kombineret") for bedre læsbarhed:

Sub Import_to_Master()
Dim sFolder As String
Dim sFile As String
Dim wbD As Workbook, wbS As Workbook
Dim wSc As Worksheet

Application.ScreenUpdating = False
Set wbS = ThisWorkbook
"""Define the sheet
Set wSc = wbS.Sheets("Combined")
sFolder = wbS.Path & ""

sFile = Dir(sFolder)
Do While sFile <> ""

If sFile <> wbS.Name Then
Set wbD = Workbooks.Open(sFolder & sFile)

" >>>>>> Adapt this part
wSc.Range("N" & wSc.Rows.Count).End(xlUp).Offset(1, 0).value = wbD.Name

" >>>>>>

wbD.Close savechanges:=True "close without saving

End If

sFile = Dir "next file
Loop
Application.ScreenUpdating = True
End Sub