Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Import Text File, File Name
I recorded a Macro while using "Data/Get External Data/Import Tex File". The Macro is: ______________________________ Sub TestTextFileImport() ' ' TestTextFileImport Macro With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;D:\My Documents\Test Text Import File.txt", Destination: _ Range("A1")) .Name = "Test Text Import File" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With End Sub ________________________ The Macro works well and is fast, but if want to import another file I have to edit the line: "TEXT;D:\My Documents\Test Text Import File.txt", Destination:= _ by writing in the new files path name. I would like to be able to write the name of the file I want to impor in a worksheet cell and have the Macro use that as the file name fo importing. I defned a variable called "FileName" and set it to a Cell A2 on Sheet which contained the same file name as in the Macro, (D:\M Documents\Test Text Import File.txt). I then inserted "FileName" where the files path was in the Macro. Th changes we ______________________________________ ' Dim FileName As Variant ' FileName = Worksheets("Sheet2").Range("A2") Sheets("Sheet1").Select ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;FileName", Destination:= _ Range("A1")) _______________________________________ The rest of the Macro was the same. When the Macro reached ".Refresh BackgroundQuery:=False" I got an erro message that the file could not be found. (Remming out ".Refres BackgroundQuery:=False" avoided the error, but the file didn't import Dah!) I also tried recording "Data/Get External Data/New Database Query". I seemed slower that Text Import and didn't help me with using a fil path on a Worksheet I tried Pearson Software Consulting's macro "ImportTextFile", whic works well but seems slow for my files. I guess wending thru 5,00 lines + of text would take some time. Is there any way to import different files w/o editing the Macro? Thanks in advance for your help, Bob -- Bob ----------------------------------------------------------------------- Bob S's Profile: http://www.excelforum.com/member.php...fo&userid=3272 View this thread: http://www.excelforum.com/showthread.php?threadid=52558 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Import Text File, File Name
'
Dim FileName As String' FileName = Worksheets("Sheet2").Range("A2").Value Sheets("Sheet1").Select ' With ActiveSheet.QueryTables.Add( _ Connection:= "TEXT;" & FileName, _ Destination:=Range("A1")) HTH -- AP _______________________________________ "Bob S" a écrit dans le message de ... I recorded a Macro while using "Data/Get External Data/Import Text File". The Macro is: ______________________________ Sub TestTextFileImport() ' ' TestTextFileImport Macro With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;D:\My Documents\Test Text Import File.txt", Destination:= _ Range("A1")) Name = "Test Text Import File" FieldNames = True RowNumbers = False FillAdjacentFormulas = False PreserveFormatting = True RefreshOnFileOpen = False RefreshStyle = xlOverwriteCells SavePassword = False SaveData = True AdjustColumnWidth = True RefreshPeriod = 0 TextFilePromptOnRefresh = False TextFilePlatform = xlWindows TextFileStartRow = 1 TextFileParseType = xlDelimited TextFileTextQualifier = xlTextQualifierDoubleQuote TextFileConsecutiveDelimiter = True TextFileTabDelimiter = False TextFileSemicolonDelimiter = False TextFileCommaDelimiter = False TextFileSpaceDelimiter = True TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1) Refresh BackgroundQuery:=False End With End Sub ________________________ The Macro works well and is fast, but if want to import another file, I have to edit the line: "TEXT;D:\My Documents\Test Text Import File.txt", Destination:= _ by writing in the new files path name. I would like to be able to write the name of the file I want to import in a worksheet cell and have the Macro use that as the file name for importing. I defned a variable called "FileName" and set it to a Cell A2 on Sheet2 which contained the same file name as in the Macro, (D:\My Documents\Test Text Import File.txt). I then inserted "FileName" where the files path was in the Macro. The changes we ______________________________________ ' Dim FileName As Variant ' FileName = Worksheets("Sheet2").Range("A2") Sheets("Sheet1").Select ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;FileName", Destination:= _ Range("A1")) _______________________________________ The rest of the Macro was the same. When the Macro reached ".Refresh BackgroundQuery:=False" I got an error message that the file could not be found. (Remming out ".Refresh BackgroundQuery:=False" avoided the error, but the file didn't import. Dah!) I also tried recording "Data/Get External Data/New Database Query". It seemed slower that Text Import and didn't help me with using a file path on a Worksheet I tried Pearson Software Consulting's macro "ImportTextFile", which works well but seems slow for my files. I guess wending thru 5,000 lines + of text would take some time. Is there any way to import different files w/o editing the Macro? Thanks in advance for your help, Bob S -- Bob S ------------------------------------------------------------------------ Bob S's Profile: http://www.excelforum.com/member.php...o&userid=32721 View this thread: http://www.excelforum.com/showthread...hreadid=525581 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Import Text File, File Name
Ardus, THANKS WOW. It really does help to know what you are doing! It works Great. I even added a separate Path and File Name, all by myself! With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & PathName & "\" & FileName, Destination:= _ Range("A1")) Bob S -- Bob S ------------------------------------------------------------------------ Bob S's Profile: http://www.excelforum.com/member.php...o&userid=32721 View this thread: http://www.excelforum.com/showthread...hreadid=525581 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Skipping Import Text File dialog in a Macro | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
Import text file without specifying a path macro won't work | Excel Programming |