Save Time When Saving Excel Files

Last week, Seth Godin recommended hiring a geek to help you save an hour a day.

Well, you're a geek, so you don't have to hire one! Just pay attention as you run through your morning office routine, and answer this question:

  • What can you change about your Excel habits, to save an hour a day, or even a few minutes?

Then, make the change.

Doing Steps Manually

Every morning, I follow these steps:

  • download some website statistics to Excel,
  • crunch the numbers, and
  • save the raw data file.

Fortunately, I have a macro that does the first 2 steps. For some reason, I was doing the last step manually. Don't ask me why.

Excel is a bit slow when opening the Save As dialog box, so that final step was taking 30-60 seconds. Not a huge productivity drain, but why do something manually, if it can be easily automated?

Make It Automated

To get rid of that manual step, I added a few lines of code to the existing macro. The Excel VBA code saves a copy of the active workbook, into the Backup folder, and adds the previous day's date to the file name.

It only took me a couple of minutes to add the code, so the time invested was quickly repaid. Now I just have another 59 minutes to trim!

Sub SaveDailyData()
Dim wbData As Workbook
Dim strDir As String
Dim strName As String
Dim strExt As String
Set wbData = ActiveWorkbook

strDir = "C:Backups"
strName = "DailyData_" & Format(Date - 1, "yyyymmdd")
strExt = ".xls"

wbData.SaveCopyAs strDir & strName & strExt
wbData.Close SaveChanges:=False

End Sub



You may also like...

2 Responses

  1. Gregory says:

    There is a WordPress plugin by the name of CodeColorer that actually works to make VBA code look like, well VBA code.

  2. Thanks Gregory, I’m testing the CodeColorer on my Debra D’s Blog, to make sure that I understand how it works.

Leave a Reply

Your email address will not be published. Required fields are marked *