question about automating excel

[wccppp]

Hello group,

I was trying to use Excel to open a tab delimited text file. The applicable method is Workbooks.OpenText and the syntax is:

OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

Where I'm having trouble is the argument for FieldInfo. In VBA, the argument is something like this: Array(Array(1, 2), Array(2, 2), Array(3, 2) ...). In each nested array, the first item is the column number (1 based), the second item (2) is for data type. Here 2 is for Text.

I tried something like this:

Dim $array[256][2]

Dim $i

For $i = 0 To 255

$array[$i][0] = $i+1

$array[$i][1] = 2

Next

and pass $array as the FieldInfo argument. Excel fails to open the text file, but no error was reported. Any ideas?

Thanks for your help.

wcc

[Paulie]

Hardly understood the questions, but it's pretty safe to say that if your automating Excel

Check the 'ExcelCOM UDFs'

I'll get you a link

Edit:

Here You Go!

Edited August 7, 2006 by Paulie

[ilfin]

Hi,

I have the same question about FieldInfo argument. Does anybody know the solution?

Your help will be appreciated.

thx

[wccppp]

Hi,

I have the same question about FieldInfo argument. Does anybody know the solution?

Your help will be appreciated.

thx

Not with AutoIt. I had to use VBA in Excel.

[Locodarwin]

Unfortunately, to my knowledge, it is not possible to pass arrays (let alone 2 dimensional arrays) via AutoIt to COM objects through method parameters.

The best solution I can think of is to omit the parameter or use the "Default" keyword to skip over it -- then format the appropriate columns after the import.

-S

[Valik]

Unfortunately, to my knowledge, it is not possible to pass arrays (let alone 2 dimensional arrays) via AutoIt to COM objects through method parameters.

The best solution I can think of is to omit the parameter or use the "Default" keyword to skip over it -- then format the appropriate columns after the import.

-S

Have you tried? I'm pretty sure there's an AutoIt array to SAFEARRAY conversion that takes place.

[GEA]

Hi,

I had the same problem and here is how I solved that problem:

;This is the initial VB line for delimited text file

;Array(Array(0, 2), Array( _

;7, 2), Array(41, 2), Array(47, 2), Array(61, 1), Array(98, 1), Array(99, 1))

Here is the solution for AutoIt

dim $Array1[2]=[0,2]

dim $Array2[2]=[7,2]

dim $Array3[2]=[41,2]

dim $Array4[2]=[47,2]

dim $Array5[2]=[61,1]

dim $Array6[2]=[98,1]

dim $Array7[2]=[99,1]

dim $ArrayComplet[7]=[$Array1,$Array2,$Array3,$Array4,$Array5,$Array6,$Array7]

;----------------------------------------------------------------;

.workbooks.opentext($SetLeaf,$xlwindows,1,$xlFixedWidth,$xlTextQualifierNone,False,False,False,False,False,False,"",$ArrayComplet)

Hope it helps.

[NickT]

I've been looking for this for two weeks! Thank you! Now...how is this working? I try to view the array "$ArrayComplet" using _ArrayDisplay but it returns nothing.

[water]

This thread is nearly 6 years old!

AutoIt has evolved during this time so the solutiong might be completely different.

Please open a new thread describing your problem. If necessary point to this thread.