[AccessD] VB6/VBA & OpenOffice - Copy A Range Of Cells From One Sheet To Another Sheet

[Daniel Waters]

Hi Paul,

Something to try --

In Excel, you can record a macro, then view the code recorded.  That macro code can then be manipulated in the VBA window to turn it into what you actually want.  I used this technique a lot in the past and could not have manipulated Excel from Access if I hadn't.

Start a Macro.

Type anything into a cell.
Format that cell in some way.
Right-click that cell and select Copy.
Right-click a different cell and you'll see some Paste Options.
Hover over Paste Special and a window appears.
Then hover over the icon in the top row on the right where the pop-up says, 'Keep Source Formatting.'
Left-click on that icon (it looks like a clipboard with a sheet of paper with lines of text).

End the Macro

Now you can open and view the Macro in edit mode where you can see the code step by step.  Hopefully, some of that code specifically shows how to keep the source formatting.

Unfortunately, I can't get into edit mode for Macros on my PC.  Excel tries to open Visual Studio as the code editing software (and then stalls out) instead of the VBA editor in Excel.  Did a google search on this issue and couldn't find anyone else who has the same problem!

Good Luck!
Dan


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland via AccessD
Sent: Monday, June 29, 2020 8:48 AM
To: Access List
Cc: Paul Hartland
Subject: [AccessD] VB6/VBA & OpenOffice - Copy A Range Of Cells From One Sheet To Another Sheet

To All,

I would be pulling my hair out with this one by now if I hadn't got the
clippers on it the weekend, 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, I have to do it for MS Excel as well, but I think I have
code for that somewhere so can't see that being a problem, but this with
OpenOffice is driving me insane at the moment.

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 Hartland
paul.hartland at googlemail.com

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com