Category Archive 'Excel macros'

Creating Files With Unique Names With An Excel Macro

Excel macros

One of the things that I often need to do when working my spreadsheets is to save information at various times during the day. It’s important for me to keep logs of all of these files. So what can I do? I used to save files in a format such as:

TD20131015.csv

Where 2013-10-15 is the date. The problem came up when I started to need saving several files in the same day (but keeping each one). I looked at potential options.

-Saving the file manually (which is against the whole idea of using macros)
-Having a location in the file where a number is written (it could be changed from 1 to 2, to 3, etc) – the biggest problem is that it would create bugs. For example what happens if you close (without saving) and reopen the file?

In the end, I decided to simply save the file using the exact date AND time. It would look more like:

TD_20120628_094100.csv

How? It’s fairly easy. First, I created a cell in my file where I get today’s date:

Then, I created a name and finally added this simple macro:

It’s fairly simple but it works

Have A Slow Macro And Not Sure Why? Create A Log

Excel macros

One issue that I’ve seen quite a few times is seeing macros that end up being very slow to run. Those usually happen when a file evolves over several years and becomes more complex, bigger, etc than was anticipated at the start.

What I Do To Detect The Problem

The biggest challenge of course is trying to find what step is taking too much time so that I can then work on that specific part. How do I do it? I generally build macros, splitting them in different modules with one macro that groups them together. It makes it much easier to get a big picture of what the macros does, etc. Then, I get to something like this:

Sub ImportData()

Call importyest
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importyest”: r = r + 1

Call importinav
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importinav”: r = r + 1

Call importa1
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importa1”: r = r + 1

Call importa2
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importa2”: r = r + 1

End Sub

It’s fairly simple and my bigger macros are obviously 10-20 times bigger but this gives you an idea. Every time I call a new part of the macro, I write down the time into a tab that is called “log”. Then, I can easily look at the number of time between each step and find out where the delay is happening. I could then repeat that same step in that part in order to find out how I could speed it up.

Sending Delayed Emails In Outlook Using Excel

Excel macros

I’m a big believer in productivity and personally, trying to manage my emails as efficiently as possible is the #1 way to do this at work. I manage my emails in many different ways and always try to keep the number of emails in my inbox to a minimum. One thing that I like to do is sending postponed emails to myself and a few people in my team. Why? Because in general it’s the most efficient way to make sure no one forgets about something that needs to be done and it’s easy for anyone to reply.

I used to do this by going into Outlook and sending emails from there while going into the options and selecting a different “sending date”. It turned out to be very time consuming though and I tried to get this done in excel since I know there are many ways to write VBA macros that communicate together. Here is what I ended up getting:

The interface that I use to generate the emails is fairly simple:

Once I enter the details, I press send and the email is added to my outbok where it will remain until that specified time and date appears.

I could also write macros that would generate many emails if there was a simple order. For example if I needed to send the same email every day or every month, I could do it using this file as a starting point.

It has truly saved me many hours of time.

As always, you can download the spreadsheet here!

Using MSN Finance To Get Stock Prices In Excel

Excel macros

A few days ago I received an email telling me that the spreadsheet that got prices from the Yahoo finance website no longer worked. One clear downside of using web queries is that they sometimes require work if the publisher changes the way the website is presented or built. In this case, the “last price” is not as easy to access. He mentioned a MSN plugin but I thought I’d try to build a new one using MSN Finance and it proved fairly easy to do. First off, I did a manual web query to see if the last price of any stock would appear (it should) and it did. One problem is that it is not written “Last Price” so what would be the best way to “find it”? I decided to look at the prior line (which in all cases is “Print Report”

So I decided to do the query in column A:

=IF(C1=”Print Report”,1,0)

I added this in A2. This simply verifies what the prior line had. Then, I need the price only, not the variation so I decided to take whatever was before the “space”. I did the following:

=IF(A2=1,FIND(” “,C2))

For that specific line, this gives me the last price.

Then, I simply needed a query that would go through each line of the page, adjust the query, find the price and add it to the page. It’s very similar to what I had done in the previous file. Here is my code:

You can of course download my entire spreadsheet here.

Automatically Creating Client Invoices With Microsoft Excel

Excel macros

Today, I wanted to give an example of one very common problem that can be automated thanks to excel. Let’s imagine that you are a pool cleaner and you have a file with the name and emails of your clients (or addresses) in order to automatically create one file per client that can then be printed and sent. In theory, it could even be automatically emailed to each client just a few clicks.

I know for a fact that many small businesses have files such as this one and then create invoices one by one in what can be a very boring but also potentially filled with errors task. First, here is an idea of what my file looks like:

I simply created a sample invoice that you can see here:

It’s not very good looking but the goal here wasn’t exactly that either:) I have the formulas (all vlookups) in yellow just to give you an idea.

What I will do is simply have the macro go to my first sheet, go through each line and write the invoice number in cell C3. When that happens, all other details will be updated.

Then, I will simply have this file copied into a new excel, saved with a custom name. In my case, I will name it:

Pool_cleaning_invoice_1.xls

With one button press, all of these invoices will be created. The possibilities are endless as I could have the macro send out emails, print them, create envelope labels, etc. Here is the code that I used:

Very simple right? As you can imagine, the posssibilties are almost endless. You can download the spreadsheet here!