VBA Syntax

Help
1

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

2

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 ?

3

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.

4

can you share your workflow??

5

Capture
Capture714×588 29.5 KB

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