QueryTables DataTypes & ColumnWidths Arrays as variables

[forumreader]

How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names. Thanks for help

Dim dt As Variant
Dim cw As Variant
dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2"
cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:\BOMOE\BTR\bfro1.txt", Destination:=Range("A1"))
.Name = "BFRO1"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(dt)
.TextFileFixedColumnWidths = Array(cw)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Andrew Poulsom]

Welcome to the Board.

Try:

dt = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

and:

.TextFileColumnDataTypes = dt

 

[forumreader]

Thanks for the quick reply. It worked on both dt and cw. I had tried that before, but not since I changed them to variant instead of string. I get drain bamage sometimes. Now I can finish the macro to import larger than 65536 line, text file import. Works by splitting large text file into subfiles of 65530 lines and import each one with same data types and column widths to separate worksheets. Again thanks.

 

[SandeepKumarMYVN]

Welcome to the Board.

Try:

dt = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

and:

.TextFileColumnDataTypes = dt

Hi,

Instead of hardcoding the values, is it possible to dynamically pull the values from excel sheet and pass them to Array. Please help.

Lets say, the Data types are present in sheet "Input" Column 'A' and Column widths in Column 'B'. Please help me in how to pass these values to Array.


Thanks,
Sandeep.

 

[Andrew Poulsom]

Try eg:

.TextFileColumnDataTypes = Application.Transpose(Worksheets("Input").Range("A1:A3").Value)

 

[SandeepKumarMYVN]

Hi Andrew,

Thanks for the reply.. I tried the code.. but I am getting Invalid call or Argument.. any idea on this?
I am using Excel 2010

.TextFileFixedColumnWidths = Application.WorksheetFunction.Transpose(Worksheets("Input").Range("E1:E46").Value)

 

[Andrew Poulsom]

What's in E1:E46? Does your text file have 46 columns?

 

[SandeepKumarMYVN]

those fields have column widths like E1 : 4 E2: 5 E3:4 like that...

 

[Andrew Poulsom]

And there are no blanks? The code I posted worked for me.