I have an excel where I have date in one or more columns in “dd-MMM-yyyy” format
I need to change it to “MM/dd/yyyy”
I know how to chnage it row by row, but thats taking a lot of timeis there a way to change the format of the entire column in one go just by providing a column name?
Please try this
implement this in Invoke code Activity
You can use the code below by compiling it in the “invoke code” activity. You can put a kill activity in before of it. (excel)
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(inputFilePath, [ReadOnly]:=False)
excel.Visible = False
ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
ws.Range("A:A").NumberFormat = "MM/dd/yyyy"
inputFilePath = your excel file path
Sheet1 = your excel sheet name
A:A = the column whose format you want to change.
MM/dd/yyyy = your format
This is what I tried and this does row by row which takes lot of time.
Hi @Gangadhar_Athili - May I know what “r” means here?
@muhammedyuzuak - sure, I will this code and let you know. Thank you.
I am facing an issue here. My xlsx file column has date in the following format “01-JUN-2022” which is not working with the code you gave me. But it’s definitely working if I have the date format as “1-Jan-22”. Is there anything I can do to work for this format too - “01-JAN-2020”?
How you are getting this data in the excel?? Are you getting it as Datatable and writing to excel??
If yes, you can keep a static excel template in which format of the columns predefined. It will help to auto change the format of the string as you are pre defining.
I have a csv file, in which the date format is listed in the columns as "1-Jun-22’. I am then using “read csv” activity to read the data and writing it to the excel, where the dates are changing to "01-JUN-2022’ in excel. This conversion from csv to excel is creating this date format problem.
But, the code that @muhammedyuzuak provided is not looking for any specific date format type upfront correct? Whatever date format it gets, it should still convert to ‘MM/dd/yyyy’ per my understanding ?
I am sharing an article with examples of using .NumberFormat. You can review. The critical event here is to simulate the custom formats given in excel. Vb support covers them.