CODEWITHSUNDEEP
vbaexcel
M.P
M.P

Reputation: 23

Vba excel macro open txt file browse

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

Upvotes: 2

Views: 2061

Answers (2)

A.S.H
A.S.H

Reputation: 29352

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

Upvotes: 1

Aeneas
Aeneas

Reputation: 270

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

Upvotes: 0

Related Questions