How to get the Sheet name from Excel

excel

#1

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…!!!


How to read sheet name of an excel file
How to fetch sheet name of excel file in variable
Get Active Sheet Name
How to get data table of active sheet in excel
How to capture any sheet name in the excel?
How to Get names of Excel worksheets
#2

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


#3

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.


Get Sheet name in the case there is only 1 sheet
#4

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.


#5

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)


#6

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


#7

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?


#8

Could you update the Excel Activities, too?


#9

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.


#10

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)


How to fetch sheet name of excel file in variable
#11

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)


#12

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.


#13

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.).


#14

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.


#15

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.


UiPath.Credentials.Activities.GetCredential activity output is String type which is unsecure
How to get information from excel if I do not know name of sheet and how many columns
Excel file with multiple sheet of images. how can I iterate each sheet and fetch the text from each sheet and fill the fetched data in another sheet
#16

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


#17

@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.


#18

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


#19

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.


#20

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.