VB6 Copy Range Of Cells With Formatting From One Sheet TO Another

[HartlandP]

To All,

I am trying to copy one range of cells from a selected sheet to another blank sheet and the code below works, so happy days, however it only copies the actual data and not the formatting, anyone ever played with something like this and managed to copy the formatting as well as the data ?

Set objXLOORangeSource = sXLSheetSource.getCellRangeByPosition(lngSourceColStart, lngSourceRowStart, lngSourceColFinsh, lngSourceRowFinish)
' Set target range of cells
Set objXLOORangeTarget = objXLOOSheetTarget.getCellRangeByPosition(lngSourceColStart, 0, lngSourceColFinsh, lngTargetRowFinish)
objXLOORangeTarget.setDataArray (objXLOORangeSource.getDataArray())

Thanks in advance for any help.

Paul

[JohnSUN]

Yes, “set data array” (.setDataArray ()) will only set data, not formats. Please tell us more about your task: how many ranges you are trying to copy (one or more), what needs to be done next (use data for further calculations or prepare them for printing and forget)

[JohnSUN]

Wait! You do not need to copy anything with formatting, you only need to send part of the sheet by mail? What about setting the Print Range to the A40:AE61 range and export it to PDF? (You needlessly posted your COMMENTS in the form of ANSWER - this will confuse those who will seek an answer to a similar problem)

[Lupp]

The OQ (@HartlandP ) should have posted his additional explanations here or -by editing- at the end of his original question.) I now give a copy to ease the ongoung discussion:
Quoting @HartlandP:
“Basically the client sends has a sheet with prices etc, at the bottom of the prices there is a layout of what they will email to their branches, usually at row 40 column 0 to row 61 column 30. So what I am trying to do is grab that range from that sheet and oaste it into another, then save that sheet and automatically email it as attachment, so just copying one range with formatting onto another sheet”

[Lupp]

This is not yet clear enough.
Do you also want to copy coulumn widths and row heights?
Shall the copy (by range address) be in the original range or elsewhere…

[HartlandP]

Not really that fussed about columns widths and row height but knowing the client that would be helpful, I want to copy row40,column0 to row61,column30 from the price sheet and paste it on the new sheet at row0, column0

[Lupp]

In addition to what I demonstrated, you simply need to delete the rows above the copied range. Done.

  sheet1.removeRange(rgAbove.RangeAddress, 3) 
  REM 3 = CellDeleteMode ROW: Complete rows below move upwards.

[HartlandP]

Thanks all about to start work so will give it a try, failing that I will just settle for copying the sheet and deleting rows thank you

[Lupp]

(This is nothing to do with VB6, of course.)
Assuming you also want to reproduce sheet properties of the source sheet, I would suggest you copy the complete source sheet to a newly created Calc document, and then delete unwanted parts of the contents and the formatting.

=== Editing 2 h later ===
See the attached demo:
ask252803sheetTonewDoc_0.ods

=== Editing again ===

Sub useTransferableExample()
doc0 = ThisComponent
sheet = doc0.Sheets(0)
rg = sheet.getCellRangeByName("A41:AE62")
doc0.CurrentController.select(rg)
doc1 = starDesktop.loadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
sheetN = doc1.Sheets(0)
doc1.CurrentController.select(sheetN.getCellByPosition(0, 0))
t = doc0.CurrentController.getTransferable()
doc1.CurrentController.insertTransferable(t)
End Sub

[HartlandP]

Already done that with vb6 bit messy though really just thought i could just open two sheets copy the range and formatting and paste it, thank you though

[Lupp]

Calc doesn’t speak vb6. This isn’t mainly a question of Baisc, but of the API.
It wouild be helpful probably to use terms more thoroughly. This is about Calc, and there may be some differences in terminology as compared with Excel.
What’s bad with the dispatcher-based solution?
Copying by generic API means for sheet ranges is controlled by address structures which don’t distinguish documents and must be used by a spreadsheet. Conclusion: Only works inside one document. Copies from on document to a different one is a different thing.
Of course you can prepare a new sheet completely inside the source doument, and move it later to a new document. The relevant means is the .copyRange method then. See LibreOffice: XCellRangeMovement Interface Reference . It must be called by one of the spreadsheets in the current document.

[Lupp]

Quoting @HartlandP: “Already done that with vb6 bit messy though…”
How? Why not simply forget VB?
Quoting @HartlandP:"…really just thought i could just open two sheets {spreadsheet documents?} copy the range and formatting and paste it,"
Using XTransferableSupplier you can do this, too. However, there’s no specific method based on ranges. You need to use the Controllers of both documents, and to rely on selections. This is basically like using a clipboard. And like any Copy/Paste for spreadsheets it will only paste column properties if complete columns were selected in advance of the Copy step. For rows respectively.
See the amendment to my answer.