CODEWITHSUNDEEP
excelvba
Marshall Rachan
Marshall Rachan

Reputation: 27

Extract files based on the prefix

I have written a code to extract filenames from a folder where I can manually select the path and all the filenames and the date of modification is extracted. However, I want a to modify the code so that it extracts filenames of only those files which starts from Alphabet "A". Below is the code: 

Option Explicit

Function BrowseForFolder(Optional OpenAt As Variant) As Variant

Dim ShellApp As Object
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Users\rd4470\Documents\Test Folder")
i = 1
For Each objFile In objFolder.Files
    Cells(i + 1, 1) = objFile.Name
    Cells(i + 1, 2) = objFile.DateLastModified
    i = i + 1
Next objFile
    Exit Function
Invalid:
    BrowseForFolder = False
End Function

Upvotes: 2

Views: 170

Answers (1)

Dportology
Dportology

Reputation: 808

I assume objFile.Name returns a string of your filename? You would just need to surround the innards of your for loop in an if, checking if the first character is an 'A'. (I believe this is what you wanted)...

Dim tempString As String

For Each objFile In objFolder.Files
    tempString = left(objFile.Name, 1)
    If tempString = "A" Then
        Cells(i + 1, 1) = objFile.Name
        Cells(i + 1, 2) = objFile.DateLastModified
        i = i + 1
Next objFile

code to specify folder:

Private Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
            "SHGetPathFromIDListA" (ByVal pidl As Long, _
            ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
            "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
            As Long

Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
  Dim X As Long, bi As BROWSEINFO, dwIList As Long
  Dim szPath As String, wPos As Integer


With bi
    .hOwner = hWndAccessApp
    .lpszTitle = szDialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
    wPos = InStr(szPath, Chr(0))
    BrowseFolder = Left$(szPath, wPos - 1)
Else
    BrowseFolder = vbNullString
End If
End Function


' insert code anywhere to call function

tempFilepath = BrowseFolder("Choose Folder For Import") & "\"
    If tempFilepath = "\" Then
        MsgBox "No output path specified, please select a valid output path"
        Exit Sub
    End If

Upvotes: 2

Related Questions