Copy & Paste to different Excel workbooks while retaining formats

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.

1 Like

Fine
if we are trying to copy the entire file then we can use COPY FILE activity

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

Hey @Palaniyappan!

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

1 Like

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


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

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.

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:

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.

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.

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

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?

no macros involved

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.

@Krutika_Kotkar

please go through

Regards,
A Manohar

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!