I have an Excel workbook with 2 worksheets in it. One of the worksheets imports a txt file and then the other worksheet displays data that formulas I have written have collected.
I usually import the txt file by running through the many steps with the "import data" option. This is good but I want it to be faster.
I used the macro recorder and ended up with the following code
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\Desktop\Import Folder\Test.txt" _
, Destination:=Range("A1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End WithThis code is great because it has all the settings that I always use when importing the txt file. The only thing that will need to change is the target file often and sometimes even use this excel workbook on different computers.
"TEXT;\\Desktop\Import Folder\test.txt" _Would there be a way to write a macro that when run would prompt the user to select the folder where the txt file will be located? Maybe assign the folders location to a variable that could then be inserted into the previously mentioned code?
Cheers,
Dave[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I searched the forums a bit more to try and peice some other codes together.
I found this tread that takes about a modual that creats a function that graps the name of the target folder
http://www.ozgrid.com/forum/showthread.php?t=68703
Pulling some things from there i was able to make the following code.
Private Sub CommandButton2_Click()
'Finds the location of the folder when the Get_Folder() module is used
Dim wbkSrc As Workbook
Dim wsSrc As Worksheet
Dim fso, f, g
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.getfolder(Get_Folder())
Dim Target As String
'I used these lings here to check that i have the right file location
Target = f & "\Test.txt"
MsgBox Target
'It works, I get back in a string "FOLDER\File.txt"
'Now I just need to implament it in here--->
With ActiveSheet.QueryTables.Add(Connection:=Target _
, Destination:=Range("A1"))
'Just putting Target in there doesn't work
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=Flase
End With
End SubSo Any VB people out there that can help me use the right syntax to get that "target" string variable into that script.
Thanks,
Dave
SOLUTION FOUND!!!!!
After some more searching i found this Post,
http://www.ozgrid.com/forum/showthread.php?t=55830
Norie has comented on the correct syntax.
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Target _Thanks,
Dave