extract the file name from the directory-CodePudding

I'm new to VBA. I wrote a code to import data from a text file part of it is prompting the user to choose a file to open. my question is how to extract the file name and save it in a cell or even assign it to a variable let's say X.

Sub Macro1()
'
' Macro1 Macro
'
    ChDir "C:\Users\majed502\Documents\VBA WORK\VBA"
    
   the_file_picked = Application.GetOpenFilename("Text Files (*.txt), *.txt")
        
    Workbooks.OpenText Filename:= _
        the_file_picked, Origin:=437, _
        StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
        , 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:= _
        True
    'Sheets("Nov2007").Select
    ActiveSheet.Move Before:=Workbooks("monthly update.xlsm").Sheets(1)
    
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = Left(ActiveSheet.Name, 7)

End Sub

I'm currently taking the name out of the sheet name but would like to take it from the file name

CodePudding user response：

In order to retrieve the filename from an entire path, you might do like in following macro:

Sub test()
Dim a As String
a = "C:\Temp_Folder\blabla.txt"

iSplit = InStrRev(a, "\")

strName = Right(a, Len(a) - iSplit)
End Sub

=> strName equals "blabla.txt", which is the filename.

As a oneliner, you can go for this here:

strName = Right(str_Path, Len(str_Path) - InStrRev(str_Path, "\"))

... where strPath is the full path of the file.

CodePudding user response：

Try the following sub.

Sub LoopThroughFiles()
    Dim StrFile, FolderPath As String
    Dim StartCell As Range
    Dim i As Long
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        FolderPath = .SelectedItems(1)
    End With

    StrFile = Dir(FolderPath & "\")  'Dir("c:\testfolder\*test*") --- Dir("C:\Users\HARUN\Music\*.xlsx")
    
    Set StartCell = Application.InputBox(Prompt:="Select a cell to put file names. ", Type:=8)
    
    i = StartCell.Row
    Do While Len(StrFile) > 0
        'Debug.Print StrFile
        Cells(i, StartCell.Column) = StrFile
        StrFile = Dir
        i = i   1
    Loop
End Sub

This will prompt user to select a folder.
Then will prompt to select a cell to put files name.
Modify the codes as needed.