topic Re: Excel - Looking for method to execute Text to Columns in Product Forum

Excel - Looking for method to execute Text to Columns

john.hammond — Tue, 04 Jul 2023 12:34:34 GMT

Having a bit of a mare with writing dates into an Excel column, which I'll briefly expand upon before mentioning what it is I'm after:

I'm sourcing data from a number of spreadsheets. This is collated by a Power Query (where the dates all appear in the format dd/mm/yyyy). These are read into a Blue Prism collection (where the dates all STILL appear in the format dd/mm/yyyy). Writing this collection to Excel randomly throws out the odd mm/dd/yyyy format (when the date is possible to do so, ie. 01/11/2023 will come out as 11th January, but 28/08/2023 will remain 28th August). Have searched high and low for a way around this (all locale settings appear to be correct), and the best I can currently do is pre-format the column in Excel to be Text and then write into it. This preserves the correct date format. Unfortunately, off the back of these date columns, I have a number of formulae that need to check the date(s) entered, which now do not function correctly. The only workaround I currently have for this is to perform the write in two steps - one up to the date column (the date column I write to is hidden, and another column picks it up in the correct format) and then a second write action for the rest of the collection in the proceeding columns. This is not ideal, however, and I'm looking for another solution.

What I would like to do is emulate the functionality within Excel that allows text to be parsed as dates (after the point of having written the data). Manually, I would do this by clicking Text-to-Columns, Delimited, Next, Next, Date (DMY). Would somebody be able to help me with the code stage required to do this, if it is even possible?

Many Thanks

Re: Excel - Looking for method to execute Text to Columns

Vaibhav9878 — Tue, 04 Jul 2023 13:53:56 GMT

Hi John,

As per my understanding of your query, you are trying to change the format of a column which has dates in it (automatically saving as MM/dd/YYYY) and you are trying to change the format to "dd/MM/YYYY". If my understanding is correct, this can be resolved with help of macro.

----Code---

Sub test()

Dim wsThis As Worksheet

Dim lastrow As Integer

Set wsThis = ThisWorkbook.Worksheets("Sheet1")

lastrow = wsThis.Cells(wsThis.Rows.Count, "A").End(xlUp).Row

wsThis.Range("A1:A" & lastrow).NumberFormat = "dd/MM/yyyy"

End Sub

--------End of Code-----

I have tried it in macro and worked well. You could also try it.

correct me if I am wrong.

Regards,

Vaibhav.

Re: Excel - Looking for method to execute Text to Columns

john.hammond — Tue, 04 Jul 2023 14:04:08 GMT

Hi Vaibhav, thank you for your response.

It's funny that you mention performing a Macro, as I got that idea also as soon as I posted this. You're quite right, it does the job well, although I'd be interested to hear if there is a code stage-based solution, as I know that some recipients may not be permitted to open files with macros embedded. However, I think this is the best approach for the time being! Many Thanks.

Re: Excel - Looking for method to execute Text to Columns

Vaibhav9878 — Tue, 04 Jul 2023 14:12:08 GMT

Hi John,

Yes, that is a nice point as everyone wont be having permission to open macro. But its a script and we are invoking it from our end, So, I think there wont be any issue with others opening the macro as they can't see this. We can use this macro as supporting file.

Thank you

Re: Excel - Looking for method to execute Text to Columns

MichealCharron — Tue, 04 Jul 2023 17:06:19 GMT

@john.hammond & @Vaibhav9878

If you are just looking to set the date format of a column, you can use the "Format Cell" action in the "MS Excel VBO" VBO. "Format Cell" is somewhat of a misnomer for that action in that you can also format ranges. Take for example the following stage:

It sets the date format of the entire "F" column to "dd/MM/yyyy". Also, because it sets the format at the column level, as opposed to the individual cell level, it results in smaller file sizes and new rows added are automatically formatted accordingly.

Re: Excel - Looking for method to execute Text to Columns

Vaibhav9878 — Wed, 05 Jul 2023 13:35:17 GMT

Hi @MichealCharron

Yes your option is actually good as it is inside the Blue Prism. Thanks for the answer.