Importing text file

[Keith]

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

 

[Guest]

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", "")

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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"

 

[Tom Ogilvy]

See previous answer.

--
Regards,
Tom Ogilvy

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

 

[Guest]

Fantastic. Thank you very much indeed.

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"