Read excel datetime cell as string while preserving formatting

datatable
excel
studio
string

#1

Hello

We are receiving some excel files where the formatting of datetimes varies from file to file and this formatting needs to preserved.

But when trying to extract this data via either a ReadCell or a ReadRange activity the returned value is just the raw datetime string (in MM/dd/yyyy hh:mm:ss format)
This also happens when trying to access the cell value directly via workbook.ReadCell(Of String)(Range as string)

Is there any way to either
a: Force uipath or a vb.net method to access the displayed value rather than the stored value in excel
or
b: Read out the applied formatting so i can convert the datetime value myself to the same format in a tostring().

The excel file is from an external source and cannot be modified.

Thanks in advance


#2

For anybody else who runs into this issue, i ended up solving this issue by using invoke code and running the following code:

Dim xlApp As Microsoft.Office.Interop.Excel._Application = Nothing
Dim xlWorkBooks As Microsoft.Office.Interop.Excel.Workbooks=Nothing
Dim xlWorkBook As  Microsoft.Office.Interop.Excel.Workbook=Nothing
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet=Nothing
Dim xlWorkSheets As Microsoft.Office.Interop.Excel.Sheets=Nothing

xlApp=CType(Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
xlApp.DisplayAlerts=False
xlWorkBooks=xlApp.Workbooks
xlWorkBook=xlWorkBooks(1)
xlApp.Visible = True
xlWorkSheets=xlWorkBook.Sheets
 
xlWorkSheet= CType(xlWorkSheets(1),Microsoft.Office.Interop.Excel.Worksheet)
result = xlWorkSheet.Range(cellAddress).Text.ToString

With an outargument callen result and an in argument called cellAddress


#3

hello, i had tried Hello,

I want to format excel column as following:
29487,15 => 29 487.15
89,64 => 89,64
73607,58 => 73 607.58

i used the following VBA expressing that gives thousands separator:
[xlWorkSheet.Range(“H:L”).NumberFormat = “(* #,##0.00);(*
(#,##0.00);
(* “”-”"??);(@_)" ]

but need also to replace "," by "."