Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bossing the Text Import Wizard about
I need to import a series of comma delimited text files into Excel. The
following chunk of code (pilfered from the macro recorder so it's probably more wordy than it needs to be) does what I want but it relies on the name and path of the text file to be hardcoded in. With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Documents and Settings\admin\Desktop\Press Repor1.txt", Destination _ :=Range("A2")) .Name = "Press Repor1_2" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileTabDelimiter = True .TextFileCommaDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With What I need is for the user to be able to browse to the text file to be imported, because it could be anywhere and called anything. Doing a File|open and browsing to the text file means that the user would have to go through the text import wizard, and I don't trust them to choose the right settings. Can anyone provide a suggestion as to how I can allow the user to browse to the text file and then for excel to get the text import settings from my code rather than forcing the user to choose them? It's probably really simple but I'm drawing a massive blank. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bossing the Text Import Wizard about
You can prompt for a file like this:
Sub Demo() Dim FName As Variant FName = Application.GetOpenFilename("Text files(*.TXT),*.TXT") If FName < False Then MsgBox FName Else MsgBox "User cancelled" End If End Sub -- Jim Rech Excel MVP "Katherine" wrote in message ... |I need to import a series of comma delimited text files into Excel. The | following chunk of code (pilfered from the macro recorder so it's probably | more wordy than it needs to be) does what I want but it relies on the name | and path of the text file to be hardcoded in. | | With ActiveSheet.QueryTables.Add(Connection:= _ | "TEXT;C:\Documents and Settings\admin\Desktop\Press Repor1.txt", | Destination _ | :=Range("A2")) | .Name = "Press Repor1_2" | .FieldNames = True | .RowNumbers = False | .FillAdjacentFormulas = False | .PreserveFormatting = True | .RefreshOnFileOpen = False | .RefreshStyle = xlInsertDeleteCells | .SavePassword = False | .SaveData = True | .AdjustColumnWidth = False | .RefreshPeriod = 0 | .TextFilePromptOnRefresh = False | .TextFilePlatform = 850 | .TextFileStartRow = 1 | .TextFileParseType = xlDelimited | .TextFileTextQualifier = xlTextQualifierDoubleQuote | .TextFileTabDelimiter = True | .TextFileCommaDelimiter = True | .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, | 1, 1, 1, 1, 1, 1, 1, 1, 1, _ | 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, | 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ | , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) | .TextFileTrailingMinusNumbers = True | .Refresh BackgroundQuery:=False | End With | | What I need is for the user to be able to browse to the text file to be | imported, because it could be anywhere and called anything. Doing a File|open | and browsing to the text file means that the user would have to go through | the text import wizard, and I don't trust them to choose the right settings. | | Can anyone provide a suggestion as to how I can allow the user to browse to | the text file and then for excel to get the text import settings from my code | rather than forcing the user to choose them? It's probably really simple but | I'm drawing a massive blank. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bossing the Text Import Wizard about
How would I use the sub Jim just wrote, and reference the user define filename&path in the rest of my code? Thanks, Kiera -- Kieran102 ----------------------------------------------------------------------- Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567 View this thread: http://www.excelforum.com/showthread.php?threadid=27009 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
Text Import Wizard | Excel Worksheet Functions | |||
Text Import Wizard | Setting up and Configuration of Excel | |||
Text Import wizard | Excel Discussion (Misc queries) | |||
Text Import Wizard | Excel Programming |