I have a macro which I run from a new LO spreadsheet using Tools…Macro…Run Macro. The first step loads a spreadsheet from my hard drive:
sub CopyBondFile
Dim oDoc As Object
Dim sUrl As String
Dim oTargetDoc As Object
Dim OrigFile As String
OrigFile = StarDesktop.getActiveFrame().Title
oTargetDoc= ThisComponent Rem TargetDoc is the spreadsheet you are working from
Dim Prop(0) as New com.sun.star.beans.PropertyValue
Prop(0).name="FilterName"
Prop(0).value="MS Excel 97"
sUrl = convertToURL("c:\Backup\nvts\BondFile.xls")
if fileExists(sUrl) then
oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop())
else
msgbox "Not found"
end if
'#The macro then copies a range from a named sheet in BondFile.xls and pastes it into the' '#spreadsheet I'm running the macro from (oTargetDoc).'
'#At this point, I have two spreadsheets open -- BondFile.xls, which is the visible one, and untitled.ods,'#
'# which has the pasted range but is not the immediately visible one.'#
'#I have a routine I got by recording a macro. It is supposed to sort the pasted data in untitled.ods:'#
dim document as object
dim dispatcher as object
Rem How to make TargetSheet theactive document?
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel"
args2(0).Value = true
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())
rem dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, Array())
dim args4(9) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ByRows"
args4(0).Value = true
args4(1).Name = "HasHeader"
args4(1).Value = false
args4(2).Name = "CaseSensitive"
args4(2).Value = false
args4(3).Name = "NaturalSort"
args4(3).Value = false
args4(4).Name = "IncludeAttribs"
args4(4).Value = true
args4(5).Name = "UserDefIndex"
args4(5).Value = 0
args4(6).Name = "Col1"
args4(6).Value = 2
args4(7).Name = "Ascending1"
args4(7).Value = true
args4(8).Name = "IncludeComments"
args4(8).Value = false
args4(9).Name = "IncludeImages"
args4(9).Value = true
dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args4())
end Sub
Instead, however, it seems to sort data in BondFIle.xls. I assume the problem is that ThisComponent.CurrentController.Frame is BondFile.xls when I want it to be untitled.ods. How do I make untitled.ods the “ThisComponent” doc?
I have tried a bunch of different potential solutions from this BBS but nothing has worked. Any help much appreciated!