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.
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
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.
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.