Selection window when importing text file

BarinaX

New Member
Joined
Dec 25, 2014
Messages
21
Hello,

I'm in need of your help once again.

I have a macro that imports txt file into excel. It's recorded at the moment but I would like it to start with a selection window that lets the user select which text file he wants to import. What is the VBA-command for this?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
something like this will prompt you for a text file:


Code:
    [color=blue]Dim[/color] filename [color=blue]As[/color] [color=blue]String[/color]
    filename = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt") [color=lightgreen]' prompt user for a FileName[/color]
    [color=blue]If[/color] filename = [color=blue]False[/color] [color=blue]Then[/color]
        [color=lightgreen]' user cancelled, get out[/color]
        [color=blue]Exit[/color] [color=blue]Sub[/color]
    [color=blue]End[/color] [color=blue]If[/color]

Something like this will prompt you for excel files initially , but you can select all files by clicking on the drop box bottom left in the Open File dialogue box


Code:
  [color=blue]Dim[/color] StrOpenFileTypesDrpBx [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'The Drop Box options in GetOpenFilename Dialogue Window[/color]
  [color=blue]Let[/color] StrOpenFileTypesDrpBx = "Excel (*.xlsx),*.xlsx,OpenOffice (*.ods),*.ods,All Files (*.*),*.*,ExcelMacros (*.xlsm),.xlsm"
             [color=lightgreen]'"DrpBxMsg1(DisplydFileType1),*.DisplydFileType1  ,  DrpBxMsg1(DisplydFileType2),*.DisplydFileType2   ,    DspBxMsg3(..etc...."[/color]
  [color=blue]Dim[/color] NewSheetPathAndFileName [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Complete Path on Computer string[/color]
  [color=blue]Let[/color] NewSheetPathAndFileName = Application.GetOpenFilename(StrOpenFileTypesDrpBx, 1, "Name up left in Dialogue box", , [color=blue]False[/color]) [color=lightgreen]'All optional Arguments[/color]
    [color=lightgreen]''("DrpBxMsg1(DisplydFileType1),*.DisplydFileType1 ,  DspBxMsg2(..etc....",   Default DrpBx Index   ,      "DialogueWindow(Form)Name" ,   Button-Mac Only     ,  MultipleFileSelectionOption  )[/color]


Alan
 
Upvote 0
Thanks, but that didn't do the trick. My bad, I should have specified the problem more accurately.

So far, I have used the recorded macro and modified it, so I have no idea how to input the code you gave me into this. How do I do that? The imported file must go to a new worksheet, as here.

Code:
    Set ws = Worksheets.Add
    

    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\Data\file_to_import.txt", Destination:=Range("$A$1"))
'        .CommandType = 0
        .Name = "file_to_import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ws.Move After:=Worksheets(Worksheets.Count)
 
Upvote 0
Managed to solve it myself, just needed a break and lots of coffee!

Here's the code if someone needs it.

Code:
    Set ws = Worksheets.Add
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select a TXT file!"
        .Show
            If .SelectedItems.Count = 0 Then
                MsgBox "You did't select a text file!", vbExclamation, "Canceled"
                Exit Sub
            Else
                strFileName = .SelectedItems(1)
            End If
    End With
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFileName & "", Destination:=Range("$A$1"))
'        .CommandType = 0
        .Name = strFileName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
Upvote 0
Managed to solve it myself, just needed a break and lots of coffee!
.....Here's the code if someone needs it.
...................

Hi BarinaX,

. Thanks for the quick feedback, that makes a nice change…

.. so this would, I think, be your equivalent to my two versions:

Code:
[color=blue]Sub[/color] BarinaXGetTextFile() [color=lightgreen]'http://www.mrexcel.com/forum/excel-questions/852774-selection-window-when-importing-text-file.html[/color]
[color=lightgreen]'[/color]
' Requires reference to Microsoft Office __._ Object Library.
  [color=blue]Dim[/color] strFileName [color=blue]As[/color] [color=blue]String[/color]
    [color=blue]With[/color] Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = [color=blue]False[/color]
        .Title = "Select a TXT file!"
        .Show
            [color=blue]If[/color] .SelectedItems.Count = 0 [color=blue]Then[/color]
                MsgBox "You did't select a text file!", vbExclamation, "Canceled"
                [color=blue]Exit[/color] [color=blue]Sub[/color]
            [color=blue]Else[/color]
                strFileName = .SelectedItems(1)
            [color=blue]End[/color] [color=blue]If[/color]
        [color=lightgreen]'[/color]
    [color=blue]End[/color] [color=blue]With[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'BarinaXGetTextFile()[/color]

.. that works nicely for me, so I have a new variation to add to my ways to get prompted to open a file, :)
. so thanks for sharing..

…................

.. then just out of interest while I am here…: - , here my two variations again applied equivalent to your code: ( They still seem to work by me and do something similar to yours )

Code:
[color=lightgreen]'[/color]
[color=blue]Sub[/color] GetTextFileAlan()
  [color=blue]Dim[/color] strFileName [color=blue]As[/color] [color=blue]String[/color]
    [color=blue]Let[/color] strFileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt") [color=lightgreen]' prompt user for a FileName[/color]
        [color=blue]If[/color] strFileName = [color=blue]False[/color] [color=blue]Then[/color]
        MsgBox "You did't select a text file!", vbExclamation, "Canceled"
        [color=blue]Exit[/color] [color=blue]Sub[/color] [color=lightgreen]' user cancelled, so get out of Sub[/color]
        [color=blue]Else[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=lightgreen]'[/color]
[color=blue]End[/color] [color=blue]Sub[/color] 'GetTextFileAlan()
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] GetFileAlan()
 
  [color=blue]Dim[/color] StrOpenFileTypesDrpBx [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'The Drop Box options in GetOpenFilename Dialogue Window[/color]
  [color=blue]Let[/color] StrOpenFileTypesDrpBx = "Excel (*.xlsx),*.xlsx,OpenOffice (*.ods),*.ods,All Files (*.*),*.*,ExcelMacros (*.xlsm),.xlsm"
             [color=lightgreen]'"DrpBxMsg1(DisplydFileType1),*.DisplydFileType1  ,  DrpBxMsg1(DisplydFileType2),*.DisplydFileType2   ,    DspBxMsg3(..etc...."[/color]
  [color=blue]Dim[/color] strFileName [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Complete Path on Computer string[/color]
  [color=blue]Let[/color] strFileName = Application.GetOpenFilename(StrOpenFileTypesDrpBx, 1, "Name up left in Dialogue box", , [color=blue]False[/color]) [color=lightgreen]'All optional Arguments[/color]
    [color=lightgreen]''("DrpBxMsg1(DisplydFileType1),*.DisplydFileType1 ,  DspBxMsg2(..etc....",   Default DrpBx Index   ,      "DialogueWindow(Form)Name" ,   Button-Mac Only     ,  MultipleFileSelectionOption  )[/color]
        [color=blue]If[/color] strFileName = [color=blue]False[/color] [color=blue]Then[/color]
        MsgBox "You did't select a file!", vbExclamation, "Canceled"
        [color=blue]Exit[/color] [color=blue]Sub[/color] [color=lightgreen]' user cancelled, so get out of Sub[/color]
        [color=blue]Else[/color]
        [color=blue]End[/color] [color=blue]If[/color]
  [color=lightgreen]'[/color]
[color=blue]End[/color] [color=blue]Sub[/color] 'GetFileAlan()

Alan

P.s. Go easy on the coffee, try a Protein Shake instead sometimes.
 
Upvote 0
Thanks for the help! It's good to have different options! Yours seems a bit better generally as it filters away others than text-files, but I don't need the filter as my folder only contains text files.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top