Batch processing all files in a directory

[alexxl]

I wrote a macro to read in a text file and do some necessary manipulation in Excel and then save it back to an Excel file with the text file's name. But I don't know how to do this in batch. I want to process all the text files in the same directory in sequence. Basically I like to assign a folder with a dialog box and then the macro will find all the text files in that folder and process them.
Thank you very much.

[Tom Schreiner]

Hi Alexxl
You could call your macro or place the code in a for each loop using the FileSearch object.
From the VBE, search help for FileSearch Object.
If you need any further help, please repost.
Tom

[alexxl]

I did that. A new question. I tried to get the folder name through a dialog box and put it in the modifier for the FileSearch (.LookIn). What is the best way to achieve this? I tried to use a File Open dialog and it can only open a file. When I tried to use .Path to find its folder, it returned the whole path and file name instead of just the folder. I ended up using string operation to cut the filename. I suppose there is a more direct way to do this.

[Ivan F Moala]

On 2002-05-13 13:04, alexxl wrote:
I did that. A new question. I tried to get the folder name through a dialog box and put it in the modifier for the FileSearch (.LookIn). What is the best way to achieve this? I tried to use a File Open dialog and it can only open a file. When I tried to use .Path to find its folder, it returned the whole path and file name instead of just the folder. I ended up using string operation to cut the filename. I suppose there is a more direct way to do this.

Unfortunately there is no direct way to just
get a Dir listing you can go with what you
have but this will be prone to errors
if a user tries to input a Dir via input.
Your other option is via API.
Have a look @ this code. Don't worry about
The top part...it is the Macro named
[GetMyDir] that you use to Get your Dir.

Paste Code in it's own Module.

Option Explicit



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

'// pidl

'Pointer to an item identifier list that specifies a file or directory

'location relative to the root of the name space (the desktop).



'// pszPath

'Pointer to a buffer that receives the file system path.

 

'

'

'

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _

    "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

'// lpbrowseinfo

'Pointer to a BROWSEINFO structure that contains information

'used to display the dialog box.





Function GetFolderName(msg As String) As String

'// Returns the name of the folder selected by the user

Dim bInfo As BROWSEINFO, path As String, r As Long, X As Long, pos As Integer



bInfo.pidlRoot = 0&                          ' Root folder = Desktop



If IsMissing(msg) Then

    bInfo.lpszTitle = "Select a folder."     ' the Default dialog title

Else

    bInfo.lpszTitle = msg                    ' the User defined dialog title

End If



bInfo.ulFlags = &H1                          ' Type of directory to return

X = SHBrowseForFolder(bInfo)                 ' Display the dialog

path = Space$(512)                           ' Parse the result

r = SHGetPathFromIDList(ByVal X, ByVal path) ' Convert to filesys path



If r Then

    pos = InStr(path, Chr$(0))

    GetFolderName = Left(path, pos - 1)

Else

    GetFolderName = ""

End If



End Function



Sub GetMyDir()

Dim FolderName As String



FolderName = GetFolderName("Please Select a folder to update files in")

If FolderName = "" Then Exit Sub



msgbox FolderName



'The rest of your code goes here

'>

'>

If you require more help - post

[alexxl]

Tom and Ivan, thanks very much for the great help.