AUTODICE problems - OLE/automation

[isn-removed200637]

I have posted this and other quieries to VF-support but have
received no replies so I will try here...

I am using CVF 6.6 Pro.
I am a newbie as far as OLE/automation is concerned so
I started with the AUTODICE example and eventually got
it to work on my system using EXCEL 2000.

The AUTODICE example fills 12 cells in the first row
of an EXCEL sheet, beginning at A1 (R1C1) and ending
at L1 (R1C12). with randomly generated dice values
and charts the numbers as they are generated (in
steps of 200 throws).

The EXCEL cell range is defined as follows in the AUTODICE.F90 file:

	! Create a new chart
CALL VariantInit(vBSTR1)
vBSTR1%VT = VT_BSTR
bstr1 = ConvertStringToBSTR("A1")
vBSTR1%VU%PTR_VAL = bstr1
CALL VariantInit(vBSTR2)
vBSTR2%VT = VT_BSTR
bstr2 = ConvertStringToBSTR("L1")
vBSTR2%VU%PTR_VAL = bstr2
range = $Worksheet_GetRange(worksheet, vBSTR1, vBSTR2, status)
CALL Check_Status(status, " Unable to get RANGE object")

'range' is then used in later calls to AUTOSETPROPERTY
to update the contents of the EXCEL cells in the range
with the contents of single-dimension array 'cellCounts'
e.g.

status = AUTOSETPROPERTY (range, "VALUE", cellCounts)

I have tried replacing "L1" with "A12" in order to get
the numbers into the first 12 cells in the first column instead,
just to see how flexible the example is, for
future reference.
I recompiled, linked and executed,
with just this single change. The result is an EXCEL
sheet with a column of 12 zeros (in A1:A12, or R1C1:R12C1)
and the chart plots these zeros out ok. There were no
error messages. Any ideas as to the reason for the failure
to change the cell contents to the generated values?

I have also tried initialising the cells using the index
'i', i.e. CellCount(i) = i and found, when using the range
"A1" to "A12" that the initial (and final) contents of all
12 cells A1:A12 was 1 instead of ranging from 1 to 12.
There was no change to their values as the number of dice
throws increased from 1 to its maximum. Again, there were
no error messages. Any ideas as to the cause and the remedy?

(When I switched back to using range values "A1" and "L1",
the cell contents were correctly displayed as 1 to 12
initially and they updated as the dice were thrown). TIA

[jdchambless]

Hi Paul, Anthony,
Thank you guys for helping me out with this one. I think I may have gotten it figured out from your posts. If anyone comes along later and needs some tips on this, please email me (jdchambless |at| gmail |dot| com) and I'll try to help out.The only thing I have left to do is figure out how to refresh the workbook pages while the application is running, and I think I might be done ( !!! ). I am going to check the discussion boards for something, but if anyone has any ideas on how to get COM to refresh the Excel window during execution, I would appreciate it.
Thanks again,
Jason C.

[Paul_Curtis]

In my F90 code the stuff that loads the spreadsheet is bracketed by calls to $Application_SetScreenUpdating(), with final argument first FALSE while the spreadsheet is being worked on, then TRUE which causes the display to be updated and then fully accessible in that form to the Excel user.

[rasa]

Quoting - Paul Curtis

In my F90 code the stuff that loads the spreadsheet is bracketed by calls to $Application_SetScreenUpdating(), with final argument first FALSE while the spreadsheet is being worked on, then TRUE which causes the display to be updated and then fully accessible in that form to the Excel user.

I apologize for bringing this thread back to life from its long hibernation. This thread has given very useful information regarding the COM Automation with Excel. It is appropriate that more questions be asked in this thread rather than creating a new one.

I have the following questions:

1) How to add a new worksheet in Excel? I have figured out how to add data to the default sheet and to rename it (based on the information in this thread). But, I can not get the Worksheets_Add() function to work successfully (for adding a second worksheet to the existing workbook). If you have any example on adding worksheets, that will help me a lot.

2) I can get printing a real array in a single row to work. But I tried to print an array in a vertical column in Excel. Excel seems to be repeating the first value in the array to all the cells. I am guessing that I can not get the range to work properly when I select a bunch of cells in the same column. There is some discussion in this thread regarding this. Has anyone figured out to get this thing to work?

3) If you have any sample code for EXCEL COM FORTRAN interface (in additon to the ones posted in this thread) that will help.

4) Has anyone figured out to to plot charts based on the values in two columns (or more). I am assuming that to do that one should make the $Worksheet_GetRange() to work for multiple cells in a column. This goes back again to my difficulties in question (2).

I have manage to limp this far based on the info in Intel forums, but I still have a long way to go. Thanks.