[Solved] Macro trouble when trying to insert a worksheet

The Application Programming Interface and the OASIS Open Document Format

[Solved] Macro trouble when trying to insert a worksheet

Postby mikelongbeach » Tue Jan 19, 2010 12:08 am

I recorded a macro in Calc, trying to insert a worksheet. I get an error message when I run the macro saying "Can't insert the table". I'm not trying to insert a table, just a new blank worksheet. I'm using OpenOffice 3.1.1 in Windows Vista Home.
Last edited by mikelongbeach on Sat Jan 23, 2010 9:44 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 7
mikelongbeach
 
Posts: 19
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Postby FJCC » Tue Jan 19, 2010 6:22 am

I recorded a macro to insert a sheet and it seems to work fine. Here is the code
Code: Select all   Expand viewCollapse view
sub AddSHeet
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Name"
args1(0).Value = "Sheet4"
args1(1).Name = "Index"
args1(1).Value = 1

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args1())


end sub

How does your code compare to that?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7385
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro trouble when trying to insert a worksheet

Postby mikelongbeach » Thu Jan 21, 2010 4:36 am

It was looking very similar but I had a bunch of lines that continued after yours stopped and they mentioned stuff about inserting tables. There was a bit more that I wanted to do with the macro and that must be where something went wrong. I'll keep plugging away. I did notice that sometimes I could insert one sheet but then if I wanted to run the macro again to insert another I got that same error message. Thanks for trying to help.
OpenOffice 4.1.6 on Windows 7
mikelongbeach
 
Posts: 19
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Postby mikelongbeach » Fri Jan 22, 2010 2:07 am

I have included the macro I wrote. What I'm trying to do is have the macro take me to the first sheet, copy it, insert a new sheet after the first sheet, then paste the first sheet onto the second.
REM ***** BASIC *****

Sub Main

End Sub



sub insertsheet3
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dim args1(0) as new com.sun.star.beans.PropertyValue
rem args1(0).Name = "Tables"
rem args1(0).Value = Array(0)

rem dispatcher.executeDispatch(document, ".uno:SelectTables", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Name"
args4(0).Value = "Sheet14"
args4(1).Name = "Index"
args4(1).Value = 2

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Sel"
args5(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub


sub insertsheet4
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sheet"
args1(0).Value = Array(0)

dispatcher.executeDispatch(document, ".uno:SelectSheet", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Name"
args4(0).Value = "Sheet15"
args4(1).Name = "Index"
args4(1).Value = 2

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub


sub insertsheet5
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Tables"
args3(0).Value = Array(0)

dispatcher.executeDispatch(document, ".uno:SelectTables", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Name"
args6(0).Value = "Sheet14"
args6(1).Name = "Index"
args6(1).Value = 2

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args6())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub
OpenOffice 4.1.6 on Windows 7
mikelongbeach
 
Posts: 19
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Postby FJCC » Fri Jan 22, 2010 8:35 am

I have never understood the dispatcher-based macros very well. It is really better to program using the API. A good place to start learning about it is the English Macro Document by Andrew Pitonyak. To give you a start, I wrote the following macro that inserts a new sheet after the first sheet and copies the contents of the first sheet to the new sheet
Code: Select all   Expand viewCollapse view
Sub CopySheet
oDoc = ThisComponent 'get the document that called the macro
oSheets = oDoc.Sheets  'get the collection of sheets

oSheet1 = oSheets.getByIndex(0)  'get the leftmost sheet
CopyRangeAddr = oSheet1.RangeAddress   'get the RangeAddress of the entire sheet
NewSheetName = "NewSheet"   'define a name for the new sheet
suffix = "_1" 'prepare a suffix in case a sheet with the above name already exists

While oSheets.hasByName(NewSheetName)  'Check if the sheet name is already in use
   NewSheetName = NewSheetName & suffix   'If it is in use add the suffix to the name   
Wend  'loop back to check the new name

oSheets.insertNewByName(NewSheetName,1)  'insert the new sheet in position 1 i.e. the 2nd sheet from the left
oSheet2 = oSheets.getByName(NewSheetName) 'get the new sheet
PasteCell = oSheet2.getCellRangeByName("A1")  'get the A1 cell of the new sheet
PasteCellAddr = PasteCell.CellAddress   'get the cell address of A1
oSheet2.CopyRange(PasteCellAddr, CopyRangeAddr)  'copy all of the 1st sheet into the 2nd, pasting in A1
End Sub
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7385
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro trouble when trying to insert a worksheet

Postby mikelongbeach » Sat Jan 23, 2010 9:43 pm

That's exactly what I wanted to do. Thanks for the help and thanks for the info on the other macro writing program.
OpenOffice 4.1.6 on Windows 7
mikelongbeach
 
Posts: 19
Joined: Mon Jan 18, 2010 11:52 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest