Bypass Excel file selection confirmation for data connection with non-.txt text file

Question

I have an Excel workbook that processes model output from another program. That program produces space-delimited plain-text output files with a ".plt" extension. I have a data connection in Excel to bring those data in, but it requires more clicks than I would like. Currently the work flow is this:

1. Run other model.
2. In Excel, click Data->Refresh All
3. In the file dialog box that opens, click on "Text Files (*.prn, *.txt, *.csv)"
4. Pull Down appears - click on : "All Files (*.*)".
5. Select file name from list (filename never changes - Always "Output.plt")
6. Select "Import."

From here, it remembers all of the settings for the data connection - locations, text to columns, etc. Since my file name never changes, though, I wish it would remember the name of my file, so I would only have one or two clicks, instead of 5.

I tried to record a macro, going through all these steps, but the only piece that showed up in VBA is ActiveWorkbook.RefreshAll

Answer 1

There's an option to prompt for filename.

Go to Data / Connections, choose your connection, click properties.

Under the refresh control section, on the Usage tab, take the tick out of the box "prompt for file name on refresh"

Answer 2

Dim File_Path As String
File_path=" C:\Users\owner\John\Output.plt"
Open File_Path for Input as #1
row_num= 5
Do until EOF(1)
Line Input #1, Line_FromFile
Line_Items = Split(Line_FromFile, ",")
Range("C"&row_num).Value = Line_Items(2)
Range("B"&row_num).Value = Line_Items(1)
Range("A"&row_num).Value = Line_Items(0)
row_num = row_num + 1
Loop
Close #1