Allow user to choose which text file to import

hharvey · May 26th 2016

Hi,

I am creating a macro that allows me to import data from a text file using the Text Import Wizard and some custom fixed width column settings.
It works great but....

... I would like it to pause at the 'open file' dialogue box to allow me to choose which text file to import.
At present this macro only works for the specific text file I used to create the macro, whereas I would like to be able to choose a different text file to import in future.

Any help much appreciated

Code

Sub dce()
'
' Open Text file Macro
'
' Keyboard Shortcut: Ctrl+f
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
          "TEXT;C:\Users\xxxxx\Documents\xxxxx\example.txt" _  , Destination:=Range("$A$1"))
        .Name = "example"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(23, 2, 25, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=21
    Range("D35:D52").Select
    Selection.Copy
    If ActiveSheet.Index = Worksheets.Count Then
    Worksheets(1).Select
    Else
    ActiveSheet.Next.Select
    End If
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Display More

shknbk2 · May 27th 2016

Re: Allow user to choose which text file to import

Refer to this link to find one way of choosing files in Excel. Modify it for your needs. Do this at the beginning of the macro where you can also handle what happens if no file or the cancel button is selected.

In your code, change the Connection text from

Code

"TEXT;C:\Users\xxxxx\Documents\xxxxx\example.txt"

to something like

Code

"TEXT;" & FName

or whatever variable you set as receiving the selected file name. Make sure the variable has the whole path.

mrmmickle1 · Jun 12th 2016

Re: Allow user to choose which text file to import

You can use code like this to accomplish your goal:

Code

Dim fNameAndPath As Variant
    
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.TXT), *.TXT", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub


    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT; " & fNameAndPath, Destination:=Range("$A$1"))

hharvey · Aug 30th 2016

Re: Allow user to choose which text file to import

Thanks mrmmickle1 - I have tried that but now I get a run time error 1004.

Code

Sub dce()
     '
     ' Open Text file Macro
     '
     ' Keyboard Shortcut: Ctrl+f
     '
Dim fNameAndPath As Variant
 
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.TXT), *.TXT", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
 
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT; " & fNameAndPath, Destination:=Range("$A$1"))
        .Name = "example"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(23, 2, 25, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=21
    Range("D35:D52").Select
    Selection.Copy
    If ActiveSheet.Index = Worksheets.Count Then
        Worksheets(1).Select
    Else
        ActiveSheet.Next.Select
    End If
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
End Sub

Display More

mrmmickle1 · Aug 30th 2016

Re: Allow user to choose which text file to import

What line of code are you getting the error on?

When you type the file name and path of the file you are choosing manually will the procedure run to completion? i.e. :

Code

ActiveWorkbook.Worksheets.Add 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;C:\Users\xxxxx\Documents\xxxxx\example.txt" _  , Destination:=Range("$A$1"))

Does your file contain any non compatible characters?

< , > , [ , ] , | , *

Allow user to choose which text file to import

Participate now!

Share