# Calc macro copy and paste woes [closed]

I am banging my head on what should be a simple copy and paste job. I have information on an invoice sheet, and I just want to copy and paste it to a receipt page and print it out. My first attempt used copyRange, which seemed to work sometimes but not others. Specifically, it seemed to work better when I was stepping through the code, but if I ran it at full speed, it always seemed like something went wrong - the data wasn't copied, it printed the source sheet rather than the receipt, etc.

So then I tried shifting to using copy/paste based on the macro recorder. (Frankly I hate the fact that the macro recorder seems to be living in a whole different world. For example, where can I find the arguments to ".uno:InsertContents"? Nowhere...) So this is the code I currently have:

document   = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

shtSource = oDoc.Sheets.getByName(sTransType)
rngPrintArea = shtSource.getCellRangeByName(sTransType & "PrintArea")
oDoc.CurrentController.Select(rngPrintArea)
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

shtReceipt = oDoc.Sheets.getByName("Receipt")
shtReceipt.Unprotect("")
rngDest = shtReceipt.getCellRangeByName("ReceiptOne")
oDoc.CurrentController.Select(rngDest)
rngDest.ClearContents(1023)
dim args(5) as new com.sun.star.beans.PropertyValue
args(0).Name = "Flags"
args(0).Value = "SVDT"
args(1).Name = "FormulaCommand"
args(1).Value = 0
args(2).Name = "SkipEmptyCells"
args(2).Value = false
args(3).Name = "Transpose"
args(3).Value = false
args(4).Value = false
args(5).Name = "MoveMode"
args(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args())
rngDest.CellBackColor = RGB(255,255,255)
' Make sure the columns are the right width
For i = 0 to rngPrintArea.Columns.getCount-1
rngDest.Columns.getByIndex(i).Width = rngPrintArea.Columns.getByIndex(i).Width
Next i
shtReceipt.Protect("")
oDoc.Print(Array())
oDoc.CurrentController.Select(rngPrintArea)


I left out some of the details... Let me make a couple of comments on the code. I do a ClearContents(1023) - i.e. everything - so that I don't get that annoying pop-up that asks if you want to replace the current contents. (Shouldn't there be an option in .uno:InsertContents that says replace contents without asking?) I turn the cell background color to white so I don't waste ink printing a gray background. And the final loop makes sure that the column widths match the original - that information doesn't appear to ...

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-16 14:20:30.605750

Sort by » oldest newest most voted

Well, I went back to use the copyRange method, and somehow was able to get it to work this time - at least to resolve problem 1. Here is the code I have now:

Dim shtSource As Object
Dim rngPrintArea As Object
Dim shtReceipt As Object
Dim rngDest As Object
Dim oDestCell As New com.sun.star.table.CellAddress
Dim i As Integer

shtSource = oDoc.Sheets.getByName(sTransType)
rngPrintArea = shtSource.getCellRangeByName(sTransType & "PrintArea")
shtReceipt = oDoc.Sheets.getByName("Receipt")
shtReceipt.Unprotect("")

rngDest = shtReceipt.getCellRangeByName("ReceiptOne")
rngDest.CellBackColor = RGB(255,255,255)
rngDest.ClearContents(128)  ' get rid of any buttons copied

rngDest = shtReceipt.getCellRangeByName("ReceiptTwo")
rngDest.CellBackColor = RGB(255,255,255)
rngDest.ClearContents(128)  ' get rid of any buttons copied

' Make sure the columns are the right width
' Since receipts are stacked, changing one changes both
For i = 0 to rngPrintArea.Columns.getCount-1
rngDest.Columns.getByIndex(i).Width = rngPrintArea.Columns.getByIndex(i).Width
Next i
shtReceipt.Protect("")

Dim view As Object
view = oDoc.getCurrentController()
view.setActiveSheet(shtReceipt)
oDoc.Print(Array())
view.setActiveSheet(shtSource)


When I had tested copyRange before, I don't remember building up oDestCell like that, so maybe that was my problem. Anyway, this code seems to work for copying the range over to the receipt sheet. I still can't get the print to print the right sheet, but since that's a different problem, I think I will post it as a separate question, so it doesn't get lost.

more

## Stats

Asked: 2013-12-06 10:10:47 +0100

Seen: 3,436 times

Last updated: Dec 09 '13