VBA Syntax

I’m using VBA to code some macros in excel which work with my UiPath program but I’m having some issues. I’ve coded the following macro to copy a worksheet from one workbook and paste the values and formatting to a new worksheet in a different workbook.

When I run the code I get the following error: Exception from HRESULT 0x800A9C68, System.Runtime.InteropServices.COMException

Any help would be greatly appreciated!! Here’s my code:

Sub copySheetToMaster(sourcePath As String, destinationPath As String, masterShtName As String)
Dim srcBook, destBook As Workbook

'open workbooks 
set srcBook = Workbooks.Open(sourcePath)
set destBook = Workbooks.Open(destinationPath)

srcBook.Worksheets("AllocationSheet").UsedRange.Copy Before:=destBook.Worksheets(1)
With destBook.Worksheets(1).Range("A1")
    .PasteSpecial(xlPasteFormats)
    .PasteSpecial(xlPasteValues)
End With

destBook.Worksheets(1).Name = masterShtName

End Sub

hi

are you geting this error

  1. when you are running form “Execute Macro” from UIpath or
    2)rjunning from EXCel itself.

try to change excel version to 2003 ?

I’m running an invoke VBA activity within an excel application scope in uipath.

Some of the cells in my workbook have formulas and if I try to switch it to xls (2013) it says I will have significant loss of functionality for any cells with a fomula so I’m not sure that I can change it to 2013.

can you share your workflow??

I scratched out the file location but I’m not sure if this is going to help much.

I tried changing my code to simplify it and see if I could even just add a new worksheet to the workbook but it’s not working.

destBook.Worksheets.Add Before:=destBook.Worksheets(1).Name = masterShtNane

I tried this and got the 1001 error saying method ‘add’ of object ‘sheets’ failed, but it doesn’t tell me why