Query Table - Extract Data From Text File by Column - TextFileColumnDataTypes

[Cerkes]

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

[mdmackillop]

Can you post a sample txt file (you may need to zip it) Go Advanced /Manage Attachments.

[Cerkes]

Dear mdmackillop,


TXT and XLS files are attached.


Thanks in advance for your help


br

[Cerkes]

Dear All,

I found a solution from another forum.

Just for the purpose of someone needs help in the same situation, the solution can be seen below.

All the best

Cerkes

---Added this code to the macro above--------------------

Dim strarray As Variant
Dim iarray() As Integer
Dim i As Integer
strarray = Split(Range("B7").Value, ",")
ReDim iarray(UBound(strarray))
For i = 0 To UBound(strarray)
iarray(i) = strarray(i)
Next i

---- made a change in the code like below-----------

With ActiveSheet.QueryTables.Add(Connection:= _
.............
.............
.............
.............
.............
.............
.............

.TextFileColumnDataTypes = iarray


End With