Unanswered

'Save Excel' as csv is changing date time regional format

(0) Share

Report

Posted on by ShackletonsDog

I have a simple flow that downloads an xlsx file from a website, opens the downloaded xlsx and saves it as a csv.

The problem is that the 'Save Excel' process is converting dates from the supplied dd/MM/yyyy hh:mm (26/06/2023 08:38) to m/dd/yyyy h:mm (6/26/2023 8:38). There are no further settings when saving an excel document:

Here are the regional settings on my PC:

All I am doing with excel in my flow is:

Launch Excel > Save Excel > Close Excel

How can I get this flow to save as csv with my regional settings, the same way this works if I manually save as csv in excel myself?

Any help is appreciated, thanks.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Sort by

ShackletonsDog 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Any suggestions at all would be very welcome, thanks 🙂

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Try this. It should open the workbook same as you opening it because Excel is opening it instead of PAD:

Create a macro workbook (just have to save as .xlsm instead of .xlsx). Write this macro (thx GPT for creating it.) In the macro workbook, have A1 say: "FilePath:" and have B1 say: "FileName:". Then create the following macro:

Sub OpenFile()

' Declare variables
Dim filePath As String
Dim fileName As String

' Set variables
filePath = ThisWorkbook.ActiveSheet.Range("A2").Value ' Get file path from A2
fileName = ThisWorkbook.ActiveSheet.Range("B2").Value ' Get file name from B2

' Combine the file path and file name
Dim fullPath As String
fullPath = filePath & fileName

' Open the file
Workbooks.Open (fullPath)

End Sub

The bot will open the macro workbook

Have the bot write %Files[0].Directory% to A2

Have the bot write %Files[0].Name% to B2

Have the bot run the macro to open the file

This should open the file the same as you open it.

Have PAD attach to the newly opened file

Have PAD Save and Close as .csv

Best of luck!

Was this reply helpful? Yes No
ShackletonsDog 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Michael - I will give this a try and let you know how I get on.

Thanks again 🙂

Was this reply helpful? Yes No
JamesRPA 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, Please follow step
1. Lauanch Excel
2. Get current date and time
3. Convert datetime to text
4. close excel
Please see picture

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You can also do the following instead:
1. Launch Excel
2. Get first free column/row from Excel worksheet
3. Read data from Excel
4. Close Excel
5. Loop through the table you got as a result of Reading from Excel
6. Write to CSV

This way you will control the creation of the CSV file and will not need to rely on the way Excel handles the conversion.

Was this reply helpful? Yes No