I have a text file that I am settup up to have imported into a sheet
and then do some calculations with that data. That all works fine. The
issue I am having is writing the macro to import the data again to the
same location and everything but the name of the text file has
changed. I am pulling data from another computer that creates a text
file for every day. It has a name like 20071003.s and 20071002.s
Here is the code I recorded and can rerun to refresh the data from the
same file;

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;\\UL TRAVISION
\Opti information\prodhist\200710\20071002.s", Destination _
:=Range("D5"))
.Name = "20071002.s"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.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, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Here is what I tried to chage it to, to change where the data comes
from 20071003.s instead of 20071002.s
I am on the same sheet when I run this also so that is not a issue.
The file exists and I can import it manually.
THe error I get is a runtime error 1004 that says "Excel cannot find
the text file to refresh this external data range"

Sub Macro1()
Dim NewDataFileNameandPath As String
Dim NewDataFileName As String

NewDataFileName = "20071003.s_1"
NewDataFileNameandPath = "\\ULTRAVISION\Opti information\prodhist
\200710\20071003.s"

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;NewD ataFileNameandPath",
Destination _
:=Range("D5"))
.Name = NewDataFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.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, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks
Scott