Write to Excel, read results of a formula

[CampnJim]

All,

I'm trying to push a value out to an Excel spreadsheet, and then read the results of a formula in another cell that references the pushed value.

For example:
A1=5
A2=5
A3=10 (A1+A2)

If I push 30 into A2, and then Read A3 it still contains 10. I'd like it to contain the new total of 35.

So far, the only way I can find to get the formula to refresh is to actually open the Excel file.

Does anyone know how to force an attached Excel data source to refresh it's formulas?

[lgrupido]

Re: Write to Excel, read results of a formula

Can we ask WHY you want to do this?

Why not do the calculation first, then push the finished product to Excel?

I assume you have a really complex excel spreadsheet with formulas just the way you like them and your example above is a simplification, but still …

All I can think of is that you save, close the connection to the excel file, then reopen it. That might trigger a formula refresh.

[Davidk]

Re: Write to Excel, read results of a formula

I tried this using the Office::ExcelDocument class. I don't see a way to read the current value of a cell calculated with a formula... or to refresh it's contents. Reading the cell seems to be hit and miss. Either I get the original value or I get zero after updating the cells used by the formula.

How are you getting at the Excel Sheet... what's the code you're using? And what Alpha version/build are you using?

I've had more luck using OLE, but the doc, as usual, is terrible.

[CampnJim]

Re: Write to Excel, read results of a formula

Larry and David, Thanks for responding.

First to Larry, you are correct. I have an extremely complex excel spreadsheet that I will be using, and the example if definitely over simplified.

David, I'll paste me code below and I'm using the latest version I think v4.5.4.8 Build 5581

Code--------------------------------------------------------------
dim vFilePath as c = "C:\Temp\Attributes.xlsx"
dim vResults as n

dim Doc as Office::ExcelDocument
dim Sheet as Office::Spreadsheet
if Doc.Load(vFilePath)
if Doc.FindSheet(Sheet,"tbl_Attributes")
Sheet.Write(2,3,500)
Doc.Save(vFilePath)
end if
end if
delete Doc

dim Doc as Office::ExcelDocument
dim Sheet as Office::Spreadsheet
if Doc.Load(vFilePath)
if Doc.FindSheet(Sheet,"tbl_Attributes")
vResults = Sheet.Read(5,3)
end if
end if
delete Doc

[Davidk]

Re: Write to Excel, read results of a formula

It's been a very long time since I worked with Alpha and Excel. I always found Alpha methods and functions either too difficult to work with... or they were just a bit too flakey.

Using the Office::ExcelDocument class I can write to cells... but cannot read the value of a formula cell.
Using OLE, I cannot write to cells, but can read the value of a formula cell.

I know it's stupid, but combining the class and OLE I can do what you want to accomplish. It's just that it's silly.

Using the doc for the class... https://documentation.alphasoftware....Operations.xml
and Using the doc for OLE (which is wrong)... https://documentation.alphasoftware....with%20OLE.xml

here is code that uses both to write to cells and then read the results from a formula cell...

function excelOLE as c (e as p)

	debug(1)	
	dim ptr2[1,3] as C
	get_excel_data("C:\Exceltest\book1.xlsx", 1, 1, ptr2)	
	
end function

function get_excel_data as V (Filename as C, First_Row as N, First_Col as N, Data_Array as P)
	debug(1)

	'using Class code to update a cell
	dim Doc as Office::ExcelDocument
	dim filename as C = "C:\Exceltest\book1.xlsx" 
	dim SheetName as c = "Sheet1"
	dim Sheet as Office::Spreadsheet
	
	if Doc.Load(filename) 
	    if Doc.FindSheet(Sheet,sheetName) 
	        Sheet.Write(1, 1, 22) 
	        Doc.Save(filename) 
	    end if 
	end if
	delete Doc
	delete Sheet	
	
	'now use OLE to read a cell value based on a formula
	dim ExcelBook1 as P
	dim row as N
	dim col as N
	dim rows as N
	dim cols as N
	dim cell as C
	dim temp as C
	cols = Data_Array.size(2)
	rows = Data_Array.size(1)
	on error goto quitting
	ExcelBook1 = ole.getobject(Filename)
    
	for row = First_Row to First_Row + rows
		for col = First_Col to First_Col + cols
			cell = get_col(col) + row
			Data_Array[row,col] = ExcelBook1.activesheet.Cells.Range(cell)
		next col
	next row

quitting:

	ExcelBook1.close()
	delete ExcelBook1

end function

' Computes the column letter
function get_col as C (num as N)
	dim k as N
	dim c1 as C
	dim c2 as C
	k = int( mod(num/26,26) )
	c1 = if(k > 0, chr(k + 64), "")
	c2 = chr(num - (k * 26) + 64)
	get_col = ltrim(c1+c2)
end function

[CampnJim]

Re: Write to Excel, read results of a formula

That did it! Thanks for the code. I agree, it's stupid that it needs to be done that way but in the end, I just wanted it to work, and it does.

Thanks again.

[kkfin]

Re: Write to Excel, read results of a formula

That did it! Thanks for the code. I agree, it's stupid that it needs to be done that way but in the end, I just wanted it to work, and it does.

Thanks again.

File is just a file. Excel makes data and formulas alive. So nothing stupid here.

[CampnJim]

Re: Write to Excel, read results of a formula

How can I update excel cell values using OLE and not the class? Some of the cells I'm referencing have vba behind them and they're not updating their values like the non-vba ones do. I'm hoping that if I update them after opening the file via OLE the vba cells will update.

[Davidk]

Re: Write to Excel, read results of a formula

Jim, you'll need to ask Alpha. There's not enough Alpha doc on this for OLE or the Class. Code that seems to make sense doesn't work.