Answered

Manipulating Excel

(0) Share

Report

Posted on by 90_percent

Scenario: I have a csv file that is produced every day, I need to manipulate the data, and save it as an excel file.
I have it so that it opens excel, opens the right file, and can save the file as an XLS format.

The issue is that before i can save it, I need to do two things,

1. Remove Duplicate rows based on the last column (BZ:BZ)

2. Need to change the next to last column (BY:BY) format from date to number (#####)

I have tried different aspects of PowerShell, commands,

Is this not possible or should i just use the PowerShell script to do it.

thanks

Guy

Categories:

Power Automate Desktop

All responses (4)

Answers (1)

Sort by

Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Record a macro from an xlsm workbook.

PAD opens your macro workbook and runs the macro.

the macro opens the csv, selects all data, removes duplicates on BZ, formats the date, and saves as xls.

good luck.
90_percent 69 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you! I hadn't thought of that!
I have been trying to use the recorder (first time using it), have been getting pretty interesting results.

I will switch to the macro and see if I can get it consistent.

Thanks Again. Sometimes having someone look at an issue from a different angle, Helps Immensely.
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

No pun intended on your name. Literally, 90% of my bots are:

Pull report(s) via command line
Open macro workbook
run macro

It’s very effective.
90_percent 69 on at

Like
a
(0)

Report
Copy link

Link copied!

That is how I am seeing some of these going, I am having to scrub my brain for VBA knowledge 😛

and the 90 Percent is from, i can usually get it 90% of the way there....but then need some help 😛

-Guy