Com-handles In Excel2000

[emnu]

Hi,

Im trying to make a little program for inserting CSV (Comma Separated Value) files in Excel via COM_HANDLING. The point
is that i want to open only once excel, so 1 workbook, create
the necessary sheets, activate and insert the involved CSV files
in the sheets. Creating the Excel appl, and sheets was a piece of cake.

To test the insertion of a CSV file in a particular sheet, i first opened excel, start recording a macro via <tools>,<macro>,<start recording>, then while the macro was running, inserted a CSV file via <data>,<get external data>,<text-file>, chooses the delimiter <semi colon> and begin point and the CSV file was inserted in the appropriated sheet. Then i stopped the macro recorder, which gave me the next VBA code:

/* The VBA code, recorded with a macro is the next : */

With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\tmp\022000.csv", _
Destination:=Range("A1"))
.Name = "022000"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub

Next i've tried to translate the QueryTables:Add method to Progress, but that doesn't work. I've opened the Progress Object viewer afterwards, searched for QueryTables, but this didn't help me at all !

/* BEGIN - PROGRESS CODE */

DEF VAR chExcel AS COM-HANDLE NO-UNDO.


/* Create handle to Excel Appl */
CREATE "Excel.Application" chExcel.
/* Show Excel */
chExcel:Visible=true.
/* Open workbook with first CSV file */
chExcel:Workbooks:OPEN("d:\tmp\032000.csv").
/* Create a second sheet */
chExcel:Sheets:ADD.
/* Select new created sheet, which has default label Sheet1 */
chExcel:Sheets("Sheet1"):Select.
/* Insert second csv file in selected sheet */
chExcel:ActiveSheet:QueryTables:ADD(Connection:="TEXT;D:\tmp\022000.csv",Destination:=Range("A1")). /* Doesn't work, howto ? */

/* release the handle */
RELEASE OBJECT chExcel.

/* END - PROGRESS CODE */

Does someone has a clue ?


Greetings,
Emmanuel Nuyttens
Programmer
C&C Computers Oudenaarde.

 

[NickA]

I suggest...

... you try something along the lines of ...

chExcel:ActiveSheet:QueryTables:ADD("TEXT;D:\tmp\022000.csv",chExcel:ActiveSheet:Range("A1")).

Progress won't support parameters passed with ':='.

Instead, look at the online VBA help and pass the parameters strictly in the specified order. You can skip optional parameters just by inserting extra comma's.

Also, don't make assumptions about the object in your calling parameters 'Range(A1)' won't work AFAIK, whilst 'chExcel:ActiveSheet:Range(A1)' should.

You'll also need to capture the returned QueryTable handle so that you can apply the rest of the QueryTable properties after the initial creation of the QueryTable object.

HTH

 

[saratht]

can u explain abt this xlWindows value

 

[KnutHandsome]

can u explain abt this xlWindows value

Please don't use txt spk. It's v irtng. We are mostly adults here.

The Office applications (Excel etc.) have many, many attributes that can have various values, according to (eg.) what the appearance or behaviour of the document is to be like.

Rather than having to remember each individual value, the values are assigned a constant identifier. You could hardcode the value, rather than using the identifier, but you might get thumped by the programmer who has to amend your code.

In the case above,

:

.RefreshStyle = [COLOR="Blue"]xlInsertDeleteCells[/COLOR]
.SavePassword = [COLOR="SeaGreen"]False[/COLOR]
.SaveData = [COLOR="#2e8b57"]True[/COLOR]
.AdjustColumnWidth = [COLOR="#2e8b57"]True[/COLOR]
.RefreshPeriod = [COLOR="#2e8b57"]0[/COLOR]
.TextFilePromptOnRefresh = [COLOR="#2e8b57"]False[/COLOR]
.TextFilePlatform = [COLOR="#0000ff"]xlWindows[/COLOR]

:

etc.

the document is having certain attributes set to certain values with a mixture of values (eg. False/True/0, green above) and identifiers (eg. xlInsertDeleteCells, xlWindows, blue above).

So, in English,

.TextFilePlatform = [B]xlWindows[/B]

translates (roughly) as

The platform this document originates from is Windows.

but you could have written it with the value 2 instead, ie.

.TextFilePlatform = [B]2[/B]

which would have made sense to Excel, but not to a programmer who doesn't know what the different values of TextFilePlatform represent.

Now, to be able to use those identifiers in Progress (or most other places), you must define them first, so you can use them in your code.

Somebody helpful once compiled a list of those identifiers, which I once posted in this thread.