Files List

Sub Example1()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder("F:\Dropbox\Excel\VBA TO EXCEL")
    i = 1
    'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
        'print file name
        Cells(i + 1, 1) = objFile.Name
        'print file path
        Cells(i + 1, 2) = objFile.Path
        i = i + 1
    Next objFile
End Sub

File Name without Extension FSO

Public Sub Test1()
    Dim fso As New Scripting.FileSystemObject
    Debug.Print fso.GetBaseName(ActiveWorkbook.Name)
End Sub

Public Sub Test2()
    Dim fso As New Scripting.FileSystemObject
    Debug.Print fso.GetBaseName("MyFile.something.txt")
End Sub
myfilename = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

Open File using keywords

Sub OpenfileFromKeywords()
    Dim wb As Workbook
    Dim fPath As String
    Dim ifile As String
    fPath = "\\\FS01\Public\MIS_Team\Template\"
    ifile = Dir(fPath & "*Comparison_Configuration*.xlsx")
    If Found <> "" Then
        Set wb = Workbooks.Open(fPath & ifile)
    End If
    Set wb = Nothing
End Sub

Remove Bad character from string or filename

Function removeBadCharinfilename(strText As String) As Long
    Dim BadChars    As String
    Dim i           As Long
    Dim J           As Long
    BadChars = ":\/?*[]"
    For i = 1 To Len(BadChars)
        J = InStr(strText, Mid(BadChars, i, 1))
        If J > 0 Then
            BadChar = J
            Exit Function
        End If
    Next i
    BadChar = 0
End Function

Select File of Folder using VBA

Sub open_file_Folder()
    Dim fExplorer As FileDialog
    Dim FolderPath As String
    uMsg = vbNullString
   'Use one of the below
        'Set fExplorer = Application.FileDialog(msoFileDialogFilePicker)
        Set fExplorer = Application.FileDialog(msoFileDialogFolderPicker)
    With fExplorer 'Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show <> -1 Then
            uMsg = "No folder or file selected"
            GoTo endsub
        End If
        FolderPath = .SelectedItems(1)
    End With
    txtFolderOld = FolderPath
    If uMsg <> "" Then MsgBox uMsg
End Sub

File save As with Date and Time

Set NewBook = Workbooks.Add
        With NewBook
            .Title = "NAS_MAJ"
            .Subject = "Fusion"
            .SaveAs Filename:="\\Corpapps\Users\prasada\My Documents\Macro\Sid\Reports\Carms_Report_" & Format(Now(), "mmddyyyyhhmmss")
        End With