Thread: importing multiple text files with the same data type

1) Turn on the macro recorder.
2) Let it record you going through all the steps to import a text file to the format you want.
3) Do it again for a second file, just to be sure.
4) Turn off the macro recorder and post the resulting code here (between code tags, please, as shown in my signature)

We'll take a look and make a "dynamic" version you can run anytime to do it for you.

The Forum Rules are posted... see the link in the menu bar across the top of this page.

In my signature you see examples to the two "tags" you would need to add, one at the start of your pasted code, one at the end. The end result look like this:

Sub Macro1()

     'my code is here

End Sub

This will keep prompting you to select files and will abort when you do...

Option Explicit

Sub ImportTextFiles()
Dim fName As String, fPath As String

fPath = "F:\Gloucester\PLCData\1993\Dec93\12-93\H\"   'starting directory
 
Do
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = fPath
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"        'everything
        .Filters.Add "Text Files", "*.txt"     'text files
        .Filters.Add "001 Files", "*.001", 1   '001 files, the default
        .Show
        If .SelectedItems.Count > 0 Then
            fName = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

    Workbooks.OpenText Filename:=fName, Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Loop

End Sub

HI,

Here is something I use for this purpose. It was put together from snips provided by different forum members over time.

Hope it helps you.

Good luck.

abousetta

Sorry, CMORAL, I've been using that so long I forgot you needed to turn on an optional reference library in your Excel.

In the VBEDITOR, go to Tool > References and put a check-mark in the reference for:
Microsoft Office 11.0 Object Library

Do you have a reference option for Microsoft Shell Controls and Automation?

Or any chance this works?

Option Explicit

Sub ImportTextFiles()
Dim fName As String
Do
    fName = Application.GetOpenFilename("001 Text Files(*.001), *.001")
    If fName = "False" Then Exit Sub

    Workbooks.OpenText Filename:=fName, Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Loop

End Sub

Hi Jerry,

Yes. Thanks. That last bit of code worked nicely except I changed the code a bit so that I could open up all text files of the same type of data. Some have different extensions other than ".001"

One last question. How do I get to use the code for all other files I want to open? Do I have to always open the file containing that particular code to open the other files or is there some universal place I can store the code and then access it regardless of the files I open?

Option Explicit

Sub ImportTextFiles()
Dim fName As String
Do
    fName = Application.GetOpenFilename("Text Files(*.*), *.*")
    If fName = "False" Then Exit Sub

    Workbooks.OpenText Filename:=fName, Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Loop

End Sub

Thanks for all your help. I really appreciate it.

cmoral

1) fix the closing code tag in the post above. / instead of \

2) Macros stored in your Personal.XLS project (look in the other projects listed on the left, might be there already) are available at all times.