How to write a VLOOKUP formula using path&sheetname

Feedback Activities
, ,
1

Hello,

I have a code. I can use this formula without using sheetname for lookup.

Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.ApplicationClass

Try 
	Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(Path)

	Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet
	xlWorksheet = CType(xlWorkbook.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet)

	xlWorksheet.Activate()

  	Dim RowsCount As Int32 = xlWorksheet.UsedRange.Rows.Count

	xlWorksheet.Range(Range+RowsCount.ToString).Formula=ExcelFormula

	xlWorkbook.Save
	xlWorkbook.Close

Catch ex As Exception
	Throw ex
Finally
	xlApp.Quit
	
End Try

desktop
desktop790×323 12.8 KB

I can give the formula: “=IFERROR(VLOOKUP(AT2,‘[in_path]’!$A:$K,4,0),0)”

But How can i arrange formula giving a path with using sheetname?

3

@Betul_Dundar

Please try like this

“=IFERROR(VLOOKUP(AT2,[pathtoExcel]SheetName!$A:$K,4,0),0)”

Cheers

4

Hi @Anil_G , thank you for response. But it gives me an error:

21.10.6+Branch.support-v21.10.Sha.5cb5747f95eaecc8ba6a6256e82fd4d128d2c90e Source: Invoke code: VLOOKUP Message: Exception has been thrown by the target of an invocation. Exception Type: System.Reflection.TargetInvocationException RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC at UiPathCodeRunner_8fcefede64c443379efca47c0e334146.Run(String sheetName, String Path, String ExcelFormula, String Range) — End of inner exception stack trace — at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams) at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object args) at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs, IEnumerable1 imps, Object args) at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context) at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)— End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at UiPath.Executor.BaseRunner.EndExecute(IAsyncResult result) at UiPath.Executor.InProcessRunner.EndExecute(IAsyncResult result) at UiPath.Core.Activities.ExecutorInvokeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

5

@Betul_Dundar

Can you show how you are pssing…because ideally i added only the sheetname field…but no change…so this new error shpuld not be coming

Cheers

6

I tried this formulas which is giving same error:

sheetname: MAĞ

“=IFERROR(VLOOKUP(AT2,‘[C:\Users.…\Desktop\Filename.xlsx]MAĞ’!$A:$K,4,0),0)”

“=IFERROR(VLOOKUP(AT2,‘C:\Users.…\Desktop\Filename.xlsxMAĞ’!$A:$K,4,0),0)”

“=IFERROR(VLOOKUP(AT2,‘C:\Users.…\Desktop\Filename.xlsx]MAĞ’!$A:$K,4,0),0)”

7

@Betul_Dundar

please try this

"=IFERROR(VLOOKUP(AT2,'C:\Users.…\Desktop\[Filename.xlsx]MAĞ'!$A:$K,4,0),0)"

single quotes around path and sheetname and brackets around the filename and extension only

cheers

1 Like
8

Thank you @Anil_G . It worked !

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.