Cerkes
09-03-2017, 12:05 PM
Dear All,
I have a question regarding the QueryTable thrugh VBA ..
Basically I am trying to prepare dynamic QuaeryTable which is extracting whole rows of specific columns (There re 300+ columns) in a text file.
In the recorded macro "TextFileColumnDataTypes" part allows us to make selection that to be extracted can be seen below
.TextFileColumnDataTypes = Array(1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,1 ,1,1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9)
When I want to change the column to extract I need to modify Array () part of code therefore I decided to create Array part of the code in excel through formula (Let's say in "B7") and then I assigned this to code like this.
.TextFileColumnDataTypes = Range("B7").Value
Now, the problem is when I run the macro, I get an error message (invalid procedure call or argument - 5) and macro does not work. It seems the problem is VBA returns this to
.TextFileColumnDataTypes = "Array(1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,1 ,1,1,9...........,9,9)"
Please note that there are quotation marks and this could be related to variable types but unfortunately I could not find a way to get this without quotation ("") marks.
My questions; is there a solution for this or is there a simple way to get specific column (it’s a kind of HLOOKUP) of a text box.
My whole code is below
Thanks in advance for your kind help…
---Cerkes---
Sub TextQueryTest()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\TempTest_02.09.2017.txt", Destination:=Range("$D$1"))
.Name = "TempTest_02.09.2017_1"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "?"
.TextFileColumnDataTypes = Range("B7").Value
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I have a question regarding the QueryTable thrugh VBA ..
Basically I am trying to prepare dynamic QuaeryTable which is extracting whole rows of specific columns (There re 300+ columns) in a text file.
In the recorded macro "TextFileColumnDataTypes" part allows us to make selection that to be extracted can be seen below
.TextFileColumnDataTypes = Array(1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,1 ,1,1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 ,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9, 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9)
When I want to change the column to extract I need to modify Array () part of code therefore I decided to create Array part of the code in excel through formula (Let's say in "B7") and then I assigned this to code like this.
.TextFileColumnDataTypes = Range("B7").Value
Now, the problem is when I run the macro, I get an error message (invalid procedure call or argument - 5) and macro does not work. It seems the problem is VBA returns this to
.TextFileColumnDataTypes = "Array(1,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,1 ,1,1,9...........,9,9)"
Please note that there are quotation marks and this could be related to variable types but unfortunately I could not find a way to get this without quotation ("") marks.
My questions; is there a solution for this or is there a simple way to get specific column (it’s a kind of HLOOKUP) of a text box.
My whole code is below
Thanks in advance for your kind help…
---Cerkes---
Sub TextQueryTest()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\TempTest_02.09.2017.txt", Destination:=Range("$D$1"))
.Name = "TempTest_02.09.2017_1"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "?"
.TextFileColumnDataTypes = Range("B7").Value
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub