Apache OpenOffice: forum della comunità

Codice: Seleziona tutto

=SE($H2<>"";CERCA.VERT($H2;$A$2:$D$31;3;0);"")

Codice: Seleziona tutto

REM  *****  BASIC  *****
const nomefileA as string="SCARICHI.ods"
const fogliofileA as string="_da JLR"
const col1A as string="B"
const col2A as string="C"
const col3A as string="D"
const rigainizioA as integer=2

const nomefileB as string="Contributi JLR.ods"
const fogliofileB as string="Variabili LR"
const col1B as string="D"
const col2B as string="E"
const col3B as string="Q"
const rigainizioB as integer=6

Sub Main
dim percorsoEnomefileA,urlFileB,percorso as string
Dim DocB As Object
Dim vuoto as integer
Dim Dummy() 'It is an (empty) array of PropertyValues
	DocA=ThisComponent
	oSheetA=DocA.Sheets.getByName(fogliofileA)
	percorsoEnomefileA=nomefile()
	percorso=ricavapercorso(percorsoEnomefileA)
	if left(percorso,1)="/" then
		percorso=right(percorso,len(percorso)-1)
	end if
'	msgbox percorso
	urlFileB="file:///" & percorso & nomefileB
	DocB= StarDesktop.loadComponentFromURL(urlFileB, "_hidden", vuoto, Dummy)
	oSheetB=DocB.Sheets.getByName(fogliofileB)
	oCol1B=oSheetB.Columns.getByName(col1B)
	oRange1B = oCol1B.queryEmptyCells()  
	if oRange1B.count>1 then
    	oRango=oRange1B.getbyIndex(oRange1B.count-1)
    	ultimarigaB=mid(oRango.AbsoluteName,instr(oRango.AbsoluteName,"$" & col1B)+3,instr(oRango.AbsoluteName,":")-instr(oRango.AbsoluteName,"$" & col1B)-3)
    end if
	i=0
	for for1=rigainizioB to cint(ultimarigaB)-1
	oCell1A=oSheetA.getCellRangeByName(col1A & cstr(rigainizioA+i))
	oCell2A=oSheetA.getCellRangeByName(col2A & cstr(rigainizioA+i))
	oCell3A=oSheetA.getCellRangeByName(col3A & cstr(rigainizioA+i))
	oCell1B=oSheetB.getCellRangeByName(col1B & cstr(for1))
	oCell2B=oSheetB.getCellRangeByName(col2B & cstr(for1))
	oCell3B=oSheetB.getCellRangeByName(col3B & cstr(for1))
	oCell1A.setvalue(oCell1B.value)
	oCell2A.setvalue(oCell2B.value)
	oCell3A.setvalue(oCell3B.value)	
	i=i+1
	next
	DocB.close(True)
	msgbox "Aggiornato"
End Sub

function nomeFile() as string
	Doc = thiscomponent
	If (Doc.hasLocation()) Then
		sDocURL = Doc.getURL()
		DosUrl=convertfromurl(sDocURL )
	'	msgbox DosUrl
		nomeFile=DosUrl
	else
		msgbox "Questo File non ha percorso"
	End If
end function

function ricavapercorso(percorsoEnomefile as string) as string
	If instr(percorsoEnomefile,nomefileA)>0 Then
		percorso=left(percorsoEnomefile,instr(percorsoEnomefile,nomefileA)-1)
	'	msgbox percorso
		ricavapercorso=percorso
	else
		msgbox "Questo File non si chiama " & nomefileA
	End If
end function

Codice: Seleziona tutto

oCell1A.setvalue(oCell1B.value)

Codice: Seleziona tutto

oCell1A.setstring(oCell1B.string)