Skip to main content
A
avibabo
Navigator | Tier 3
September 28, 2022
Question

How to set a number variable to an excel cell?

  • September 28, 2022
  • 3 replies
  • 321 views

Hi, I need to retreive a decimal number from one excel file and need to transfert it to another. If I store it in a string variable. the separator of the decimal that I get in the 2nd file is dot while in the original file is a comma. I need the original separator (comma) because otherwise Excel doesn't want to perform calculations.

I tried to store the original value in a number variable, but then the number variable doesn't appear as an option in the Set excell cell action.

 

Thanks

    3 replies

    A
    avibaboAuthor
    Navigator | Tier 3
    September 29, 2022

    @Ashwin A.K​  thanks! but WOW ... seriously, we need to run a macro for this operation? I am quite surprised there isn't a simpler way

    Ashwin A.K
    Ashwin A.K
    Navigator | Tier 3
    September 29, 2022

    Hi @Avrora Keremidchieva​ ,

     

    After entering data into Excel and closing it, you can run this VBScript code to replace dots with commas:

    Function formatColumn(str_wbPath, str_sheetName,str_columnIndex)

    Set xlObj = CreateObject("Excel.Application")

    xlObj.visible = False

     

    On Error Resume Next

    'Filepath and Sheet name goes in here

    Set xlFile = xlObj.Workbooks.open(str_wbPath)

    Set shName = xlFile.Worksheets(str_sheetName) 

     

    'Select Column Index for changing format

    xlObj.ActiveSheet.Columns(str_columnIndex).Select.Replace ",", "."

     

    xlObj.ActiveSheet.Range("A1").Select

     

    xlFile.Save

    xlFile.Close

    xlObj.Quit

    If Err.Number <> 0 Then

    formatColumn = Err.Description

    Else

    formatColumn = "Success"

    End If

     

    Kind Regards,

    Ashwin A.K

    Blogs at TheCodingTheory - https://www.thecodingtheory.com
    Ashwin A.K
    Ashwin A.K
    Navigator | Tier 3
    September 29, 2022

    Actually you can replace text in Excel using the Excel Advanced: Replace Action, but if you want to target a specific column(s) then you have to use either VBA or VBScript.

    imageKind Regards,

    Ashwin A.K

    Blogs at TheCodingTheory - https://www.thecodingtheory.com
    Terms & ConditionsAccessibility statement