Post by cjm on Apr 5, 2015 16:06:48 GMT
The macro listing given below reads the value of a cell in a spreadsheet and deducts the value of another cell. The result is then pasted back to the first cell. In this particular application it is used to update the balances of a loans over a number of similar, individual sheets in a spreadsheet, by the periodic down payments.
I do not claim that it is very difficult to write but it took me a great deal of time. The information is scattered over many sources. At least, I could not find a consolidated example. Perhaps this makes life a bit easier for someone. The basic listing can be adapted to a variety of applications. For most people, I assume, the for...next structure should be removed as it accesses the sheets one after the other. In many applications, I assume, only a single sheet will be used.
I do not claim that it is very difficult to write but it took me a great deal of time. The information is scattered over many sources. At least, I could not find a consolidated example. Perhaps this makes life a bit easier for someone. The basic listing can be adapted to a variety of applications. For most people, I assume, the for...next structure should be removed as it accesses the sheets one after the other. In many applications, I assume, only a single sheet will be used.
Sub MainBetaalSkuld
dim oDoc rem spreadsheet to be manipulated
dim oSheets
dim i%
dim oActiveSheet As Object
dim oOuSkuld
dim oBetaal
oDoc=ThisComponent rem accesses the current spreadsheet document
oSheets=oDoc.sheets rem load individual sheets from that spreadsheet
rem take the sheets one by one
For i=0 to oSheets.getCount-1
rem open the sheet - this is where the men get separated from the boys!
ThisComponent.currentController.setActiveSheet(oSheets.getByIndex(i))
ThisComponent.getCurrentController.getActiveSheet(oSheets.getByIndex(i))
oActiveSheet=oSheets.getByIndex(i)
rem get value of specific cell
oOuSkuld=oActiveSheet.getCellByPosition(3,10).getValue() rem cell D11 - of course you can select your own cell here
rem get value of another cell in same sheet
oBetaal=oActiveSheet.getCellByPosition(6,13).getValue() rem cell G14 - of course you can select your own cell here
oOuSkuld=oOuSkuld-oBetaal rem deduct second value from first
oActiveSheet.getCellByPosition(3,10).setValue(oOuSkuld) rem paste result back into D11
next
End sub
dim oDoc rem spreadsheet to be manipulated
dim oSheets
dim i%
dim oActiveSheet As Object
dim oOuSkuld
dim oBetaal
oDoc=ThisComponent rem accesses the current spreadsheet document
oSheets=oDoc.sheets rem load individual sheets from that spreadsheet
rem take the sheets one by one
For i=0 to oSheets.getCount-1
rem open the sheet - this is where the men get separated from the boys!
ThisComponent.currentController.setActiveSheet(oSheets.getByIndex(i))
ThisComponent.getCurrentController.getActiveSheet(oSheets.getByIndex(i))
oActiveSheet=oSheets.getByIndex(i)
rem get value of specific cell
oOuSkuld=oActiveSheet.getCellByPosition(3,10).getValue() rem cell D11 - of course you can select your own cell here
rem get value of another cell in same sheet
oBetaal=oActiveSheet.getCellByPosition(6,13).getValue() rem cell G14 - of course you can select your own cell here
oOuSkuld=oOuSkuld-oBetaal rem deduct second value from first
oActiveSheet.getCellByPosition(3,10).setValue(oOuSkuld) rem paste result back into D11
next
End sub