Sintax for Excel Command Querytables

Osmar Morais

New Member
I´m trying to import text file on Excel with "QueryTables" command but I´m having problem with "TextFileColumnDataTypes" parameter to define the fields types

I´m listing below the command. Except for the last line the rest fo command is executing properly, but, imports characters fields cutting left zeroes (for example the field invoice number has a content "0001234" and it is changed to "1234"); If I keep the last line I had sintax error. I get this sintax from a Excel macro "Array(2, 2, 1, 1, 2, 1, 1, 4, 2, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 1)" .

ASSIGN chWS1:NAME = "Invoices".
ASSIGN chQT = chWS1:QueryTables:ADD("TEXT;" + cFile1, chWS1:Range("A1")).
ASSIGN chQT:NAME = "Test"
chQT:FieldNames = True
chQT:RowNumbers = False
chQT:FillAdjacentFormulas = False
chQT:preserveFormatting = True
chQT:RefreshOnFileOpen = False
chQT:RefreshStyle = 1 /*xlInsertDeleteCells*/
chQT:SavePassword = False
chQT:SaveData = True
chQT:AdjustColumnWidth = True
chQT:RefreshPeriod = 0
chQT:TextFilePromptOnRefresh = False
chQT:TextFilePlatform = 2 /*xlWindows*/
chQT:TextFileStartRow = 1
chQT:TextFileParseType = 1 /*xlDelimited*/
chQT:TextFileTextQualifier = 1
chQT:TextFileConsecutiveDelimiter = False
chQT:TextFileTabDelimiter = FALSE
chQT:TextFileSemicolonDelimiter = TRUE
chQT:TextFileCommaDelimiter = False
chQT:TextFileSpaceDelimiter = FALSE
chQT:TextFileColumnDataTypes = Array(2, 2, 1, 1, 2, 1, 1, 4, 2, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 1).

I know I could populates the worksheet cell by cell (and line by line) but the performance isn´t acceptable.

My problem is to import character fields without losing information.

Thanks for any help,
 
Top