Blog

VBA Scripting in MS Office Made My Day

VBA Scripting in MS Office Made My Day

I learned a new trick this morning that made my day. It not only managed to save me quite a bit of time, but it also opened up a brave new world full of possibilities for the future!

Ok, so it's not really a trick. And chances are many of you frequently use it already. But it was new to me!

For a project i was working on this morning, I was using a rather large Microsoft Visio document and needed to export each page individually as a GIF files. To my dismay, Visio has no such option - at least not that I could find. But a quick search on Google led me to discover that it can easily be done by writing a short macro using Microsoft's Visual Basic Editor.

I've never written a macro for Office before, so I had no idea how to get started. But Visual Basic?? Now we're speaking my language. A little more Googling led me to exactly what I was looking for: How to Export All Pages in a Visio Document. This code if pretty good and did what I needed. If you ever need to export multiple pages at once, give it a shot!

For those of you, who like me, have never before created your own macro, this is a great one to try it out on. Give it a whirl, you can do it in about 3 minutes in 5 easy steps:

(Using Microsoft Visio 2010)

Step 1: In your Visio document, click on the Developer Tab on the ribbon. (if you don't see a Developer Tab, start here). In the upper left-hand corner, click on the Visual Basic icon (this is where I got excited).

Step 2: When Microsoft Visual Basic for Applications opens, on the Insert menu, click Module. It will probably create a module named Module1, but if you go under View and select Properties Window, and then select the newly created module, you can rename it to something better - such as ExportAllPages.

Step 3: Double click on your new module in the Project Explorer Window. Now you can start adding code! For starters, here's a brief snippet that will export all pages as etiher GIF or JPG, and name them according to their page name. (This code is derived from Visio Guy's example, but watered down a little).

Public Sub AsGIF()

Dim FileExtension As String
FileExtension = ".gif"

'// Init folder, doc and counter:
SaveDirectory = ThisDocument.Path
Set doc = Visio.ActiveDocument

'// Loop through pages in the Visio Document:
For Each pg In doc.Pages
    
  '// Setup the filename:
  FileName = pg.Name
    
  '// Add the extension:
  FileName = FileName & FileExtension
  
  '// Save it:
  Call pg.Export(SaveDirectory & FileName)
    
Next

End Sub

Public Sub AsJPG()

Dim FileExtension As String
FileExtension = ".jpg"

'// Init folder, doc and counter:
SaveDirectory = ThisDocument.Path
Set doc = Visio.ActiveDocument

'// Loop through pages in the Visio Document:
For Each pg In doc.Pages
    
  '// Setup the filename:
  FileName = pg.Name
    
  '// Add the extension:
  FileName = FileName & FileExtension
  
  '// Save it:
  Call pg.Export(SaveDirectory & FileName)
    
Next

End Sub

Step 4: Save your document. The Visio file also contains your VB code, so saving from either Visio or the Microsoft Visual Basic for Applications editor does the trick. Now you can close down the editor and return to Visio.

Step 5: Run your macro and enjoy all that time you just saved. To run the macro, click on the macro's icon on the Developer ribbon. You should be given two choices:
a) YourDocumentName.ExportAllPages.AsGIF
b) YourDocumentName.ExportAllPages.AsJPG

While it is a little sad that I've never before created a macro for Office, I'm already looking forward to automating everything I do.

Comments

There are currently no comments, be the first to post one.

Post a comment

Name (required)

Email (required)

CAPTCHA image
Enter the code shown above: