I would like to start off 2013 with an interesting idea for a macro. Let me explain...
I have an Excel spreadsheet that contains hundreds of song titles and their keys. Another column has been setup with a hyperlink formula that opens PATH:Song Title:"*":Key:".pdf". This way, if PDFs have been created in more than one key, you would just change the key, and be able to open the PDF for the song in that key.
To help with the process of creating the PDFs with the "*KEY" suffix, I was able to create the following VB script:
If ActiveDocument.MailMerge.State = wdNormalDocument Then
MsgBox "Not a Mail Merge Document!"
CurrentKey = ActiveDocument.MailMerge.DataSource.DataFields("Root").Value
If CurrentKey = "" Then
MsgBox "Song Key Not Defined!"
If Len(ActiveDocument.Path) = 0 Then
MsgBox "File has no name!"
DocPath = "SONGCHARTS:PDF:"
DocType = Right(ActiveDocument.Name, 4)
Select Case DocType
ExtSize = 5
ExtSize = 4
DocName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - ExtSize)
NewDocPath = DocPath & DocName & " *" & CurrentKey & ".pdf"
ActiveDocument.SaveAs fileName:=NewDocPath, FileFormat:=wdFormatPDF
So I was wondering if it would be possible to take this baby to the next level, and have an expanded version of the above macro be created in Excel so when the user clicks the hyperlink, it would do one of two things: 1) if the PDF already exists, open it; OR 2) if the PDF doesn't exist, create it, then
Well, there it is. Obviously, if you need further details or clarification, don't hesitate to ask!
Best Wishes to Everyone for a Happy and Prosperous 2013!