Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
I'm trying to get a macro which imports any text file selected from a
directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
The Connection parameter neecs to be the complete file path - you can't leave
off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: ..Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
I've tried changing my macro but it still doesn't work. I'm obviously
missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
I've tried changing my macro but it still doesn't work. I'm obviously
missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
I've tried changing my macro but it still doesn't work. I'm obviously
missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
Dim fName as String
chdrive "C" chdir "C:\Data" fname = Application.GetOpenFileName(filefilter:="Text Files (*.txt),*.txt") if fName = "False" then exit sub end if With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & fname , Destination:=Range("A1")) .Name = "Mydata" -- Regards, Tom Ogilvy "Keith" wrote in message ... I've tried changing my macro but it still doesn't work. I'm obviously missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
See previous answer.
-- Regards, Tom Ogilvy "Keith" wrote in message ... I've tried changing my macro but it still doesn't work. I'm obviously missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file
Fantastic. Thank you very much indeed.
"Tom Ogilvy" wrote: Dim fName as String chdrive "C" chdir "C:\Data" fname = Application.GetOpenFileName(filefilter:="Text Files (*.txt),*.txt") if fName = "False" then exit sub end if With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & fname , Destination:=Range("A1")) .Name = "Mydata" -- Regards, Tom Ogilvy "Keith" wrote in message ... I've tried changing my macro but it still doesn't work. I'm obviously missing something but I just can't see it. I think it's possibly to do with not setting the directory first but I'm not sure. What I'm trying to achieve in the macro is for the user to select which text file gets imported from the C:\Data directory so the macro would go Data, Get External Data, Import Text File This needs to bring up the directory C:\Data. The user then selects which file to import then the import process continues. I'm new to this and am stuck on this one. I'd really appreciate some help editing my code (in the first post) so I can make this work. Thanks "K Dales" wrote: The Connection parameter neecs to be the complete file path - you can't leave off the file name. How are you selecting the file? Do you have the file name in a VBA variable? Assuming you have just the file name - not the complete path - in a variable called MyFileName, I think this should work: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\" & MyFileName, Destination:=Range("A1")) The .Name parameter is not used to specify the file name for import; rather it is the resulting Range name for the imported data table, though it makes sense to use the same name: .Name = Replace(MyFileName, ".txt", "") "Keith" wrote: I'm trying to get a macro which imports any text file selected from a directory C:\Data\Archive. The recorded macro is: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive\Mydata.txt", Destination:=Range("A1")) .Name = "Mydata" etc. This relates to a specific file whereas I want to import any text file in that directory I've tried lots of variations including: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Data\Archive", Destination:=Range("A1")) .Name = Application.GetOpenFilename but they don't work. I'd really appreciate any ideas. Thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
Help Importing Text File! | New Users to Excel | |||
Importing Text File | Excel Discussion (Misc queries) | |||
importing text file, removing data and outputting new text file | Excel Programming |