CODEWITHSUNDEEP
vba
Christoffer Tuxen Rosing
Christoffer Tuxen Rosing

Reputation: 11

Insert name of the file

I have a folder with many different files and I want to insert a column with the name of the file.

This is my code:

Sub AllWorkbooks()
   Dim MyFolder As String 'Path collected from the folder picker dialog
   Dim MyFile As String 'Filename obtained by DIR function
   Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
   If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
      Exit Sub
   End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
   'Opens the file and assigns to the wbk variable for future use
   Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
   'Replace the line below with the statements you would want your macro to perform
Sheets(1).Range("j1").Value = "Date"
wbk.Close savechanges:=True
MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub

So what I want to do is add some part of the file name to the entire J column except the header that is "Date" which I already implemented in the code above.

Thanks in advance

Upvotes: 0

Views: 251

Answers (1)

Davy C
Davy C

Reputation: 649

Add this code just after your "Date" value setting and before you close and save changes:

Dim i As Long

For i = 2 To Sheets(1).UsedRange.Rows.Count

    Sheets(1).Range("J" & i).Value = MyFile

Next i

This will insert the whole filename to the J column for each row, except for the header (2), that has data in the sheet. You should now be able to adapt this code for "some part of the filename" (you don't say which part!).

Upvotes: 1

Related Questions