[Solved] Macro trouble when trying to insert a worksheet
-
- Posts: 22
- Joined: Mon Jan 18, 2010 11:52 pm
[Solved] Macro trouble when trying to insert a worksheet
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
Re: Macro trouble when trying to insert a worksheet
I recorded a macro to insert a sheet and it seems to work fine. Here is the code
How does your code compare to that?
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
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 22
- Joined: Mon Jan 18, 2010 11:52 pm
Re: Macro trouble when trying to insert a worksheet
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
-
- Posts: 22
- Joined: Mon Jan 18, 2010 11:52 pm
Re: Macro trouble when trying to insert a worksheet
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
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
Re: Macro trouble when trying to insert a worksheet
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 22
- Joined: Mon Jan 18, 2010 11:52 pm
Re: Macro trouble when trying to insert a worksheet
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