Question
Excel Import Issue on Dates.
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.
Is there any simple way to do this?
I did something a little different. I created a 3rd spreadsheet and added a macro named format Column.
I expanded on the Excel Connector automation example in the following link from Pega.
https://community.pega.com/knowledgebase/documents/using-microsoft-office-connectors
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.
Sub formatColumn()
ChDir "C:\Users\amcgrif1\Desktop"
Workbooks.Open Filename:="C:\Users\amcgrif1\Desktop\OpenSpanTest2.xlsx"
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
End Sub