I am working on a used case where I am importing over 15000 rows. I am using UsedRange Method to get the rows and columns. In a source Excel Worksheet, I have date column which includes dates in a format (e.g- 12/3/2018) but after I import the Date format changes to integer (e.g-43437). Before I am importing, I am deleting a previous worksheet in a destination workbook and inserting a worksheet with same name to get the accurate rows and column count (In each automation run). Thus, I cannot use preset worksheet before importing. I would like to know:
How can I get the date in a format as it is in the source worksheet (e.g- 12/3/2018) not (e.g-43437) in the destination work sheet too? I am using two different Excel connectors.
I need to do this via robotics. I know I can use “Format Cell” option in Excel to get the date format but this not what I want.
I need to achieve this in just one column, but I have several thousand rows and I don’t want to use loop as it slows down the process to set the cell in each rows.
After the data is moved from spreadsheet 1 into the datagrid then into spreadsheet 2 I get the same non formatted issue with the dates. My automation saves the spreadsheet, then closes it. I then trigger the macro in the 3rd spreadsheet which opens the 2nd and formats the first column A.
On a side note: In excel you can Add Developer to the Ribbon, Record yourself completing formatting or inserting columns/rows etc, and then (Alt+F11) to view the VBA editor and optimize the recorded macro, and Add it to your master macro file.
See attachments for How to:
Pega has been adding various method to manipulate excel: