What I did was had the program that produces the CSV also write out a text
file with the path and CSV file name. I created an auto execute macro in the
template that has all the header data to read the TXT file and then import
the CSV file.
Private Sub Workbook_Open()
Dim ConnString As String
Open "location of file\qryfile.txt" For Input As #1
Line Input #1, ConnString
Close #1
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ConnString _
, Destination:=Range("A3"))
.Name = "Rpt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub