Read range file and sheet from variables

Dear all,

I am assigning variables for file and sheet name , but I get error when reading range .

I am attaching the commands and the error I get .

Print the sheet name and check whether it is there in excel file or not.

Validate the variables through writeline or log message before using it in workflow.

@the.christopoulos,

The sheet not available in the excel file.

Print the sheet name in the output and then check whether it is there in the excel sheet.

I did as you said and it seems crazy because the sheet does exist :thinking:

Hi
this looks like the sheet doesnt exists in the excel file we have
either if we are sure with the sheetname then use that variable sheet
like this in read range activity
sheet.ToString.Trim
if not sure with the sheetname then we need to validate that
–in this case if we are not sure on what is the sheetname then we can do this
–use a EXCEL APPLICATION SCOPE and pass the file path as input
–from this activity get the output with a property workbook, a variable named out_workbook
–now inside the scope use assign activity llike this
arr_sheetname = out_workbook.GetSheets().ToArray
where arr_sheetname is a array of string variable
–now use a FOR EACH activity within this excel application scope and pass the above variiable as input
–and change the type argument as string in the property panel of for each loop
–inside the loop use READ RANGE and mentionthe sheetname as item.ToString and get the output with a variable of type datatable

Cheers @the.christopoulos

@the.christopoulos
Then try with Sheet.Trim, just to eliminate unwanted whitespace characters if any

@Palaniyappan it worked your way, but still its strange why it was not working although the sheet name was the correct one.

@sarathi125 WIth trim it did not work

may i know what was mentioned in the assign activity
Cheers @the.christopoulos

@Palaniyappan

  1. I assigned workbooks=out_workbook.GetSheets().ToArray
  2. For each item in workbooks
  3. read range : item.tostring

Fine
this method is more reliable, because we might come up with some error while entering hard coded way
so do we have any other queries to be discussed
Cheerrs @the.christopoulos

@Palaniyappan I used the same exactly in a second .xaml process I needed it and I get error :

image

Fine
is out_workbook is obtained from the property workbook in EXCEL APPLICATION SCOPE activity

Cheers @the.christopoulos

@Palaniyappan The type for the variable is this one :thinking:

that iis fine
is that mentioned in the workbook property in the property panel of excel application scope
Cheers @the.christopoulos

Yes, my mistake. This was the issue.

Thank you @Palaniyappan ! It worked fine

1 Like