VBA Code for Opening a .dat file from Excel

[Ken Monville]

I recorded a macro in Excel to open a specific .dat file. The .dat files are named in YYYYMMDD_XXX format and are specific to a calibration event. Normally the XXX is represented by 000. For this example I am using 20080718_000.

The code from the recorded Macro is:

ChDir "C:\Program Files\Compass"
Workbooks.OpenText Filename:="C:\Program Files\Compass\20080718_000.dat", _
Origin:=xlWindows, StartRow:=39, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False
End Sub

What I want to do is to change the 20080718_000 to a variable so that the Macro will retrieve the variable from a block in the spreadsheet, say A1 (or a UserForm), then put that value in where the 20080718_000 is now.

Any ideas would be greatly appreciated.

 

[BrianB]

'- **PARTIAL CODE **
Dim MyFile As String
MyFile = "C:\Program Files\Compass\" & ActiveSheet.Range("A1").Value
Workbooks.OpenText Filename:=MyFile ' ...........etc.

 

[Ken Monville]

Brian,

Thanks, it does exactly what I wanted.