Automating Excels

Zoovash

Hello,

Does anybody know any programs for automatic excels creation ?
I find myself constantly modifying excels to meet a specific format. Also, the source format is always the same, only the data has changed. With this in mind I though I should find a way to automate my tasks.
I found I few blogs about people who recommended to use PowerShell for all things Windows. Do you know of any resource about this kind of thing ?

Thanks!

bradl3yC

I used KU Tools a lot in the past and it helped immensely with the task I was trying to complete:

Kutools - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

N2IT

Do you mean automatically creating workbooks? Is this using API's until another application to extract data or are you just creating workbooks on the fly?

Just wondering so I can better answer your question.

Zoovash

There's no API involved. Everyweek I have a few reports in a very long non-friendly format with a lot of gibberish. I have to clean that up and make it more readable. Since I'm completely new to Microsoft Excel (and Office in general ) I don't know of any other way to do that, but manually.

Qord

Personally, i would try powershell first, but I don't know exactly what you need to do to clean these files up so that might not be your best option. Are you removing data, rows, or columns? Making graphs from it? Simply reorganizing it?

JoJoCal19

I agree with Qoord. I would look to learning at least some basics of Powershell. First thing at my new job, my manager wants to automate our monthly deletion of leavers' document folders on the NAS. I was adamant about not learning Powershell due to wanting to learn Python since it's portable to any OS and more helpful in InfoSec so I went to relearn batch file scripting and man, the syntax is awful. I wanted to pull names out of a txt file to delete the folders with the respective names and the syntax was something like for /f "delims=" %%f in (leaversmydocs.txt) do rd /s /q "%%f" and I was trying to learn the delims and all that crap. Well with Powershell you can easily pipe output into another command and I looked up the syntax to do the same thing and its Get-Content leaversmydocs.txt | Remove-Item. I tested it out and worked immediately. Wow what a difference.

Command line scripting: /f "delims=" %%f in (leaversmydocs.txt) do rd /s /q "%%f"
Powershell: Get-Content leaversmydocs.txt | Remove-Item

So needless to say I'm learning Powershell, at least on a basic level, so I can automate some of the tasks we do in Windows. It's actually really powerful with
being able to hook into applications and such.

*My script is far from done as I want to delete all subfolders and files without prompting as well as allow us to take ownership of the folder first. This is just an example of what you can do and how easy it is with Powershell.

ccnxjr

I'm curious as to what format the source data is in.
If it's in CSV format then you can easily use Powershell, Python, bash, to wrangle data.

If the source data is already in Excel then you might be better off using Excel's built in macro recorder/editor.
Basically you can click a "record macro" button, perform a bunch of steps, stop the recorder and you have a macro which will perform those steps exactly!

You can then review the generated Visual Basic and edit it so that you can re-use it in other documents.

You also shouldn't be put-off by learning visual basic, it might be difficult and boring at first, however, that's the same with any programming language.
And once you become proficient in one, learner another language really does become soo much easier.

Zoovash

Thanks for your input, I appreciate it.
Source data is already in Excel but has a lot of useless columns. I need to create a few worksheets, each with a different number of columns and sort them in custom order. As well as modifying some cell's format (bold, alignment, size etc).
I guess it comes down to learning powershell, I might as well make good use of my holidays free time

dave330i

Zoovash wrote: »

Thanks for your input, I appreciate it.
Source data is already in Excel but has a lot of useless columns. I need to create a few worksheets, each with a different number of columns and sort them in custom order. As well as modifying some cell's format (bold, alignment, size etc).
I guess it comes down to learning powershell, I might as well make good use of my holidays free time

Use the Excel macro recorder/editor. No need to go into powershell.