How to write Excel data correctly without formulas

[aggneha9821]

Hi , I want to write a data in Excel suppose my value is 111000001074 but when bot write this value in Excel it becomes like this 1.11E+11 .is there any method to fix this 

[Ahammad_Riyaz]

I expect that when you drag the column, you'll be able to view the values. To achieve this, you can create a template file where you can input your content.

The complete value might not appear when copied from a website, but if the data is in Excel, you can retrieve the entire value.

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

Regards,
Riyaz

[aggneha9821]

@Ahammad_Riyaz when I drag the column also m not able to view the digit .... actually it's a report Excel which I am generating at the end .....but these type of data are not key in correctly it's coming as formula only ......it's a buisness need that this should be write correctly in correct format 

[Ahammad_Riyaz]

Okay from where you are getting the data.

If possible share the snip as well.

[aggneha9821]

@Ahammad_Riyaz from queue this data is coming .plz find attached document 

[NathanAlvares24]

Hi @aggneha9821 !

Try out an excel formula to convert that value to text.

Check this for reference:

I used this formula:

=TEXT(%NewVar%,"0")

where my variable holds the value of that number.

Output:

As seen above, you can see the difference. The first one i.e. in cell A1 is coming like that in Scientific format or General. But at cell C1, it is showing in correct format. But only thing here is that in the formula of cell C1, it won't show as simple text but a formula.

This is just for reference. Test it with the data from your datatable and see if this works out for you.

I hope this helps.

[VishnuReddy1997]

Hi @aggneha9821 ,

There are few methods to achieve this.

Method 1: You Can change or make in Excel Template itself the Column into Number Format as shown in below image.

Method 2:

After writing it to the excel you can change the A column format to Number format.You Run Vbscript action.

Use below Vbscript.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\your\file.xlsx")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange.Columns(1)

objRange.NumberFormat = "0"

objWorkbook.Save
objWorkbook.Close
objExcel.Quit

Methods 3:

You can pass send keys using Sends key action.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[aggneha9821]

@NathanAlvares24 not working for me as well getting same

[aggneha9821]

• @VishnuReddy1997 method 3 is not working as it's giving 111,000,001.00 like this type of format
• For method one so I need to manually change that number format bcse if so I cannot do that as bot is generating this Excel 

[VishnuReddy1997]

Hi @aggneha9821 ,

Can you try the Method 2 it will work.In mean while i will check with method 3.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[VishnuReddy1997]

Hi @aggneha9821 ,

Please try this olution for Method 2.

Code:

FUNCTION 'Number Format' GLOBAL
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\BOM_SPDP_Akshatha_BOM_Output_Template_04_July_2024_16_07_44.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
    UIAutomation.FocusWindow.FocusByInstanceOrHandle WindowInstance: ExcelInstance
    Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''A''' EndRow: FirstFreeRow - 1
    MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Control}({Shift}({1}))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.PressReleaseKey.PressKey Control: False Alt: True Shift: False Win: False
    MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: $'''H''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: 9 DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: $'''H''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: 9 DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.PressReleaseKey.ReleaseKey Control: False Alt: True Shift: False Win: False
END FUNCTION

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[aggneha9821]

@VishnuReddy1997 thanks for ur help but still not getting correct.pls see attached document 

[VishnuReddy1997]

Hi @aggneha9821 ,

In the  screenshot provided it removed decimal parts na and regarding , are there when your extracting itself i think.

Did you tried method 2?

 Regards,

Vishnu Reddy

[aggneha9821]

This data m getting from queue and there is no comma in between the digits ...

For method 2 - I can not use scripting for this particular project as it's not allowed 

[VishnuReddy1997 · Accepted solution]

Hi @aggneha9821 ,

Then Method 1 is the Only choice. in the template itself make that column as number format and save it.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy