Question

How to set a number variable to an excel cell?

  • 28 September 2022
  • 3 replies
  • 15 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

@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

Userlevel 3
Badge +7

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

Userlevel 3
Badge +7

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

Reply