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

[Paul Hartland]

Got the solution to this now for both open office and Excel

Paul

On Mon, 29 Jun 2020, 17:03 Paul Hartland, <paul.hartland at googlemail.com>
wrote:

> Thanks Dan, thats what I usually do when stuck with Excel, but doesnt seem
> to be the same with open office which I am stuck on, but thank you anyway :)
>
> Paul
>
> On Mon, 29 Jun 2020, 16:17 Daniel Waters, <df.waters at outlook.com> wrote:
>
>> 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
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>