Solved

Error when running macro that merges and saves MSWord docs as PDFs

Posted on 2013-06-06
4
468 Views
Last Modified: 2013-06-11
Hi Experts

I've found some very useful macro code which helps me to automatically merge and save individual PDFs documents.

I've associated the macro with a button in a MSWord document. It automatically opens my MSWord document (which is a certificate) that contains merge fields linking it to a MSAccess query and then creates individucal MSWord documents for each records and finally PDFs them in the same process.

Everything works as I would except it to and all PDFs get created, however I'm receiving this error at the end of the process, "This file is in use by another application or user."

Can anyone help with with why this happens? I have attached a text document with the code.

Thanks in advance
darls15
MergePDFsCode.txt
0
Comment
Question by:darls15
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39228588
I can't quarrel with the first macro. I recognise one of my own comments in it.

However the second one tries to open every file in the folder and create a PDF from it, even if it is already a PDF. The rename from .doc to .pdf won't do anything, so you will be trying to export to a file that is already open. This version filters out the files that aren't Word documents.
Sub ConvertWordsToPdfs()

    Dim strFolder As String
    Dim strFile As String
    Dim doc As Document
    Dim newName As String
    Dim strNameParts() As String
    
    
    strFolder = "G:\General\OUTPUT"
    strFile = Dir(strFolder & "\*.doc*") ' word document files only (.doc, .docx, .docm)
    Do Until strFile = ""
        strNameParts = Split(strFile, ".")
        strNameParts(UBound(strNameParts)) = "pdf" .set extension to pdf
        newName = Join(strNameParts, ".")
        Set doc = Documents.Open(strFolder & "\" & strFile)
        doc.ExportAsFixedFormat OutputFileName:=strFolder & "\" & newName, _
                ExportFormat:=wdExportFormatPDF
        doc.Close wdDoNotSaveChanges
        strFile = Dir
    Loop

End Sub

Open in new window

If you don't need to have Word document copies you can export in the first macro:
Sub MergeToPDFs()
    Dim wdMainDoc As Word.Document
    Dim r As Integer
    Dim rng As Word.Range
    Dim wdResultDoc As Document
    
    Set wdMainDoc = Documents.Open("G:\General\Certificates.docm")
    With wdMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        .DataSource.ActiveRecord = wdFirstDataSourceRecord
        Do
            'Do the Mail Merge to new document
            With .DataSource
                .FirstRecord = .ActiveRecord
                .LastRecord = .ActiveRecord
            End With
            'Set the value from the merge data
            .Execute
            'Export the resulting document
            Set wdResultDoc = ActiveDocument
            wdResultDoc.ExportAsFixedFormat _
                OutputFileName:="G:\General\OUTPUT\" & .DataSource.DataFields("RegESW").Value & ".doc", _
                ExportFormat:=wdExportFormatPDF
            wdResultDoc.Close wdDoNotSaveChanges
            r = .DataSource.ActiveRecord
            .DataSource.ActiveRecord = wdNextDataSourceRecord
            'recordcount might not be findable (=-1),
            'so just check to see if we're stuck on the same record
        Loop Until r = .DataSource.ActiveRecord
    End With
    wdMainDoc.Close wdDoNotSaveChanges
End Sub

Open in new window

0
 

Author Comment

by:darls15
ID: 39236631
Hi Graham

First of all, thank you for the original macro, this is going to help me a lot!

I tried your amended ConvertWordsToPdfs macro and it works very well and all PDFs are created without error.

I then tried your second macro as I don't need to keep the copies of the Word documents however it only created the Word versions and stopped.

Can you please help?

Thanks
darls15
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39236996
I did fail to change the extension for the output file to .pdf. This is now corrected.

Also, for easier maintenance, the three strings that are likely to need changing to suit individual circumstances have been pulled out to be at the start of the procedure.
Sub MergeToPDFs()
    Dim wdMainDoc As Word.Document
    Dim r As Integer
    Dim wdResultDoc As Document
    
    Dim strMainDocument As String
    Dim strOutputFolder As String
    Dim strDataFieldForName As String
    
    strMainDocument = "C:\MyMergeDocs\MMtest.docx"
    strOutputFolder = "C:\PDFFolder\"
    strDataFieldForName = "NameField"
    
    Set wdMainDoc = Documents.Open(strMainDocument)
    With wdMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        .DataSource.ActiveRecord = wdFirstDataSourceRecord
        Do
            'Do the Mail Merge to new document
            With .DataSource
                .FirstRecord = .ActiveRecord
                .LastRecord = .ActiveRecord
            End With
            .Execute
            'Export the resulting document
            Set wdResultDoc = ActiveDocument
            wdResultDoc.ExportAsFixedFormat _
                OutputFileName:=strOutputFolder & .DataSource.DataFields(strDataFieldForName).Value & ".pdf", _
                ExportFormat:=wdExportFormatPDF
            wdResultDoc.Close wdDoNotSaveChanges
            r = .DataSource.ActiveRecord
            .DataSource.ActiveRecord = wdNextDataSourceRecord
            'recordcount might not be findable (=-1),
            'so just check to see if we're stuck on the same record
        Loop Until r = .DataSource.ActiveRecord
    End With
    wdMainDoc.Close wdDoNotSaveChanges
End Sub

Open in new window

0
 

Author Closing Comment

by:darls15
ID: 39239452
This is brilliant & works perfectly! Thanks for all your help Graham.

Have a great day.
darls15
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now