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 get copied/pasted, and the sheets I’m copying data from don’t all have the same column widths, but the printarea size and number of columns is the same.
If I run this code at speed, I have two problems: it doesn’t copy the receipts, and it prints the source page, not the receipt. If I set a breakpoint and step through the code, it selects the rngPrintArea but it does not copy it (no marching ants around the selection). So obviously pasting is not going to give the desired result. If I perform the copy manually at that point, the code continues on and does what it is supposed to INCLUDING printing the receipt and not the source page.
Here’s a clue (I think) to problem 1. When I go to perform the copy manually, if I go to the Edit menu, Copy is greyed out. If I click somewhere on the sheet, then reselect the printArea, then I can select Copy from the Edit menu, the ants start marching, and if I continue on with the macro, it will paste properly. So I think that because I started the macro by clicking on a button, that the focus is somehow still on the button and that even though I have selected the print range in the macro (the row and column headers are highlighted in blue), the Copy can’t happen. It seems to me that using the programmatic copyRange might be better, and not so dependent on the vagaries of the user interface. Should I go back to that? But again, it seems like I was having a similar problem with copyRange. One other comment: The source sheet is protected, but I wouldn’t think that copying from a protected sheet would be a problem, just modifying it. But just in case, I did try unprotecting the source sheet before copying, and got the same result.
For problem 2, if I single step through the code, I see that when I Select(rngDest) to select the receipt it changes the view of the sheet to the receipt sheet. Then when it executes the print, it prints the receipt. But if run without a breakpoint, or I break the code near the beginning and start full-speed running again while it is still showing the source sheet, then it will only print the source sheet. It’s like it “knows” that the program hasn’t had the time to display the receipt sheet (even though there was a selection on that sheet), so it just prints the sheet that is currently displayed. Surely there must be a way to print a sheet that is not the current sheet - some parameter or something to the Print method?
Thanks for any light you can shed on these problems.