Hello friend,
I’m stuck with this problem, i’m testing a VB code that should copy data from sheet to another, but always the following exception occure:
the code is attached
VBA test.xaml (7.5 KB)
Hi,
I can reproduce your issue if input excel path is relative path.
So can you try to use absolute path for input excel file, if you use relative path?
Sample20211129-2.zip (10.3 KB)
And perhaps we should use Try-Catch-Finally or Using statement for accessing to each com object to make sure to dispose the object to prevent resource leak etc.
Regards,
1 Like
Hi @abdel,
I have ran your code after installing the above package. Its running fine for me.
Excel file is located in local folder or shared drive?
If its in shared drive, test it in local folder first.
Thanks!
Hi,
Your vb code is working in my studio. Anyway i have modified your code attaching the sample code below. please try and let us know if you still facing the issue. Thanks.
Sample VB code.txt (731 Bytes)
No shared drive
not working also, i d’ont know why this error occure:
RemoteException wrapping System.Reflection.TargetInvocationException: Une exception a été levée par la cible d’un appel. —> RemoteException wrapping System.Runtime.InteropServices.COMException: Index non valide. (Exception de HRESULT : 0x8002000B (DISP_E_BADINDEX))
à Microsoft.Office.Interop.Excel.Sheets.get__Default(Object Index)
à UiPathCodeRunner_d01cb2f7180e44338b388b7f4b88b230.Run(String in_Path, String in_WorkSheetSource, String in_WorkSheetDestination)
— End of inner exception stack trace —
à System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
à System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
à System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
à System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
à UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object args)
à UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs, IEnumerable1 imps, Object args)
à UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
à System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
à System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
à System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
In my opinion i feel the issue is not with the code and it is due to the excel which you are using not allow to copy paste the content i guess. i have tried at my end by changing the range and all it worked fine with out any errors.
there is a strange behaviour: when the code throw error, the file became locked, and read only. then to open it i must close Excel.exe!!!
Hi,
Got it. just for testing please use range(“A1:A4”).copy and try whether it is copy and pasting to another sheet.
Finally it works, but it copy the formula also, so when i read the file with workbook/read range it always gives:
Hi,
Glad to hear that you resolve the issue.
use below pasting type instead of paste all. it will help us to copy only values and exclude formulas. please try.
@kirankumar.mahanthi1 thanks it works.
Now i need to delete a sheet with the invoke VBA:
Dim excel As Microsoft.Office.Interop.Excel.ApplicationClass
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim Feuil1 As Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(in_Path)
Feuil1 = CType(wb.Sheets(in_WorkSheetSource), Microsoft.Office.Interop.Excel.Worksheet)
Feuil1.Delete()
wb.Save
wb.Close
excel.Quit
wb = Nothing
excel = Nothing
GC.Collect
but there is the following error:
@abdel you are welcome.
As per your message the code looks perfect for deleting sheet using vb. i don’t know why it is throwing error. How did you resolved your last error. thanks.
TryCast(wb.Sheets(sheetname), Microsoft.Office.Interop.Excel.Worksheet).Delete()
ne donne pas d’erreur mais elle ne supprime pas le sheet!
en plus sur le code je me suis aprecus que les dates nes ont pas copier dans le bon format:
“Date de
la facture”
41850
44319
41893
40021
41612
43643
43294
Hi,
For to maintain date format please use xlPasteValuesAndNumberFormats instead of xlPasteValues. please try and let me know. thanks.
i am able to delete the sheet with the below vb code. done small changes in the instantiating of sheet object.
VB Code:
Dim excel As Microsoft.Office.Interop.Excel.ApplicationClass
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim Feuil1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(“C:\Users\kirmah\Desktop\Demo.xlsx”)
Feuil1 = CType(wb.Sheets(“ABC”), Microsoft.Office.Interop.Excel.Worksheet)
Feuil1.Delete()
wb.Save
wb.Close
excel.Quit
wb = Nothing
excel = Nothing
GC.Collect
thank you @kirankumar.mahanthi1, but delete sheet always gives an error,
anywhere: is there an option tu put the format of date in “dd.MM.yyyy” format with VBA?
Hi,
Please try with the below VB code thanks. i have given the sample date format as per your message update the code accordingly. thanks.
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(Workbook1CopyFrom)
ws1 = CType(wb1.Sheets(Workbook1CopyFromSheet), Microsoft.Office.Interop.Excel.Worksheet)
ws1.Range(“A1”).NumberFormat = “dd.MM.yyyy”
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect
i tested this:
Dim excel As Microsoft.Office.Interop.Excel.ApplicationClass
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
Dim ws2 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(in_Path)
ws1 = CType(wb.Sheets(in_WorkSheetSource), Microsoft.Office.Interop.Excel.Worksheet)
ws2 = CType(wb.Sheets(in_WorkSheetDestination), Microsoft.Office.Interop.Excel.Worksheet)
ws1.Range(“A1:R20”).copy.ToString
ws2.Range(“A1”).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValuesAndNumberFormats)
ws2.Range(“H1”).NumberFormat =“dd.MM.yyyy”
wb.Save
wb.Close
excel.Quit
ws1 = Nothing
ws2 = Nothing
wb = Nothing
excel = Nothing
GC.Collect
but the format is for exemple 06/27/2019 00:00:00 => but i need 27.06.2019
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu