Excel and Fortran ...Again

[sumitm]

Sorry to breach this topic again but I have a need to read data from an excel spreadsheet from just one cell. I have used the autodice program to write data to any cell of excel but was struggling to get data backfrom any particular excel cell. Can anyone tell me which function of the excel97.f90 module will get me the value of a real number in a particular cell. This cell data gets changed continuosly from a transducer and the program needs to read that value in.

I tried the canaima software also. I was able to compile the read_excellite and write_excelLite programs but I was getting the following error in the ExcelRead file compilation.

Linking...
ExcelRead.obj : error LNK2001: unresolved external symbol _F90SQLDRIVERCONNECT@36
ExcelRead.obj : error LNK2001: unresolved external symbol _F90SQLPREPARE@16
ExcelRead.obj : error LNK2001: unresolved external symbol _F90SQLEXECUTE@8
Debug/ExcelReadLite.exe : fatal error LNK1120: 3 unresolved externals
Error executing link.exe.

I am trying to avoid the DSN method and instead try the other direct method.

Any ideas

Thanks in advance

Sumit

[michael_m2]

Use the function$Worksheet_GetRange to get the range from which you want to read the data. Then use the function AutoGetProperty to get the value from that range. Pass 3 parameters to AutoGetProperty: the first is the range that was returned from $Worksheet_GetRange; the second should be the string "VALUE" (with the quotes); and the third should be a string variable that will get the value of the cell you want. You then need toconvert this third argument to a real (or integer, or double precision, etc...).

Hope that helps.

[sumitm]

Thanks Michael,

That worked. Is there a way I could get more than one cell value in the string .

Seems to work for one cell (range function uses vbstr1 value for both inputsas shown below)

$Worksheet_GetRange(worksheet, vBSTR1, vBSTR1, status)

status = AUTOGETPROPERTY (range, "VALUE", string)

Also if the excel file is already open how do I access it.

Thanks

[michael_m2]

I'm glad to hear that it worked.

It looks like there should be a way to make it work for a whole range of cells (not just a single cell) -- but I have not figured out how to do that. So I just use a loopand get one cell's value at a time.

And since I use it to only get one cell at a time, I leave off the third argument in $Worksheet_GetRange (which is an optional argument).

Also, just to clarify, the third argument ofAutoGetProperty doesn't have to be a string (as I implied in my first post). If you know the type of the data that your're getting from the cell (integer, real, double precision,...) then you can just pass a variable in of that same type. That way you won't have to worry about the step of converting the string to the type that you want.

As for your last question: I haven't run into any problems accessing the file even if it was already open before my program began. You can just let the program open it up again. I think it will open a new instance of Excel.

Michael

[sumitm]

Michael,

Thanks for the input. It took me a while to get back to it. Can anyone advise how to modify this code to repeated access an open excel file.

First Time I open the excel file and read data from some cells (no problem). Then after some calculations I throw back a value to one cell and read in some more values from the first set of cells for a new calculation (no problem).

The problem starts here:

I do not want to open and close the excel workbook each time. The excel workbook needs to remain open and I am not sure where the cominitialize needs to start and end and release objects. The excel workbook has a Dynamic Data Exchange Link to gather data from a couple of transducers and hence needs to remain open

! Initialize object pointers

CALL INITOBJECTS()

CALL COMINITIALIZE(status)
! if (excelopen.eq. .false.) then

CALL COMCREATEOBJECT ("Excel.Application.11", excelapp, status)
IF (excelapp == 0) THEN
WRITE (*, '(" Unable to create Excel object; Aborting")')
CALL EXIT()
END IF
CALL $Application_SetVisible(excelapp, .TRUE.)

workbooks = $Application_GetWorkbooks(excelapp, $STATUS = status)
CALL Check_Status(status, " Unable to get WORKBOOKS object")
open(12,file='filepath.txt')
read(12,*) filepathstr
close(12)
workbook = Workbooks_Open(workbooks,trim(filepathstr),$STATUS = status)
excelopen=.true.
CALL Check_Status(status, " Unable to get WORKBOOK object; ensure that the file path is correct")
endif ! excelopen

! Get the worksheet values : This works fine
vInt%VT = VT_I4......

! Release all objects
CALL RELEASEOBJECTS()
CALL COMUNINITIALIZE()

[michael_m2]

Hello:

I'm not sure if I fully understand your problem, but I don't think there should be anything wrong with keeping Excel open until you don't need it anymore. (In fact, that's probably preferable rather than to keep opening and closing it.) Call ComInitialize before you try accessing anything in Excel (as you do in your sample code snippet), and call ComUninitialize only after you're totally done accessing Excel (also as you do in your code snippet).

I generally call InitObjects before ComInitialize, but I'm not sure if the order of these 2 makes a difference. Also, I call RealeaseObject before ComUninitialize -- again, I'm not sure what would happen if you switched the order of these 2 calls.

Michael

[jdchambless]

Hi Sumit,
I just wanted to know if you found a solution to your problem. I am trying to figure this out as well, since Excel will already be open when it calls the COM Server DLL I'm writing. I've found that I can use

"CALL COMGetActiveObjectByProgID("Excel.application",excelapp,status)"

instead of

"CALL COMCREATEOBJECT ("Excel.Application", excelapp, status)"

to keep from opening a new Excel application. From there, I use

AllWorkbooks = $Application_GetWorkbooks(excelapp, status)

to populate "AllWorkbooks" with all the local workbooks. From here, I'm unsure. Since we don't want to open another new workbook, we certainly don't want to call

workbook = Workbooks_Open(workbooks,trim(filepathstr),$STATUS = status).

I've tried using the function "Workbooks_GetItem($OBJECT, Index, $STATUS)", but I am unsure as to what the value of "Index" should be (I know that $OBJECT is just "AllWorkbooks" and $STATUS is just our status variable). If you (or anyone else) have solved this problem, or have any other ideas, I'd appreciate hearing about it.
Thanks,
Jason C.