[Solved] Macro trouble when trying to insert a worksheet

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
mikelongbeach
Posts: 22
Joined: Mon Jan 18, 2010 11:52 pm

[Solved] Macro trouble when trying to insert a worksheet

Post by mikelongbeach »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro trouble when trying to insert a worksheet

Post by FJCC »

I recorded a macro to insert a sheet and it seems to work fine. Here is the code

Code: Select all

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?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
mikelongbeach
Posts: 22
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Post by mikelongbeach »

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: 22
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Post by mikelongbeach »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro trouble when trying to insert a worksheet

Post by FJCC »

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

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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
mikelongbeach
Posts: 22
Joined: Mon Jan 18, 2010 11:52 pm

Re: Macro trouble when trying to insert a worksheet

Post by mikelongbeach »

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
Post Reply