How do I write a macro that opens the file dialogue box

[sb_stefan]

I made a macro where when I click a button, a text file is imported from the data tab (import data from text), and then some formatting is done to the text. When I recorded the Macro I simply hit record and manually went to the data tab and imported a text file. Then I assigned the macro to the button.

However, I intend to use this macro for different files with different names and from different locations, but the macro only opens the very file I opened when recording it. If that file is not available I get an error message.

Instead of instantly importing my file, I want a file dialog box to appear when I click the button, so I then can browse for any text file to import, and have the formatting done to.

This is what my code looks like now. Any advice?
("C:\Users\u0137477\Desktop\Macro test\DetailLog11.txt" is the file I used when recording the macro.)

Sub Macro1()'
' Macro1 Macro
'


'
    Sheets.Add After:=Sheets(Sheets.Count)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\u0137477\Desktop\Macro test\DetailLog11.txt", Destination:= _
        Range("$A$1"))
        .Name = "DetailLog11"
        .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 = 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, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileDecimalSeparator = "."
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Rows("1:1").Select
    Selection.Font.Bold = True
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Time (hh:mm:ss)"
    Range("C2").Select
    Columns("C:C").EntireColumn.AutoFit
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/86400"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/86400"
    Selection.AutoFill Destination:=Range("C2:C14134")
    Range("C2:C14134").Select
    Selection.NumberFormat = "h:mm:ss"
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    Columns("A:A").ColumnWidth = 34.29
    Columns("A:A").Select
    Selection.ColumnWidth = 27.29
    Range("A1").Select
End Sub

 

[ranman256]

YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
one button to pick a file
usage:

sub btnGetFile_click()
dim sFile as string

sFile = UserPick1File("c:\folder\")

 With ActiveSheet.QueryTables.Add(Connection:= "TEXT;" & sFile  , Destination:= Range("$A$1"))
'etc

end sub

place this code into a module

Public Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String

'===================
'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================

With Application.FileDialog(msoFileDialogFilePicker)   
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    '.Filters.Add "Excel Files", "*.xls;*.xlsx"
    .Filters.Add "Text Files", "*.txt"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

 

[sb_stefan]

Thank for the response. I should say that I'm new to this. When you say "place this code into a module", do you mean that I should paste that code somewhere in my macro? If so, where? Should anything from my existing code be removed? I assume the string that contains the filename of my text file should not be there.

 

[sb_stefan]

YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
one button to pick a file
usage:

Got it working. Thank you so much! One question: Does everyone who intend to use my macro first have to manually add reference in vbe menu, TOOLS, REFERENCES on their computer?

 

[Fluff]

If everyone is using the same version of Office then you will not need to make any changes to their computers.
But if you have people running different version then you will need to set the references.

Another option, that does not need any references.

   Dim Fname As String
   Fname = Application.GetOpenFilename
   Sheets.Add After:=Sheets(Sheets.Count)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Fname, Destination:=Range("$A$1"))