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

  • 1
    How about Workbooks.Open Filename:= _ "C:\Desktop/Output.plt" in a macro. – cybernard Jul 14 '15 at 21:27
  • That opens Output.plt as a file, but it doesn't bring it in to my ResultsAnalysisTemplate.xlsb I guess I could use VBA to open the file up, select each data group I need, and copy each to its appropriate place in the template, but I would've thought there would be a more direct solution. – Adam Jul 14 '15 at 22:55

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"

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
Close #1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.