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;\\ULTRAVISION
\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;NewDataFileNameandPath",
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

The error will occur if the NewDataFileNameandPath doesn't exist. Make sure
the filename includes .s_1. The NewDataFileName (withoutpath) doesn't have
to inlcude the _1.


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


"Riddler" wrote:

> 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;\\ULTRAVISION
> \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;NewDataFileNameandPath",
> 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
>
>

That was just a typo. Both should have the "_1". It still doesnt work
with it though.

Scott

Lets make sure excel can really find the file. try the code below. Dir
returns a empty string if the file is not found.

I think the problem is your network address starting with the double
backslash. Do you have the Network name mapped to a drive letter? You may
havve to go into a window explorer (not internet) and map you network dirve
(under tools menu). Then use drive letter.

sub test()

flnm = Dir("\\ULTRAVISION\Opti information\prodhist\200710\20071003.s")
if flnm = "" then
msgbox("File not found")
else
msg box("File Found : " & flnm)
end if

end sub

"Riddler" wrote:

> That was just a typo. Both should have the "_1". It still doesnt work
> with it though.
>
> Scott
>
>
>
>

I got it figured out. I recorded the code when I edited the existing
query and it works now. Here is what I came up with.

Scott

Sub RefreshExistingQuery()
'This code will refresh a existing query with new data from a new file
DataFileName = "TEXT;\\ULTRAVISION\Opti information\prodhist
\200710\" & Sheets("Sheet1").Cells(3, 1).Text & ".s"
With Sheets("Sheet1").Range("D5").QueryTable
.Connection = DataFileName
.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)
.Refresh BackgroundQuery:=False
End With
End Sub