Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Import data from one xls file to another based on criteria | Excel Discussion (Misc queries) | |||
VBE, change default path for file import | Excel Programming | |||
Hide file path on a text box | Excel Programming | |||
Import text file without specifying a path macro won't work | Excel Programming |