How to change active spreadsheet?

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!

1 Like

Please try to be clearer about the relevant facts…
Since your mentioned file is typed .xls (Better use .ods !) you may know MS-speak.

Workbook  <--> Spreadsheet document
Worksheet <--> Spreadsheet
File      <--> File.

(One document may contain many sheets!)

And:

How does your recorded macro do this?
The dispatcher commands for Copy and for Paste both need to use the CurrentSelection of the correct one of both open documents, and both need a frame of the respective document as the DispatchProvider (badyl named “document” by the recorder).

change the line above to

 document   = oTargetDoc.CurrentController.Frame
2 Likes