Run VBA script on entire folder

[Ghost01]

Hi guys,

I am new to this forum, so I hope you can help me out.

I have a folder with say 20 different .TXT files that all need to have a VBA script run through. I have created the VBA script, but I can only get it to work one at a time.

I was therefore wondering if it would be possible to add a script that would do it for all the files in the folder, regardless of the name of the files (they change frequently).

If possible I would like to create a new excel file, that allows me to enter the address for the folder, and then press a run button that would do the script for all the files in the designated folder, regardless of the names.

Hope you guys can help me out!

Thanks!

 

[schlerp]

try using the DIR function... its a little weird the way it works IMO however its very easy to use

Take a look at this example and substitute *.ESY for *.TXT:
excel - vba: getting list of all files with ESY extension in a directory - Stack Overflow

 

[Cindy Ellis]

Here's a macro "skeleton" that may be useful for you:

Sub TestGetList()GetDir = Application.GetOpenFilename("Text Files (*.txt), *.txt")


If GetDir <> "False" Then
    MyPath = CurDir & "\"
Else
    MsgBox "Directory not selected"
    Exit Sub
End If


Application.ScreenUpdating = False


NextFile = Dir(MyPath & "*.txt") 'gets the first file in the list of text files in the directory


While NextFile <> ""
    'In the next command, replace everything after "NextFile" with the parameters specific to your file, 
    '        which you can get most easily by recording the opening and parsing of the text file.
    Workbooks.OpenText FileName:= _
        NextFile _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1)), TrailingMinusNumbers:=True


' put the guts of your macro here
    
    Workbooks(NextFile).Close


NextFile = Dir()
Wend
End Sub

I have some other code for making a summary, but it will take me a while to dig it out.
Hope this helps,

 

[Ghost01]

Thanks Cindy!

That did the trick!

 

[Cindy Ellis]

Thanks for the feedback, and good luck on your project.