I am using write cell workbook activity to write data in excel cells but the problem is that I am write cell workbook writes in text format. I had tried write cell excel activity but problem here I am facing is my excel file has several sheets and one master sheet in which operation performed is reflected to master sheet and Write cell excel activity is unable to change sheets.It is showing error that it unable to make connection between sheets. Is there any perfect solution for this?
Hi @Amit_Kumar_Charde ,
in order to have the numeric formatting, kindly use the
Excel application scope and use write range activity
Workbook write range activity writes numeric data as string
The best method which i can suggest is to use a excel template with the required formats. You can create that template manually and keep all the custom formats as required for the columns.
Then use write range to write data to it. It will then keep the format and you can get it as number.
I am using Write cell workbook but still, the data appending is in the form of text.
Write Range should write numbers to Excel as numbers but they may be converted to Text if the Excel cells are already formatted as Text, so check the data table column format is numeric, and check the Excel sheet formatting before writing to an existing workbook.
I am not using data table. I am extracting some values from pdf and simply appending to an excel file in the specific cell using write cell activity .
You can use VBA to format the cells in the required one
Use invoke vba activity inside excel scope with macro code for formatting, it will work.
Will you please elaborate to me in detail? I am extracting some values from pdf and appending them to excel files using write cell activity.
As you mentioned you are getting the data from some application and you need to write that to the excel sheet using write cell activity. So when you are getting the data from the application it will be in string format. So if you are writing that to excel even though if you convert that to an integer using CInt(“your string value”), it will again get converted to text while writing to excel.
So create an excel template manually for the automation process and format each column as per your requirement. for example, if the columnA need to be in integer, select the columnA->Home->format->format cells and select the format which you want.
Then use that template in your automation for writing your data.
You can keep it in some folder and copy the excel file to some specific folder as the first step of your automation.
Can you please tell me how I will customize the Template? Or can you share with me any examples?
The steps for customizing the column format are provided in the above post. You can create it manually once. Then use it in automation.
Hello Rahul, I tried every suggestion but still faced the same issue.