1

In VBA, I have a Workbooks.OpenText command:

Workbooks.OpenText Filename:=strFileToOpen, StartRow:=11, DataType:=xlDelimited, Tab:=True, TrailingMinusNumber:=True

However, instead of opening up file by file and adding it to my workbooks. I would like to point this to a Directory, and have VBA (Excel) perform the Workbooks.OpenText function for each file in the directory. How do I do that?

steno notes
  • 11
  • 1
  • 1
    you can use this as a starting point http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – nightcrawler23 Dec 12 '16 at 01:33

1 Answers1

1

Welcome to the forum!. You can list files in a folder and open them using the sample code below. Just alter the directory and pattern recognizer. It's currently set to search for .xlsm files in the C:\temp\ folder

Public Sub OpenFilesInFolder()
    Const folder_to_search As String = "C:\temp\"
    Const pattern_recognition As String = "*.xlsm"
    Dim the_file_name As String
    Dim full_path As String
    'the_file_name = Dir(folder_to_search & pattern_recognition, vbNormal)  'Applies the pattern recognition
    the_file_name = Dir(folder_to_search, vbNormal)                        'Does not apply the pattern recognition
    Do While Len(the_file_name) > 0
        full_path = folder_to_search & the_file_name
        Debug.Print full_path 'The full path will be printed in the Immediate window Ctrl+G will open this
        Workbooks.OpenText Filename:=full_path
        the_file_name = Dir 'Move onto the next file
    Loop
End Sub
Peter H
  • 871
  • 1
  • 10
  • 33
  • 1
    `pattern_recognition` should probably = `"*.csv"` but I know that you aren't using it. I would have `Workbooks.OpenText` follow `full_path` as the second line in the loop to keep the logic together. But good job –  Dec 12 '16 at 01:52
  • @ThomasInzina Good Point.. Swapped the OpenText Lines – Peter H Dec 12 '16 at 03:58