Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Batch processing all files in a directory

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •