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?
Accepted Solution
US Bank
US
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
Coforge BPM
US
Thanks Anthony!
Looks like this is what I was looking for. I will try this and let you know.
Dipesh
Coforge BPM
US
Anthony,
It worked like a charm!
Thanks for your help!
Dipesh
US Bank
US
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