Pals,
I am trying to create a macro that inserts a spreadsheet as a link in one cell of one column, then moves to the next column and inserts another spreadsheet in the cell next to it. The spreadsheet address is taken from a cell four rows above on each column and the insertion must go on while there are addresses on those lines.
I am using the below code:
----------------------------------------------------------
Sub ColaPaginaWeb
dim coluna as integer
dim oSheet as object
dim document as object
dim dispatcher as object
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args3(0) as new com.sun.star.beans.PropertyValue
dim args4(4) as new com.sun.star.beans.PropertyValue
rem initiate coluna
coluna = 1
rem initiate oSheet and oCells
oSheet = ThisComponent.CurrentController.getActiveSheet()
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem initiate args
args1(0).Name = "ToPoint"
args3(0).Name = "ToPoint"
While oSheet.getCellByPosition(coluna, 45).getString() <> ""
args1(0).Value = oSheet.getCellByPosition(coluna, 45)
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
args3(0).Value = oSheet.getCellByPosition(coluna, 49)
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem link com fonte web
args4(0).Name = "FileName"
args4(0).Value = oSheet.getCellByPosition(coluna, 45).getString()
args4(1).Name = "FilterName"
args4(1).Value = "calc_HTML_WebQuery"
args4(2).Name = "Options"
args4(2).Value = "0 0 0"
args4(3).Name = "Source"
args4(3).Value = "HTML_all"
args4(4).Name = "Refresh"
args4(4).Value = 86400
dispatcher.executeDispatch(document, ".uno:InsertExternalDataSource", "", 0, args4())
rem incrementa coluna
coluna = coluna + 1
rem update cells
Wend
end sub
----------------------------------------------------
Looking into the variables, they do what they are supposed to do. I see columns are increasing as they should and the websites are being called. Nonetheless, only the last one is retained in the link list and only the first column is being filled.
Indicadores IBOVESPA.ods (286.2 KB)
Any help?
Thanks