[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Macro for Creating a PDF "on the fly" from a Word Doc

Posted on 2013-01-01
11
Medium Priority
?
490 Views
Last Modified: 2013-01-21
Hello Experts.

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:
Sub SaveAsPDF()
  If ActiveDocument.MailMerge.State = wdNormalDocument Then
    MsgBox "Not a Mail Merge Document!"
    Exit Sub
  End If
  CurrentKey = ActiveDocument.MailMerge.DataSource.DataFields("Root").Value
  If CurrentKey = "" Then
    MsgBox "Song Key Not Defined!"
    Exit Sub
  End If
  If Len(ActiveDocument.Path) = 0 Then
    MsgBox "File has no name!"
    Exit Sub
  End If
  DocPath = "SONGCHARTS:PDF:"
  DocType = Right(ActiveDocument.Name, 4)
  Select Case DocType
    Case "docx"
      ExtSize = 5
    Case ".doc"
      ExtSize = 4
  End Select
  DocName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - ExtSize)
  NewDocPath = DocPath & DocName & " *" & CurrentKey & ".pdf"
  ActiveDocument.SaveAs fileName:=NewDocPath, FileFormat:=wdFormatPDF
End Sub

Open in new window


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 open it.

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!

Tony G.
0
Comment
Question by:tonygardner1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 38735746
Personally I would suggest something like:

'At the top of a code module
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) _
    As Long


'Instead of your line  25   openorSave ActiveDocument, "c:\deleteme\fred.pdf", wdFormatPDF

'A new function

Function openorSave(doc As Document, FP As String, FF As Integer)
Dim fso As Object

    Set fso = CreateObject("scripting.filesystemobject")
    If fso.fileexists(FP) Then
        ShellExecute 0, "Open", FP, "", "", 0
    Else
        doc.SaveAs2 FileName:=FP, fileformat:=FF
        ShellExecute 0, "Open", FP, "", "", 0
    End If
    Set fso = Nothing

End Function

Open in new window


Chris
0
 

Author Comment

by:tonygardner1
ID: 38738259
Thank you Chris. Unfortunately, I don't think I'm "connecting the dots" just yet, especially because I've never ventured into the Excel side of the VB scripting world.

If possible, could you provide more detailed instructions on how to call the script when clicking on a cell? I'm also a little unclear as to the role my existing script would play.

Sorry for being so dense, but I am encouraged as I now feel that there is a good chance this can actually be done.

Cheers,
Tony G.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38739037
The redsponse was how to adapt the script you supplied to make the requested change ... therefore it is called exactly as before.

The only application specifics are as commented in thta the shellexecute declaration has to be at the top of the module although I am using 2010 so notice now an error:

Line 24 (in 'my' listing) should be:

doc.SaveAs FileName:=FP, fileformat:=FF

Chris
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tonygardner1
ID: 38739203
My apologies, Chris. I see where we've had a disconnect in our communication. I failed to mention that the VBscript I posted above was created to run from MS Word while the document was already open.

What I was hoping to learn was how to have our function run from Excel when the user clicks on a cell in Column C (perhaps a button or other trigger). The called Subroutine would then need to identify the Song Title in Column A of that row, Song Key in Column B of that row, then either open the corresponding PDF, or if it doesn't exist, open the document in Word, create the PDF, close Word, then open the PDF. See the attached Excel doc for a "boiled down" view.

As I type this, I realize that it could be a significant programming task. If so, I will certainly understand and seek out a developer to take on that task with appropriate compensation.

Kind Regards,

Tony G.
Workbook1.xlsm
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38739221
Whilst that changes my understanding ...

What is needed is to try and build the requirement.  For example to run from excel there isnoactivedocuent so we need to create the word instance and go froma new instance therefore how can the word document be found?

Cheis
0
 

Author Comment

by:tonygardner1
ID: 38739313
As always, good thinking Chris. For starters, all the Word docs for the songs are always named after the song title, and kept in a single directory on a fixed volume. Therefore, I use the =HYPERLINK() function to create an "intelligent" link to open the Word doc based on the data in the other columns. For example, assuming that the Song Title is in cell $A$2, and the Song Key is in cell $B$2, I have the following formula in cell $D$2 to open the Word doc:
=HYPERLINK("SONGCHARTS:WordDocs:"&$A2&".DOCX","Click Me")

Likewise, I have a similar formula which opens the PDF in cell $C$2 (assuming it's already present):
=HYPERLINK("SONGCHARTS:PDF:"&$A2&" *"&$B$2&".PDF","Click Me")

I hope that helps. If not, just let me know and I'll try to clarify further.

Cheers,
Tony G.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38739329
Okay but a bit more ....

In the sample file there is no column d etc just the button in c so how is the folder path added to the column a and b values?

Chris
0
 

Author Comment

by:tonygardner1
ID: 38739390
Of course. Do keep in mind that I'm working on a Mac, so "SONGCHARTS:PDF:" actually refers to a volume and directory. On Windows, it would be something like "S:\PDF\" (where the S: drive is assigned to the external drive containing all the documents).

I referenced Column D earlier to provide a straight-forward illustration as to how the Word doc could be retrieved from within Excel. To help clarify, I have attached an updated version, and will continue to do so for all future posts.

Best,
Tony
Workbook1.xlsm
0
 

Author Closing Comment

by:tonygardner1
ID: 38804199
Per EE, I am closing this Question, and will open another with a more specific objective.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question