Import text file contents based on path

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

crazybass2 - 29 Mar 2005 21:05 GMT

I need code that will prompt the user (via browse window) to choose a file.
I then want to import the contents of the specified file into a sheet. The
files will all be text files, but do not have a .txt extension. I don't want
the user to have to Data->Import External Data

Reply to this Message

crazybass2 - 29 Mar 2005 21:37 GMT

OK, so I guess I should have spent a few more minutes on this before posting
a questions. I figured it out. But for the benefit of others, here's what I
did.

Sub Getfile()
Dim filetocheck
filetocheck = Application.GetOpenFilename("My Text Files (*.mtf), *.mtf")
If filetocheck <> False Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & filetocheck,
Destination:=Range("A1"))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub

> I need code that will prompt the user (via browse window) to choose a file.
> I then want to import the contents of the specified file into a sheet. The
> files will all be text files, but do not have a .txt extension. I don't want
> the user to have to Data->Import External Data

Reply to this Message

cmungs - 31 May 2007 19:58 GMT

Thanks, I was having this same issue and couldn't figure out how to arrange
the filetocheck variable in the "TEXT:" statement.

Nice work.

Signature

cmungs

> OK, so I guess I should have spent a few more minutes on this before posting
> a questions. I figured it out. But for the benefit of others, here's what I
[quoted text clipped - 37 lines]
> > files will all be text files, but do not have a .txt extension. I don't want
> > the user to have to Data->Import External Data

Reply to this Message

Kulin - 11 Nov 2007 13:23 GMT

Thanks. I was in search of this.

However, Browse window opens wherein we have to move to various drive and
folders to reach at the location. Can I have code for absolute path [i.e.
path where the excel file is located pls ?.

kulin

> Thanks, I was having this same issue and couldn't figure out how to arrange
> the filetocheck variable in the "TEXT:" statement.
[quoted text clipped - 42 lines]
> > > files will all be text files, but do not have a .txt extension. I don't want
> > > the user to have to Data->Import External Data

Reply to this Message

Tom Ogilvy - 29 Mar 2005 21:43 GMT

turn on the macro recorder and do Data=>Import External Data

when the data is in turn off the macro recorder

Get the filename with

fName = Application.GetOpenFileName()

see help on GetOpenFileName for options on the filter

this function returns the fully qualified path of the selected file

use FName in the code you recorded to direct it at the file you want.

Signature

Regards,
Tom Ogilvy

Reply to this Message