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
@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
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
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.
Kind Regards,
Ashwin A.K
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.