Sign in to follow this  
Followers 0
wccppp

question about automating excel

9 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Paulie

Share this post


Link to post
Share on other sites

Hi,

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

Your help will be appreciated.

thx

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0