Import text file contents based on path
 

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

Import text file contents based on path
 

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


"crazybass2" wrote:

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

Import text file contents based on path
 

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.

--
Regards,
Tom Ogilvy


"crazybass2" wrote in message
...
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

Import text file contents based on path
 

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

Nice work.
--
cmungs


"crazybass2" wrote:

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


"crazybass2" wrote:

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

Import text file contents based on path
 

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

"cmungs" wrote:

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

Nice work.
--
cmungs


"crazybass2" wrote:

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


"crazybass2" wrote:

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