How to pass config variable in vb code in uipath

Help
, ,
1

Hi,
I am trying to create a pivot table for which i used this code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim In_config As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of sheet1 ‘’’
Dim p_rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of pivottable shhet1’’
Try
excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’

wb = excel.Workbooks.Open(“Path”)‘Open the excel the file’
excel.Visible=True

ws=CType(wb.Sheets(“Sheet1”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
ws.Activate

rng=ws.Range(“A1”,“BD200”)‘Selects the given range of sheet 1’

p_ws=CType(wb.Sheets.Add,Microsoft.Office.Interop.Excel.Worksheet)‘create a empty and and name the sheet as pivot table’

p_rng= CType(p_ws.Cells(1,1),Microsoft.Office.Interop.Excel.Range) ‘Specify the first cell for the pivot table’

'Creation pivot Cache and pivot table ’
Dim oPivotCache As Microsoft.Office.Interop.Excel.PivotCache=Nothing
Dim oPivotTables As Microsoft.Office.Interop.Excel.PivotTables=Nothing
Dim oPivotTable As Microsoft.Office.Interop.Excel.PivotTable=Nothing

oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)
oPivotTables=DirectCast(p_ws.PivotTables(Type.Missing),Microsoft.Office.Interop.Excel.PivotTables)
oPivotTable=oPivotTables.Add(oPivotCache,p_rng,“Summary”,Type.Missing,Type.Missing)‘first paramenter is cache,range the table should start,table name’

‘Creation of pivot Fields’

Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField=Nothing

oPivotField=CType(oPivotTable.PivotFields(" Plnt"),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“Material”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“Vendor”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“Vendor Name”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

'oPivotField=CType(oPivotTable.PivotFields(“NpType”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
'oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

'oPivotField=CType(oPivotTable.PivotFields(“GSM”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
'oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

'oPivotField=CType(oPivotTable.PivotFields(“Country”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
'oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(" Quantity"),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

oPivotField=CType(oPivotTable.PivotFields(“Amount in LC(excl. Cus-tom and Octroi)”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
oPivotField.Name=“Sum_Of_revenue”

Catch es As Exception
System.Windows.MessageBox.Show(es.Message)
End Try

Now i want to pass path through config file so i created an argument named “path” and passed values as:in_config(“ExportFile”).ToString+“GR List”+month+“.xlsx”

but it is showing me error as attached.

errorVB
errorVB1414×735 60.8 KB

Please suggest

2

@nsharma - My suspicion is that the variable which is holding the path in the above code is not getting the value passed from the config file. Please check how you are passing the value from the config file to the code. Please try to hard code the path value in your code and check if the code works as expected. You will then be able to narrow down the problem.

3

No i passing the hard path still it is showing no file available with this name

4

Can you please suggest me any other way to create pivot table and dragging the fields without VB code?