F90 to Excel: Function Worksheet_Add

[mcadorno]

The goal is to open several new sheets on the Excel workbook and paste data calculated in Fortran.

I want the sheets to appear in the workbook in the order they are created, so that Sheet1 will be the leftmost, sheet2 the next one to the right, etc.

Default behavior in VBA of the Worksheets.add method is  that a new sheet is added before the active sheet. Using the he optional After argument one can pass a variant to the sheet by  the referencing  the sheet after which you want put the new sheet.

The Worksheets_Add function in modExcel9A corresponds to the VBA method

Function Worksheets_Add ($OBJECT, Before, After, Count, Type, $STATUS)

where $Object is the handle to the active sheet and Before, After, Count, Type are VARIANTs.

The function is working perfectly when used without optional arguments.

When I set AFTER=varIdx or AFTER=varName the funtion returns an error.

I have tried to eliminate all the obvious causes, such as that the Workbook, Application etc have not been initialized correctly.

I have also pulled the name of the sheet and the index number of the sheet using $Worksheet_GetName and $WorkSheet_GetIndex to make sure I'm using valid reference. I have ruled out that my conversion from F90 string to VARIANT string is faulty as I use the same conversion in other portions of the code.

Does anyone have experience with this specific call to the VBA functions? Greatly appreciate any help.

I'm still using the old CVF 6.6 compiler as I havent had time to port it to Intel, but I have checked that tha interfaces on the modExcel16 are basically the same as the ones I use.

Attached an example of the code, including  the source code for excel interfaces.

[andrew_4619]

I had a brief look at this, I don't seem to use $Worksheets_Add though I do some things with worksheet index and name. I would need to make an example in the latest compiler etc. I  do not use CVF and my windows and excel is 64 bit....... But I expect this is a usage problem, exel/com can be quite fussy and sometime hard to figure. You made me laugh by the way "I'm still using the old CVF 6.6 compiler as I haven't had time to port it to Intel",  No time in  20 years!!

For what it is worth a solution I have made use of is pre-creating an excel template with sheets, formats, graphs etc and then just populating tables in it from Fortran. If that is an option it saves a great deal of coding time.

[mfinnis · Accepted solution]

The after and before variables are variants of type VT_DISPATCH with %vu%ptr_val set to a worksheet object.

[andrew_4619]

mfinnis is correct.  I put your files in the latest ifort. A couple of initial issues,  ComReleaseObject is a function and you call as a subroutine, and I removed !DEC$ ATTRIBUTES REFERENCE :: Filename in Workbooks_Open to compile and link,

I added:

    function fCVT_ptrToVariant (n)
        integer(handle), intent(IN)::    n
        type(VARIANT)::                fCVT_ptrToVariant
        fCVT_ptrToVariant%VT = VT_DISPATCH
        fCVT_ptrToVariant%VU%PTR_VAL = n
    end function fCVT_ptrToVariant

    ! Now add new sheet using AFTER argument: get the index number 
    ! of the active sheet, convert to VT before passing
    !idx = $Worksheet_GetIndex (hSheet)
    !VTidx = fCVT_Int4ToVariant (idx)
    VTidx = fCVT_ptrToVariant (hSheet)
    hSheet = Worksheets_Add(hWksheets,AFTER=VTidx,$STATUS=stat)
    count = Worksheets_GetCount(hWksheets)

The code above then seems to work. You have some problems later in your example the sheet by name makes no sense as worksheet_add needs a sheet object not a name or index but I went no further.

[mcadorno]

just compiled your code and it worked perfectly. many thanks both to  you and mcfinnis.

One further question regarding integer(handle);  I used integer (4)  and it works. Why do you use a pre-defined parameter?

[andrew_4619]

well when I made my tests I did a 64bit build and it would need to be integer(8) not integer(4), if you do not use handle you are storing up problems for if/when you use go to 64 bit.

[mcadorno]

hello Andrew_4629 and mfinnis, thanks for your suggestions! I'll try out the code and let you know.

BTW, the thing with the 20 years is that this program is very large and I worked on it unti about 2010; it continued to be used by me ad others and worked pretty well until recently when we wanted to make some improvements. No the choice was either the learning curve of the new VisualStudio, which looks daunting, or just work with the 32 bit old version. 

Though I realize that I'll have to face 64bit VisualStudio soon...

[Steve_Lionel]

I'll add that it is bad practice to hard-code type kinds in your declarations, especially as they are non-portable. Use of named (PARAMETER) constants is a much better method.