How to get the Sheet name from Excel

Eduardo Bernabe Sacahui Diaz
September 26, 2016 20:45 NONE
Hello !!! Friends!!! I have so far two assigns and a for each and I am trying to get the sheet name or names of an specific workbook. thanks for the help.

====first assigng ====

myinfo = new Workbookinfo()

  • this variable type is classexcelActivities.woorkbook

also tried the interop.excel.workbook -

====second assing ====

ListofSheets = myinfo.getSheetList(mypath.tostring)

  • this variable type is a list of strings -

====and my for each ====

for each item in listofsheets

log meesage = Item

  • this log message is an info log message -

But its throwing me this error:

System.Xaml.XamlObjectWriterException: Cannot create unknown type ‘{http://schemas.microsoft.com/netfx/2009/xaml/activities}Variable({clr-namespace:ClassExcelActivities;assembly=ClassExcelActivities}WorkbookInfo)’.
at System.Xaml.XamlObjectWriter.WriteStartObject(XamlType xamlType)
at System.Xaml.XamlWriter.WriteNode(XamlReader reader)
at System.Xaml.XamlServices.Transform(XamlReader xamlReader, XamlWriter xamlWriter, Boolean closeWriter)
at System.Activities.XamlIntegration.FuncFactory1.Evaluate() at System.Activities.DynamicActivity.OnInternalCacheMetadata(Boolean createEmptyBindings) at System.Activities.Activity.InternalCacheMetadata(Boolean createEmptyBindings, IList1& validationErrors)
at System.Activities.ActivityUtilities.ProcessActivity(ChildActivity childActivity, ChildActivity& nextActivity, Stack1& activitiesRemaining, ActivityCallStack parentChain, IList1& validationErrors, ProcessActivityTreeOptions options, ProcessActivityCallback callback)
at System.Activities.ActivityUtilities.ProcessActivityTreeCore(ChildActivity currentActivity, ActivityCallStack parentChain, ProcessActivityTreeOptions options, ProcessActivityCallback callback, IList1& validationErrors) at System.Activities.ActivityUtilities.CacheRootMetadata(Activity activity, LocationReferenceEnvironment hostEnvironment, ProcessActivityTreeOptions options, ProcessActivityCallback callback, IList1& validationErrors)
at System.Activities.WorkflowInspectionServices.d__3.MoveNext()
at System.Collections.Generic.List1.InsertRange(Int32 index, IEnumerable1 collection)
at UiPath.Executor.WorkflowTracking.GetAllActivities(Activity activity)
at UiPath.Executor.WorkflowTracking.SetTrackingProfile(Activity activity, LogLevel logLevel)
at UiPath.Executor.WorkflowTracking.SetProfile(Activity activity, LogLevel logLevel)
at UiPath.Executor.RobotRunner.InitWorkflowApplication(JobModel job, LogLevel logLevel)
at UiPath.Executor.RobotRunner.OnInvokeJob(String jobInfo)

Thanks…!!!

2 Likes

Corneliu Niculite September 26, 2016 21:33 Official comment
Hello,

My approach would be to use an Excel application scope to open the file. In the Output property, create a Workbook variable (UiPath.Excel.WorkbookApplication).

Then you have an assign SheetsNames = Workbook.GetSheets.

SheetsNames is System.Collections.Generic.IEnumerable<System.String>, aka a collection of Strings.

Then you can use these sheet names in Read or Write range activities.

Corneliu

5 Likes

Eduardo Bernabe Sacahui Diaz September 26, 2016 22:18
Thank you, thank you, for your help

I’ve tried to do what you suggested but now its saying “Object reference not set to an instance of an object.”

I created a var Mywb for the excel scope,

then assigned the variable IEnumerable as follows:

sheetname = Mywb.getsheets

after that, I used a writeline in a" for each" sheetname, because I dont want to use another excel and I dont know the sheetname on the file I’m using.

the problem is on output variable, but I dont know how to fix it, because normally when you use a variable and write a dot after shows you the possible activities but this is not the case.

1 Like

Corneliu Niculite September 28, 2016 13:28
I think it might be related to a small bug. It appears that if you put the Assign inside the Excel application scope, it does not see the updated value.

Therefore, take the Assign and all the other Activities outside of the Excel application scope.

2 Likes

Hi Corneliu, please is there any documentation with listed methods and properties for objects like Workbook in UiPath? I was not able to find anything like GetSheets method in UiPath (and it is not standard workbook’s method neither as far as i know)

1 Like

Hi jreszetar,
What is the Version of UIPATH you are using?
I am getting the option and version is 2016.2.6143.

2 Likes

Hi Uday, i am using 2016.1.6040. I have already noticed that the methods are listed once i enter .(dot) after the object in assign activity. Is there also some other place where can i find the members of specific object if its not listed in activities?

1 Like

Could you update the Excel Activities, too?

1 Like

Hi Badita, I have Excel activities package version 1.2.6123.30845. i think this is the newest one as i dont see any updates available but i am not able to find GetSheets between available activities.

1 Like

That is not an activity.
You need to use an output Workbook variable in Excel Application Scope and then use wb.GetSheets
See attached.

Sheets.xaml (5.6 KB)

6 Likes

Studio 2016.1.6137, Excel activities 1.2.6123.30845, this still throws an NRE.

Attached sample - working part and throwing an NRE. The bug that Corneliu was talking about earlier is still there.

ExcelTest.xaml (12.4 KB)

5 Likes

I have the nu package to use but don’t know if I can share it in here. this will allow you to use sheetlist function to get all the names.

From technical point of view, you can (or if you can’t add it as an attachment due to user restrictions or something similar, you could upload it somewhere and post a link, or post the code to pastebin etc.).

1 Like

The WoorkbookApplication variable is an OutArgument and right now it is created when the activity is completed. This is why you have to use it outside the Excel Application Scope.

We will fix this in a future release.

3 Likes

Dear all:

I have a solution for y’all.

First, install this package.

ExcelActivities.1.0.18.nupkg (9.5 KB)

Second: open this xaml and set up the path to an excel workbook with more than one sheet.
Readsheetname.xaml (7.7 KB)

Please, please comment your findings on this.

Hello,
Can anyone please help me to know how to move the excel sheet to another workbook.

@Uma,
What about Read Range of the sheet to a datatable variable, then Write Range with that variable to the other file?

I’m sure there are a few other coding methods too.

Thanks.

1 Like

No i want to move the sheet only which not having any data.and i am moving that sheet which is just template and i want to move this sheet into tge another workbook.
Thanks

Can you have the sheet in a file by itself? Then you can use the “Copy File” Activity to use the template with other datatables.

EDIT: Or, have a template with all the sheets you need, then like I said use Copy File and Write Range to the copied file to those sheets you want.

My template file only have the sheet(that having the format only)and i want to add this sheet into my anther workbook.supppse my one wb contains sheet 1and i wanto add this sheet 1into my another workbook.