Vba excel macro open txt file browse

Question

Hi i want to open a txt file but it changes every month so i need to be able to select the new one browse through the map.

I am a complete beginner with VBA and i recorded the macro but when going in the specific coding part I don't really know most stuff.

Sub Medical_txt_excel()

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\user101\Documents\Macro Sales Monthly\Dec 2016-selected\Claim Medical.txt" _
    , Destination:=Range("$A$10"))
    .Name = "Claim Medical"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False

i need the Claim Medical.txt to be a file i can select myself when using the macro without changing the source code every time

score 1 · Accepted Answer · answered Feb 07 '17 at 09:24

1

ChDir "C:\Users\user101\Documents\Macro Sales Monthly\Dec 2016-selected"
Dim fpath: fPath = Application.GetOpenFilename("Text Files (*.txt),*.txt")
if fPath = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= "TEXT;" & fPath, Destination:=Range("A10"))
  ...
End With

answered Feb 07 '17 at 09:24

A.S.H

29,101
5
23
50

1

Thank you this code worked perfect for what i wanted! – M.P Feb 07 '17 at 09:36
@M.P Welcome, glad to help :) – A.S.H Feb 07 '17 at 09:42

score 0 · Answer 2 · answered Feb 07 '17 at 09:19

Try this

Sub Medical_txt_excel()
 Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Please select the file."
    fd.Show

With ActiveSheet.QueryTables.Add(Connection:= _
    fd.SelectedItems(1) _
    , Destination:=Range("$A$10"))
    .Name = "Claim Medical"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
End With

End Sub

Vba excel macro open txt file browse

2 Answers2