Changing file name for a data import from a text file

[Riddler]

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

 

[Joel]

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]

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

Scott

 

[Joel]

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]

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