How can I convert an Object to a String?

Help
1

I have a Read Cell activity where the OutPut is an Object variable. I need to know the contents of the Object variable, which should be an employee number.

If I try to output AnObject.ToString I get “System.Object[,]” instead of the actual contents of the variable.

If I try to output Convert.ToString(AnObject) I get “System.Object[,]” instead of the actual contents of the variable.

If I try to output ctype(AnObject,string) I get an error saying “Conversion from type ‘Object(,)’ to type ‘String’ is not valid.”

If I try to output directcast(AnObject,string) I get an error saying"Unable to cast object of type ‘System.Object[,]’ to type ‘System.String’."

How can I get the contents of this variable?

2

Change your variable ‘AnObject’ to type string in the variable pane. Then in the read cell activity ‘Output’ property, just put in your ‘AnObject’ variable - no conversion should be necessary

1 Like
3

If I do that I get an error saying “StringConverter cannot convert from System.Object[,].” The Read Cell activity seems convinced that the data in cell is an object.

4

I’m a little confused by the [,] portion. However, you can do it using 2 separate variables than. Keep the type as whatever the activity wants it to be. Then, create a separate string object i’ll call str1. In an assign activity you should be able to assign str1 = AnObject.ToString.

If that isn’t working then can you please send a screenshot or upload your workflow?

5

@MattFella - Another quick thing to check. After you read the cell and have the object saved as ‘AnObject’ variable - can you use a write line to check AnObject.GetType()

6

Getting the same thing over again. If I assign to a string variable AnObject.ToString I get System.Object[,] If I do a Write Line with AnObject.GetType().ToString I get System.Object[,]

7

Can you please upload the workflow? If possible, upload the excel file as well. I tested on my own and it worked fine

8

Here is an example that reads a cell that contains text as well as a cell that contains a number. The read cell containing text is output directly to a string variable. The one with the number is output to an object then used the .ToString() method to display as a string.

What is the data you’re trying to get in excel? I had assumed it was a string. Are you referencing something more complicated such as a pivot table or an excel object like a dropdown list?

ExcelReadCell.xaml (7.5 KB) TestExcelReadCell.xlsx (8.6 KB)

9

Please see attached code and example excel form attached. Your code and example worked fine for me.Change of Personal Details Form Test.xls (110 KB) Get The Oracle Number.xaml (25.3 KB)

10

Did you alter your code at all? It worked for me when I ran it. I downloaded both files, updated the excel location so it searches for the excel file on my own computer, ran it and got the result of “8268889993”

11

Also, since you’re already running macros on that sheet, you could use a macro to give you the value needed if you are unable to debug this. Simply create a function similar to the below code, use ‘Execute Macro’ activity, supply “GetOracleNumber” as the name of the macro, and save the macro output to an object variable. If you can’t store the macro on the excel template/workbook itself, you can create a VBScript (.vbs) file as well. You just have to modify the below code a bit so it doesn’t use things like ThisWorkbook

Function GetOracleNumber() As String

   GetOracleNumber = ThisWorkbook.Sheets("New Personal Details Form").Range("E12").Value

End Function
12

That’s hilarious! After you said that, I’ve actually run the same code and Excel file on another environment with a more recent version of Studio installed and it worked absolutely fine. I guess it was just a bug in the older version? Thanks for your help anyway, and thanks for the idea of bodging it using macros, I hadn’t thought of that.