Copy & Paste to different Excel workbooks while retaining formats

[Nisa]

I’ve tried to manually copy & paste manually (ie. Ctrl C and Ctrl V), with Excel visible, as format & formulas need to be retained between different workbooks. This is currently working most of the time. The issue comes in when the it’s not working sometimes.

As it is performing for quite some time for quite a number of files (perhaps half an hour or so), it may experience some issues:
Issue 1) Exception from HRESULT: 0x800AC472
Issue 2) Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))

Are there any ways to solve this? It should be reliable for all the files.

[Palaniyappan (Forum Leader)]

Fine
if we are trying to copy the entire file then we can use COPY FILE activity
https://docs.uipath.com/activities/docs/copy-file

or are we trying to copy a selected range
Cheers @Nisa

[Nisa]

Hey @Palaniyappan!

Unfortunately, I’m trying to copy a selected range, copy file wouldn’t work in this case

[Palaniyappan (Forum Leader)]

no worries
we can use SELECT RANGE and GET SELECTED RANGE activity in this case

Select Range

Get Selected Range

and get the output with variable having that range stored which can be used in write range activity

the above mentioned link has some example as well
hope this would help you
kindly try this and let know for any queries or clarification
Cheers @Nisa

[Nisa]

Hey @Palaniyappan,

Get Selected Range outputs a string variable with the range as a string. How can that be used after getting the range?

And sorry, perhaps my question was not clear at the start. To simplify my question, is there a way to copy the data (with formula and formats) to another workbook without using hotkeys? I need to ensure stability for the program.

[chenderson (Cary Henderson)]

Good evening Nisa,

I’m not sure how familiar you are with VBA, this sounds like something you could pretty easily accomplish with a quick subroutine.

This solution may also work for you:

How to preserve Format while copying from an excel file

@sujoy_mukherjee Although if you need to preserve the format of the excel along with the colors and formatting it would be better to use the send hotkeys in excel to Copy (Ctrl + C) and Paste (Ctrl + V). Open excel 1 Select all the data with hotkeys use send hotkey activity with (Ctrl +C) open excel 2 use send hotkey activity with (Ctrl +V) Best regards Mazin Zunnoon

[Nisa]

Hi @chenderson!

With regards to the second option (ie. Ctrl C and Ctrl V), I’ve been doing that actually. However, as it needs to do for multiple (about 50) Excel files and Macro files (to be specific, .xls, .xlsx, and xlsm), there have been errors occasionally, as mentioned.

For the VBA option, I’m not very familiar with VBA. However, I tried this with invoke VBA, which works for .xls or .xlsx files:

Sub CopyPaste(ByVal AFileName As String, ByVal ATabName As String, ByVal ARange As String, ByVal BFileName As String, ByVal BTabName As String, ByVal BRange As String)

  Workbooks(AFileName).Worksheets(ATabName).Range(ARange).Copy
  Workbooks(BFileName).Worksheets(BTabName).Range(BRange).PasteSpecial Paste:=xlPasteAll

End Sub

But when i invoke VBA with an .xlsm file as a file name, it gives the following error:

Invoke VBA: Cannot run the macro 'CopyPaste'. The macro may not be available in this workbook or all macros may be disabled.

Are you familiar with VBA yourself? Any advice on how to get it to work on .xlsm without changing the current code for .xlsm? Thanks in advance.

[Nisa]

bump

any responses on this? I’m still trying to find a solution without using a ctrl c and ctrl v, and preferably not opening Excel visibly.

Main issue is copying for xlsm file, whereby it’s giving this error while I’m performing a sub:

Invoke VBA: Cannot run the macro 'CopyPaste'. The macro may not be available in this workbook or all macros may be disabled.

[Krutika_Kotkar]

Hi Nisa ,
did you find the solution to this ?
i’m also facing the same issue

[Nisa]

Hey @Krutika_Kotkar,

Sorry for the delayed response. Unfortunately, no, I was not able to solve it. I just used an Excel Application Scope within an Excel application scope, and still did a manual Ctrl C and Ctrl V in the end.

Just curious, do you have macros involved in your process? or just xls and xlsx in your files?

[Krutika_Kotkar]

@Nisa

in

no macros involved

[Nisa]

Hi @Krutika_Kotkar ,

Ah I see, in that case, are you able to try the VBA code listed above? It works well for .xls and .xlsx files.

[Manohar1 (Adicherla Manohar)]

@Krutika_Kotkar

please go through

How to copy a single column values in a sheet from one excel to other excel without changing in their format

Hi From a excel file I am taking the single column values and pasting in another excel but not able to retain the same format please help me to copy the data without losing the format in excel Thanks & Regards, A Manohar

Regards,
A Manohar

[Craig_Bannerman]

Hey Folks

I did a Type in and just used something like:
“[d(ctrl)]v[u(ctrl)]” + “[k(ctrl)]” + “M”

This Pastes then Opens the Paste Options and then M was my choice of paste option. But just check what options appear on Excel when you paste. For example select K for Keep Source Formatting.

It’s not quite perfect but it does the job if it’s a one off!