Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file name for a data import from a text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file name for a data import from a text file
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;\\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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file name for a data import from a text file
That was just a typo. Both should have the "_1". It still doesnt work
with it though. Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file name for a data import from a text file
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file name for a data import from a text file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import external data from changing file name | Excel Worksheet Functions | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |