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?
Thanks Anthony!
Looks like this is what I was looking for. I will try this and let you know.
Dipesh
Anthony,
It worked like a charm!
Thanks for your help!
Dipesh
That's great. You can add other Macros to the same spreadsheet and use it like a master for all other automations.
Also in the Excel macro you can change the macro to %username% instead of the user ID if you are using the users path for RDA.
Sub formatColumn()
ChDir "C:\Users\%username%\Desktop"
Workbooks.Open Filename:="C:\Users\%username%\Desktop\OPenSpanTest2.xlsx"
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
End Sub
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:
http://help.openspan.com/80/Components/Excel_File_Connector_Properties,_Events,_and_Methods.htm
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