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.

 

[Legacy 98055]

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.

<pre/>
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

'<Converts an item identifier list to a file system path.>
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.

'<Returns a pointer to an item identifier list that specifies the location of>
'<the selected folder relative to the root of the name space. If the user>
'<chooses the Cancel button in the dialog box, the return value is NULL.>
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
'>
'>

</pre>



If you require more help - post

 

[alexxl]

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