vbscript and .QueryTables.Add

[Robert Evans]

I am writing a generic vbscript that takes delimited files and makes Excel files out of them.

Using
With objWorksheet1.QueryTables.Add("TEXT;C:\Sample.TXT", objWorksheet1.Range("$A$1"))
.Name = "Sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = 1
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = 1
.TextFileTextQualifier = 1
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "§"
.TextFileColumnDataTypes = Array(2,2,2)
.TextFileTrailingMinusNumbers = True
.Refresh False
End With

This works just fine, however I NEED to have TextFileColumnDataTypes to be variable as each incoming file has a different number of columns and data types.

But if I do
dim ColDataTypes(3)
ColDataTypes(0) = 2
ColDataTypes(1) = 2
ColDataTypes(2) = 2
and change .QueryTables.Add to now say

.TextFileColumnDataTypes = ColDataTypes
or
.TextFileColumnDataTypes = Array(ColDataTypes)

I get "Invalid procedure call or argument"

There must be a way to do this....please help

 

[John_w]

dim ColDataTypes(3)
ColDataTypes(0) = 2
ColDataTypes(1) = 2
ColDataTypes(2) = 2
and change .QueryTables.Add to now say

.TextFileColumnDataTypes = ColDataTypes

VBScript arrays are zero-based, so Dim ColDataTypes(3) declares an array of 4 elements, but you define only 3 elements - (0), (1) and (2). Try changing the Dim to:

Dim ColDataTypes(2)

 

[Robert Evans]

doh

thanks much