Ask Your Question
0

Calc macro copy and paste woes [closed]

asked 2013-12-06 10:10:47 +0200

jheath gravatar image

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).Name = "AsLink"
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 ... (more)

edit retag flag offensive 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

1 Answer

Sort by » oldest newest most voted
0

answered 2013-12-09 07:58:52 +0200

jheath gravatar image

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")
oDestCell.Sheet = rngDest.RangeAddress.Sheet
oDestCell.Column = rngDest.RangeAddress.StartColumn
oDestCell.Row = rngDest.RangeAddress.StartRow
shtSource.copyRange(oDestCell, rngPrintArea.RangeAddress)
rngDest.CellBackColor = RGB(255,255,255)
rngDest.ClearContents(128)  ' get rid of any buttons copied

rngDest = shtReceipt.getCellRangeByName("ReceiptTwo")
oDestCell.Sheet = rngDest.RangeAddress.Sheet
oDestCell.Column = rngDest.RangeAddress.StartColumn
oDestCell.Row = rngDest.RangeAddress.StartRow
shtSource.copyRange(oDestCell, rngPrintArea.RangeAddress)
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.

edit flag offensive delete link more

Question Tools

2 followers

Stats

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

Seen: 3,193 times

Last updated: Dec 09 '13