Reading and Writing of Excel

[Hu__Andy]

Hi, everyone.

I was surprised to find that Fortran can read and write Excel by COM. IVF has provided a series of functions and subroutines. However, in the process of using these functions and subroutines, I encountered some problems.  

For some reasons, I want to read and write data from an Excel that I have manually opened.  The implementation plan I expected is to obtain the names of all Excel currently open, find the name of the workbook I want to read among them, then read this workbook. But I haven't found the relevant function or subroutine to get the names of all Excel currently open. Could any kind-hearted person tell me the name of this function or subroutine? 

In addition, I also want to read the data in Excel which is currently open and active. I guess this function should have this function,

$Application_GetActiveWorkbook(excelapp, $STATUS = status)

But it may be that I used it improperly and failed to call it all the time.

Any information provided will be greatly appreciated.

[andrew_4619]

I don't know the full answer to your question. I use the code below. If you look at the call workbooks = $Application_GetWorkbooks(excelapp, status) this returns an integer  pointer to a "workbooks" object. The workbooks object contains pointers to all open workbook objects and and has a number of methods and properties defined if you search online.   If you look at VBA  there is an item count and you can look over the workbook items by index (in workbooks getting things such as name.  Looking at the COM wizard output it doesn't create any tools to access the workbooks object though it would be possible it make these I think but without a detail knowledge of COM I think this will be a difficult job. I would look at working a different way.

module subroutine ExcelInit(gfile,ngfile,istat)
      use lang, only: gmeswt
      use axs6, only: inftxt
      IMPLICIT NONE   
      INTEGER, intent(in)          :: ngfile
      integer, intent(inout)       :: istat
      CHARACTER(len=*), intent(in) :: gfile
      INTEGER(4)                   :: status
      INTEGER                      :: i  
      CHARACTER(2)                 :: office_version_string
      character(len=:), allocatable :: Gversion
      istat=0         ! assume success
      excelapp  = 0   ! Initialize object pointers
      workbooks = 0
      workbook  = 0
      worksheets= 0
      worksheet = 0 
      ! Create an Excel object.  Try Office 2010 (13), then 2007 (12), then 2003 (11).
      CALL COMINITIALIZE(status)
      CALL COMCREATEOBJECT ("Excel.Application", excelapp, status)
      if ( excelapp /= 0 ) then
            Gversion = $Application_GetVersion(excelapp, status)
            if ( status == 0 ) then
                call inftxt("Detected Excel Version "//trim(Gversion) )
            endif
            goto 1
      endif 

      DO I=17,11,-1 ! 11=2003, 12= 2007,  14=2010, 15=2013 (There is No 13!), 16 = 2016 17 for good look......
        WRITE (office_version_string, '(I2)') I
        CALL COMCREATEOBJECT ("Excel.Application."//office_version_string, excelapp, status)
        if ( excelapp /= 0 ) exit ! If successful, exit the loop
      END DO
      1 continue      
      IF (excelapp == 0) THEN
          call gmeswt(" Unable to create Excel object; Aborting")
          goto 999
      END IF
      CALL $Application_SetVisible(excelapp, .FALSE._2,status)
      CALL $Application_SetScreenUpdating (excelapp, .FALSE._2,status)
   
      ! Get the WORKBOOKS object
      workbooks = $Application_GetWorkbooks(excelapp, status)  
      if (status  <  0) then
          workbooks=0 ! pointer can be junk on fail and will crash on uninit!  
         call gmeswt(" Unable to get WORKBOOKS object")
         goto 999
      endif

      
      workbook = Workbooks_Open(workbooks, gfile(1:ngfile), $status=status)
      if (status  <  0) then
         workbook=0 ! pointer can be junk on fail and will crash on uninit!  
         call gmeswt(" Unable to get WORKBOOK object; ensure that the file path is correct")
         call gmeswt('WORKBOOK: '//gfile(1:ngfile))
         goto 999
      endif
        
      !WE NEED TO SET WORKSHEET USE ExcelSetSheetHandleByName in apllication!!!!!!!!!!!!!!
      return    
999   continue      !jump here if exit with error
      istat=-1
      call ExcelUnInit
END subroutine ExcelInit

[jimdempseyatthecove]

Why are you structuring your objective "upside down"? By this I mean, why not have (introduce) a VBA script, hot-key activated, that for the current (in focus) workbook and different key for all open workbooks, execute a defined Fortran process/procedure?

What are you expecting to happen with your current method when you may have multiple instances of Excel running?

I imagine that you could use a "magic" cell containing a pre-defined signature E.G A1="Abracadabra"

You would still have the issue of having two instances of Excel running, each with magic cell.

Jim Dempsey

[andrew_4619]

A very valid question Jim, however in VB is would be trivial to make a list of all instances pointed to in the workbooks object and allow the user to pick one. It would be not so hard in Fortran if COM exposed the necessary methods.  

[MWind2]

Though the specific needs I don't know, I would try first to send and/or receive data by vba in excel to a fortran dll that would do whatever is needed.