Excel/Fortran interface assistance

[andrew_4619]

First my apologies for raising yet another Excel/Fortran thread I know there are numerous historic ones already and I have spent some time going through them. Using the Autodice example in Composer XE 2013 and also taking some tips/code from the useful posts by Paul Curtis (thanks!) and others I have  managed  the implement a scheme to populate data from my Fortran application into a spreadsheet template which has a series of pre-prepared formats and charts. I have some useful utilities to insulate the hideousness of the interface from my working code and it seems to be working well so far.

I have a couple of frustrating problems. I noticed during testing that if the active sheet in excel is a chart then we fail as there are no cells to populate in the worksheet object! I can trap the error and tell the user to go away and sort it out but something more friendly must be possible I think. I can get the active worksheet object and interrogate the name of the sheet:

[fortran]worksheet = $Workbook_GetActiveSheet(workbook, status)

gbuf = $Application_GetName(worksheet, status) !get name of active sheet object - works! [/fortran]

It appears you can change the active sheet, however I can't see where you get a pointer to the sheet you actually want.

[fortran]CALL $Worksheet_Activate($OBJECT, status) !we can activate a specific sheet if we know the pointer to it! [/fortran]

Also you can enquire the sheet Names in the workbook, the code below has OK status and returns a pointer value but I do not know what data type is being pointed at or how the retrieve said data:

[fortran]ifred = $Workbook_GetNames(workbook, status) [/fortran]

 1] Does anyone have any insight into this area to give some tips?

 2] Also is there anywhere that gives some guidance on the use of the excel interface routines that are generated by the module wizard? There is some data on MSDN but it is quite hard to try to cross correlate and/or find information.

 Thanks In Advance, Andrew

[IanH]

I don't think Workbook_GetNames returns sheet names.  I think it might return a collection of Name objects - the things created in Excel when you use the Ctrl-F3 keyboard shortcut (in the language versions I use anyway).

Off the top of my head (not all of these are things that I have done or if I have done it, I've not looked to see how I've done it in my code, so I might be a bit off with them) - if you want to get the handle of a particular sheet, you could iterate throught the collection returned by Workbook_GetSheets and query the name (Worksheet_GetName).  If you want a particularly named sheet you may be able to query that directly via the Worksheets_GetItem method (use the name as an index). 

My preferred way of operating is for the VBA code that calls the Fortran to pass in a worksheet handle, and then work directly with that - basically the client of the DLL decides what the DLL may scribble on.

Changing the active sheet isn't required for most things - once you have a handle to a sheet or range you can just work with that.

As for a reference - I used msdn with appropriate Fortran glasses on, combined with a lot of experimentation a while back to give a library of helper routines that work that I earnestly try not to break.

[andrew_4619]

IanH: Many thanks for tips, I have spent some more time beating my head further on this. The code below suggest the sheet information is in a VT_DISPATCH variant stucture but if so how do a get anything useful from this, what method/tool to use? Or perhaps more to the point how can I " if you want to get the handle of a particular sheet, you could iterate throught the collection returned by Workbook_GetSheets.....". The answers are probably quite simple but I don't seem to be finding them!!

[fortran]ibill=$Application_GetSheets(workbook, status) ! ibill is pointer to a collection of sheet objects?
call COMQUERYINTERFACE (ibill, IID_IUnknown , iinterface, status) !returns OK interface ptr returned = ibill, promising?
call COMQUERYINTERFACE (ibill, IID_IDispatch , ifred, status) !returns OK ifred set to point Presumably at a variant of type VT_DISPATCH?[/fortran]

Any help would be greatrfully accepted! Andrew.

[andrew_4619]

Having spent several hours looking at this problem the answer is very (embarrasingly) simple! Isn't it always. For the benefit of anyone that follows I have attached a code snipped that gets the handle of all the sheet objects in a workbook and gets the sheet name. As IanH pointed out the  sheet does need to be the active sheet to be able to write to it which is useful to know.

[fortran]worksheet=0
isheets=$Workbook_GetSheets(workbook, status)
CALL VariantInit(vInt)
vInt%VT = VT_I4
do l1=1,20
    vInt%VU%LONG_VAL = l1 !look at sheet no l1
    isheet=0
    isheet = Worksheets_GetItem(isheets, vInt, status) ! get handle for sheet l1
    if(status.ne.0 .or. isheet.eq.0 ) exit ! run out of sheets I guess
    gbuf = $worksheet_GetName(isheet, status) ! get name of sheet object
    if(gsheet_i_want(1:lensht).eq.gbuf(1:lensht) ) then ! bingo found the sheet I want
        worksheet=isheet
        exit
     endif
enddo
status = VariantClear(vInt)[/fortran]

[IanH]

I wasn't sure when writing my previous post, but if you know the name of the worksheet of interest you can simply index the Sheets collection with that.  Something along the lines of:

[fortran]    USE IFCOM, etc...

TYPE(VARIANT) :: name     ! Name of the desired sheet, as a variant
    !***************************************************************************
    ! Construct variant with the desired worksheet name.
    CALL VariantInit(name)
    name%VT = VT_BSTR
    name%VU%PTR_VAL = ConvertStringToBSTR('NameOfInterest')
    
    ! Get the specific sheet.
    isheet = Sheets_GetItem(isheets, name, status)

...
[/fortran]

[andrew_4619]

Yes thanks Ian that is  a more efficent way and it works after correcting the typo (worksheet_getitem). I had tried something similar previously that failed to work but I am not sure what I did wrong as I have already tidied up the trial code. Anyway thanks for the assitance, wanting to keep all that Variant bstring rubbish out of my main code I integrated that option into a general "get a worksheet handle based on a number of different posible section criteria" (active, sheet number, sheet name etc)  routine.

I think in retrospect one of the main problems is that the excel module has a large number of routines so it is not easy to find what you want or know what the capabilities/function of some of the routines are. A mixure of guess work, trial and error, MSDN and internet seaches is required. The reality is once you know what you are doing you can make a relatively small number of utility routines that do all that you want without having to have any understanding of the deeper workings of the interface and data structure...

An overview and referance document from Intel would be a huge help to quite a number of Intel Fortran users but guess that would be quite a big task.