Consulting

Results 1 to 17 of 17

Thread: Convert Word to PDF and preview / Error on PDF replacement

  1. #1

    Convert Word to PDF and preview / Error on PDF replacement

    Dear VBA Express,

    This is my first time here so apologies if I forgot something to mention.

    I have 2 macro’s which are doing the following:


    1st macro – Save Word document on open and define a name:



    1. When opening the Word template it asks by message box to fill in the file name and save it to a defined location


    2nd macro – Create PDF:


    1. When finishing the Word document I convert it to PDF and it saves in the same location as the Word version
    2. When changing this Word document and convert it again to PDF, it asks me if I want to overwrite the existing PDF yes or no, when I click on no, I am able to change the document name to a revised version.


    Now my 2 question:

    1. When creating the PDF, I do not only want to save the PDF but also want automatically open the PDF after it has been created to view it.
    2. The second one is an error I get, when creating a PDF from the same document and changing the name to a revised version I get an error message (Option 2 from the 2nd macro): compile error expected function or variable vba - Fault in ActiveDocument.SaveAs2


    Sub Word_ExportPDF()
    'PURPOSE: Generate A PDF Document From Current Word Document
    'NOTES: PDF Will Be Saved To Same Folder As Word Document File
    
    Dim CurrentFolder As String
    Dim FileName As String
    Dim myPath As String
    Dim UniqueName As Boolean
    
    UniqueName = False
    
    'Store Information About Word File
      myPath = ActiveDocument.FullName
      CurrentFolder = ActiveDocument.Path & "\"
      FileName = Mid(myPath, InStrRev(myPath, "\") + 1, _
       InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)
    
    'Does File Already Exist?
      Do While UniqueName = False
        DirFile = CurrentFolder & FileName & ".pdf"
        If Len(Dir(DirFile)) <> 0 Then
          UserAnswer = MsgBox("Deze bestandsnaam bestaat al! Klik " & _
           "[Ja] om te overschrijven. Klik [Nee] om te hernoemen.", vbYesNoCancel)
          
          If UserAnswer = vbYes Then
            UniqueName = True
          ElseIf UserAnswer = vbNo Then
            Do
              'Retrieve New File Name
                FileName = InputBox("Geef een nieuwe bestandsnaam " & _
                 "(zal opnieuw worden gevraagd wanneer u een ongeldige bestandsnaam opgeeft)", _
                 "Voer de bestandsnaam in", FileName)
              
              'Exit if User Wants To
                If FileName = "False" Or FileName = "" Then Exit Sub
            Loop While ValidFileName(FileName) = False
          Else
            Exit Sub 'Cancel
          End If
        Else
          UniqueName = True
        End If
      Loop
      
    'Save As PDF Document
      On Error GoTo ProblemSaving
        ActiveDocument.ExportAsFixedFormat _
         OutputFileName:=CurrentFolder & FileName & ".pdf", _
         ExportFormat:=wdExportFormatPDF
      On Error GoTo 0
    
    'Confirm Save To User
      With ActiveDocument
        FolderName = Mid(.Path, InStrRev(.Path, "\") + 1, Len(.Path) - InStrRev(.Path, "\"))
      End With
      
      MsgBox "PDF opgeslagen in de map: " & FolderName
    
    Exit Sub
    
    'Error Handlers
    ProblemSaving:
      MsgBox "Er was een probleem met het opslaan van uw PDF. Dit wordt meestal veroorzaakt" & _
       " door het originele PDF-bestand dat al open is."
      Exit Sub
    
    End Sub
    Function ValidFileName(FileName As String) As Boolean
    'PURPOSE: Determine If A Given Word Document File Name Is Valid
    'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
    
    Dim TempPath As String
    Dim doc As Document
    
    'Determine Folder Where Temporary Files Are Stored
      TempPath = Environ("TEMP")
    
    'Create a Temporary XLS file (XLS in case there are macros)
      On Error GoTo InvalidFileName
        Set doc = ActiveDocument.SaveAs2(ActiveDocument.TempPath & _
         "\" & FileName & ".doc", wdFormatDocument)
      On Error Resume Next
    
    'Delete Temp File
      Kill doc.FullName
    
    'File Name is Valid
      ValidFileName = True
    
    Exit Function
    
    'ERROR HANDLERS
    InvalidFileName:
    'File Name is Invalid
      ValidFileName = False
    
    End Function
     
    ------------------------------------------------------------------
    Sub AutoNew()
    ActiveDocument.SaveAs2 "C:\Users\Offerte proces\Solution offertes\Offertes\" & InputBox("Geef de correcte bestandsnaam op volgens het volgende formaat, OFNXXXXXXX_COMPANY_NAME", "File SaveAs")
    
    End Sub
    Thank you very much in advance!

    Kind Regards,

    Richard

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Perhaps:
    Sub Word_ExportPDF()
    Dim StrPdf As String, UserAnswer
    StrPdf = Split(ActiveDocument.FullName, ".doc")(0) & ".pdf"
    'Does File Already Exist?
      If Len(Dir(StrPdf)) <> 0 Then
        UserAnswer = MsgBox("Deze bestandsnaam bestaat al! Klik " & _
          "[Ja] om te overschrijven. Klik [Nee] om te hernoemen.", vbYesNoCancel)
        If UserAnswer = vbYes Then
          ActiveDocument.SaveAs2 FileName:=StrPdf, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        ElseIf UserAnswer = vbNo Then
          With Application.Dialogs(wdDialogFileSaveAs)
            .Name = StrPdf
            .Format = wdFormatPDF
            .AddToMru = False
            If .Show <> 0 Then StrPdf = .Name
          End With
        Else
          Exit Sub 'Cancel
        End If
      Else
        ActiveDocument.SaveAs2 FileName:=StrPdf, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      End If
      Documents.Open StrPdf
    End Sub
    
    
    Sub AutoNew()
    ActiveDocument.SaveAs2 "C:\Users\Offerte proces\Solution offertes\Offertes\" & InputBox("Geef de correcte bestandsnaam op volgens het volgende formaat, OFNXXXXXXX_COMPANY_NAME", "File SaveAs")
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Try changing

    ActiveDocument.SaveAs2

    to just

    ActiveDocument.SaveAs
    without the "2"

    The recorder likes to capture the .SaveAs2, but I've found that lots of times running a macro doesn't
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I've never encountered a problem with SaveAs2.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Hi Paul,

    Thank you very much for your responce!

    I am sorry if I was not clear about my question, but to answer my first question I will try to explain.

    The mentioned vba code I have posted is almost what I want, the only extra option I want is: If I convert the word document to pdf, I do not only want to save the pdf but also want Acrobat to open the pdf.

    I hope this makes it more clear now.

  6. #6
    Quote Originally Posted by Paul_Hossler View Post
    Try changing

    ActiveDocument.SaveAs2

    to just

    ActiveDocument.SaveAs
    without the "2"

    The recorder likes to capture the .SaveAs2, but I've found that lots of times running a macro doesn't
    I have tried this, but still get the same error "compile error expected function or variable vba" - Fault in ActiveDocument.SaveAs

    Any other ideas please?

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by richardgo View Post
    If I convert the word document to pdf, I do not only want to save the pdf but also want Acrobat to open the pdf.
    Why do you want Acrobat to open the pdf? What's wrong with opening it in Word? You should be able to view it just the same in Word 2013 & later.


    Using the code I posted, to have Acrobat open the pdf, change:
    Documents.Open StrPdf
    to:
    ShellExecute 0, "Open", StrPdf, "", "", vbNormalNoFocus
    and insert the following function at the top of your code module:
    Public 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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Quote Originally Posted by macropod View Post
    Why do you want Acrobat to open the pdf? What's wrong with opening it in Word? You should be able to view it just the same in Word 2013 & later.


    Using the code I posted, to have Acrobat open the pdf, change:
    Documents.Open StrPdf
    to:
    ShellExecute 0, "Open", StrPdf, "", "", vbNormalNoFocus
    and insert the following function at the top of your code module:
    Public 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
    The reason I want acrobat to open the generated PDF is not exactly to view it, but to send this pdf quotation to a customer in an easy way. Opening the pdf and close it gives me the possibility within Outlook to easely attach it without browsing to the right folder. Outlook shows a couple of attachement that were opened recently. See below:

    Screenshot_1.jpg
    Attached Images Attached Images

  9. #9
    Quote Originally Posted by richardgo View Post
    The reason I want acrobat to open the generated PDF is not exactly to view it, but to send this pdf quotation to a customer in an easy way. Opening the pdf and close it gives me the possibility within Outlook to easely attach it without browsing to the right folder. Outlook shows a couple of attachement that were opened recently. See below:

    Screenshot_1.jpg

    This works great, but in the old code there was the following functionallity: When opening the Word document, the user is forced to give it the right name, this works still fine :-)
    When converting the word file to pdf, the pdf gets the same name as the Word file and save it to the same location, also works great.
    The pdf file that opens now has a different name then the saved pdf, I have put some screenshots below (The last file shows a different name then the saved pdf):
    Screenshot_1.jpgScreenshot_2.jpgScreenshot_3.jpgScreenshot_4.jpg

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by richardgo View Post
    The reason I want acrobat to open the generated PDF is not exactly to view it, but to send this pdf quotation to a customer in an easy way. Opening the pdf and close it gives me the possibility within Outlook to easely attach it without browsing to the right folder.
    That makes no sense at all - you already have the pdf's full path and name for attachment purposes.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    Quote Originally Posted by macropod View Post
    That makes no sense at all - you already have the pdf's full path and name for attachment purposes.
    I am sorry but it does, when the converted pdf with right name opens, outlook will recognize it to simply add it as an attachment because it is already in the list within outlook attachments.

  12. #12
    Quote Originally Posted by richardgo View Post
    I am sorry but it does, when the converted pdf with right name opens, outlook will recognize it to simply add it as an attachment because it is already in the list within outlook attachments.
    But are you able to help me with that please? The only and last thing I need is that it will open the generated pdf.

  13. #13
    Quote Originally Posted by macropod View Post
    That makes no sense at all - you already have the pdf's full path and name for attachment purposes.
    But are you able to help me with that please? The only and last thing I need is that it will open the generated pdf.

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I have already given you all the code you need. You can use it with either the macro I supplied or, with only minor changes, your own code.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #15
    Quote Originally Posted by macropod View Post
    I have already given you all the code you need. You can use it with either the macro I supplied or, with only minor changes, your own code.
    I know, but I have very poor knowledge of VBA so would be realy helpful and appreciated if you could help with this last step ;-)

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Now cross-posted, without acknowledging the help received here (and using the code I posted!) at: VBA code to convert word document to pdf file - Should open the pdf file after creating it - Stack Overflow
    Kindly read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #17
    [QUOTE=macropod;405562]Now cross-posted, without acknowledging the help received here (and using the code I posted!) at:
    Kindly read VBA Express' policy on Cross-Posting in Rule 3:

    You are absolutely right and I apologize for that, just looking for an answer and thought that you did not had the intention to help me further. Absolutely no meaning of calling five cabs to do the same....I will remove the other post and hopefully I will find the right solution.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •