Including other documents when Exporting/Printing in Excel

Question

Including other documents when Exporting/Printing in Excel

Jason 0

I have an Excel form with an "output" sheet that compiles the information entered - this is intentionally not using mailmerge, and each time this form is used, it is a fresh copy of the document by necessity. Once the form is complete, the user will export/print to pdf the output sheet, then combine that pdf with other Word or pdf documents, and then send it.

These other documents are not always the same files, but will always be Word or PDF files - if it simplifies things, I could convert everything into either format.

In my dream, I could include these outside documents in the initial export using a column of dropdown lists or checkboxes etc. The user would go down the list and select the documents they need, then just hit print/export and everything would come out together.

I would also be happy with files being pre-selected based on cell values, where certain documents are automatically included.

I have limited knowledge of Macros, but my initial feeling is that due to embedded objects only containing the first page (and seem to reduce quality), I would need to:

Break up my external documents into single pages
Embed each page individually onto worksheets
Create a check of some sort on which sheets/pages should print
Include these in a new print area

My question is:

Is this achievable with VBA or other tools, in a less primitive way than I am envisioning?
Bonus Is there a way for the user to determine which order everything would print in?

Any advice would be greatly appreciated, thanks for your time!

Austin-H 3,745 Reputation points Microsoft External Staff Moderator

2025-08-25T04:25:58.8066667+00:00

Hi @Jason
I hope this comment finds you well.

May I know you have tried on Gabriel's suggestion? We are still looking forward to your update.

If it has been helpful to you, please help to mark this answer as 'Accepted' as it could be referenced for future similar questions.

1 answer

Your answer

Austin-H 3,745 Reputation points Microsoft External Staff Moderator

2025-08-25T04:25:58.8066667+00:00

Hi @Jason
I hope this comment finds you well.

May I know you have tried on Gabriel's suggestion? We are still looking forward to your update.

If it has been helpful to you, please help to mark this answer as 'Accepted' as it could be referenced for future similar questions.

Answer 1

Gabriel-N 4,570 Microsoft External Staff Moderator

Hi Jason

Thank you again for your inquiry about automating your document process in Excel.

After reviewing your goal, here’s the proposed solution. Please note that Excel’s built-in tools can’t merge multiple PDFs into one without external software. However, we can automate everything else such as selection, conversion, and organization, saving you significant time and effort.

Important: All these steps work on the Excel desktop app for Windows. Since you selected the tag “Excel for Android,” I regret to say that VBA cannot run in a mobile environment.

Step 1: Make Your Selections

We’ll add a simple sheet called Document Options to your Excel file. This sheet will let you:

Choose files: Select “Yes” for each document you want to include.
Set the order: Enter numbers (1, 2, 3…) to define the sequence for the final output.

Step 2: Click “Create Documents”

On your main form, you’ll have a macro. Once your selections are ready, click this macro to start the process.

Step 3: Automation in Action

Behind the scenes, the script will:

Prompt you to choose a destination folder.
Export your main Excel sheet as a PDF.
Convert selected Word files to PDF.
Copy any existing PDFs you selected.

Step 4: Organized Output

In moments, you’ll have all files in your chosen folder, named sequentially for easy use (e.g., 01_MainReport.pdf, 02_Terms.pdf, 03_Appendix.pdf). You can then email, print, or review them in the correct order.

This approach eliminates manual steps and ensures consistency. If this meets your requirements, please let me know, I’ll provide the full VBA code and a detailed setup guide.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jason 0 Reputation points

2025-08-15T15:59:50.3333333+00:00

Hi Gabriel,

Thanks very much for your reply! I have not submitted a question before, I didn't intent to select Android; this is for PC.

This sounds really cool and I think would solve my problem, if you are able to provide that VBA it would be fantastic!

Thanks again, I really appreciate your help.

-Jason

Gabriel-N 4,570 Microsoft External Staff Moderator

Hi Jason

Apologies for the delay in getting back to you and thank you for your patience.

I’ve tested a VBA-based approach that should meet your needs for selecting, ordering, and combining documents directly from Excel. Below are the setup instructions:

Step 1: Open a new Excel workbook > Add a worksheet and name it ControlSheet.

In the ControlSheet, create the following three columns:

Doc Name: A descriptive name for each document.
File Path: The full path to the document file. You can obtain this by right-clicking the file and selecting “Copy as path.”
Order: A numeric value indicating the order in which the documents should be merged.

User's image

Step 2: Enable Required References

In the VBA editor (Alt + F11), go to Tools > References and ensure the following libraries are checked:

Microsoft Scripting Runtime

Microsoft PowerPoint xx.x Object Library

Microsoft Office xx.x Object Library

Microsoft Excel xx.x Object Library

Microsoft Word xx.x Object Library

(Note: “xx.x” refers to the version number, which may vary depending on your Office installation.)

Step 3: Once the references are enabled, insert a new module and paste the VBA code provided below to automate the document merging process. User's image

'--- EDITED CODE -- START ---
Sub CombineDocuments_Word()
   ' === PREPARATION AND SETUP ===
   Dim wdApp As Word.Application
   Dim fso As New Scripting.FileSystemObject
   Dim tempFolder As Scripting.Folder
   Const CONTROL_SHEET As String = "ControlSheet"
   Dim tempFolderPath As String
   Dim finalPdfPath As String
   Dim ws As Worksheet
   Dim lastRow As Long
   Dim i As Long, j As Long
   Dim docList() As Variant
   Dim tempVar As Variant
   Dim docCounter As Long
   On Error GoTo ErrorHandler
   ' === READ AND SORT THE DOCUMENT LIST ===
   Set ws = ThisWorkbook.Sheets(CONTROL_SHEET)
   lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   docCounter = 0
   ReDim docList(1 To 4, 1 To lastRow - 1)
   For i = 2 To lastRow
       If ws.Cells(i, "C").Value <> "" And IsNumeric(ws.Cells(i, "C").Value) Then
           docCounter = docCounter + 1
           docList(1, docCounter) = ws.Cells(i, "A").Value ' Doc Name
           docList(2, docCounter) = ws.Cells(i, "B").Value ' File Path
           docList(3, docCounter) = ws.Cells(i, "C").Value ' Order
           docList(4, docCounter) = "" ' Placeholder for temp PDF path
       End If
   Next i
   If docCounter = 0 Then
       MsgBox "No documents selected.", vbInformation
       Exit Sub
   End If
   ReDim Preserve docList(1 To 4, 1 To docCounter)
   ' Sort the array based on the 'Order' column
   For i = 1 To docCounter - 1
       For j = i + 1 To docCounter
           If docList(3, i) > docList(3, j) Then
               tempVar = docList(1, j): docList(1, j) = docList(1, i): docList(1, i) = tempVar
               tempVar = docList(2, j): docList(2, j) = docList(2, i): docList(2, i) = tempVar
               tempVar = docList(3, j): docList(3, j) = docList(3, i): docList(3, i) = tempVar
           End If
       Next j
   Next i
   ' === PROCESS FILES AND CREATE TEMP PDFS ===
   Application.ScreenUpdating = False
   tempFolderPath = fso.GetSpecialFolder(2) & "\ExcelCombine_" & Format(Now, "yyyymmdd_hhmmss")
   Set tempFolder = fso.CreateFolder(tempFolderPath)
   ' Open word to convert file
   Set wdApp = New Word.Application
   wdApp.Visible = False
   For i = 1 To docCounter
       Dim originalPath As String
       Dim tempPdfName As String
       Dim fileExt As String
       originalPath = docList(2, i)
       tempPdfName = tempFolderPath & "\" & Format(i, "000") & "_" & fso.GetBaseName(docList(1, i)) & ".pdf"
       docList(4, i) = tempPdfName
       fileExt = LCase(fso.GetExtensionName(originalPath))
       Select Case fileExt
           Case "pdf"
               fso.CopyFile originalPath, tempPdfName
           Case "doc", "docx"
               Dim wdDoc As Word.Document
               Set wdDoc = wdApp.Documents.Open(originalPath, ReadOnly:=True)
               wdDoc.ExportAsFixedFormat OutputFileName:=tempPdfName, ExportFormat:=wdExportFormatPDF
               wdDoc.Close SaveChanges:=wdDoNotSaveChanges
               Set wdDoc = Nothing
           Case "xls", "xlsx", "xlsm"
               ' Use Excel to convert Excel files
               Dim xlApp As Excel.Application
               Dim xlWb As Excel.Workbook
               Set xlApp = New Excel.Application
               xlApp.Visible = False
               Set xlWb = xlApp.Workbooks.Open(originalPath, ReadOnly:=True)
               xlWb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=tempPdfName, Quality:=xlQualityStandard
               xlWb.Close SaveChanges:=False
               xlApp.Quit
               Set xlWb = Nothing
               Set xlApp = Nothing
       End Select
   Next i
   ' === ASSEMBLE THE FINAL PDF IN WORD ===
   finalPdfPath = Application.GetSaveAsFilename(InitialFileName:="Combined Document.pdf", FileFilter:="PDF Files (*.pdf), *.pdf")
   If finalPdfPath = "False" Then GoTo CleanExit
   Dim mainDoc As Word.Document
   ' Create a new Word file to serve as the main combined document
   Set mainDoc = wdApp.Documents.Add
   ' Insert all temporary PDF files into the main Word document
   For i = 1 To docCounter
       mainDoc.Application.Selection.InsertFile Filename:=docList(4, i)
       If i < docCounter Then
           ' Insert page breaks so the documents don’t get merged together.
           mainDoc.Application.Selection.InsertBreak Type:=7 ' wdPageBreak = 7
       End If
   Next i
   ' Export the merged Word file as the final PDF
   mainDoc.ExportAsFixedFormat OutputFileName:=finalPdfPath, ExportFormat:=wdExportFormatPDF
   mainDoc.Close SaveChanges:=wdDoNotSaveChanges
CleanExit:
   ' === CLEANUP ===
   If Not wdApp Is Nothing Then wdApp.Quit
   If fso.FolderExists(tempFolderPath) Then
       fso.DeleteFolder tempFolderPath, True
   End If
   Set mainDoc = Nothing
   Set wdApp = Nothing
   Set tempFolder = Nothing
   Set fso = Nothing
   Application.ScreenUpdating = True
   If finalPdfPath <> "False" And finalPdfPath <> "" Then
       MsgBox "
   End If
   Exit Sub
ErrorHandler:
   MsgBox "
   GoTo CleanExit
End Sub
'--- CODE END ---

Make sure to save the workbook as Excel Macro-Enabled Workbook (.xlsm) to ensure the VBA code functions properly.

How to Use: Just open the workbook, fill in the three columns on the ControlSheet, and run the macro to export a merged PDF based on the specified order.

Hope this matches your requirements. Let me know if you need any assistance!

Jason 0 Reputation points

2025-08-18T20:00:22.69+00:00
Hi @Gabriel-N

Thanks very much for this, no need to apologize at all - it's extensive and incredible!

I have a couple questions:

Are the instances of MS Word being opened just to export Word docs into PDF? Or will it pull in existing PDFs as well and then export as a batch? I'm worried about existing pdf formats being broken by Word, but perhaps this is unwarranted.

If I wanted to include the summary page of my form in the export, could I use a regular Print to PDF VBA code at the beginning, and have that run before the above?

If it simplifies things, I can explore having all our external documents converted to Word or PDF in advance to eliminate any formatting issues.

Thanks again,

-Jason
Gabriel-N 4,570 Reputation points Microsoft External Staff Moderator

2025-08-19T04:00:24.43+00:00
Hi Jason

Thank you again for your thoughtful questions about the macro. Please allow me to convert your answers into comments for clarity.

As a quick heads-up, please disregard the version of the file I sent previously. I realized I may have sent an incorrect one. I've edited it to the correct, updated version in my previous comment, please review it. My apologies for any confusion this may have caused.

To give you full confidence in the process, I've outlined how it handles each file type and how to include your summary sheet below.

1> How the Macro Handles Different File Types

The macro is designed to handle each file type safely to protect your original documents.

For Word Files (.docx): The macro opens a hidden instance of MS Word in the background. Its only job is to convert these Word documents into temporary PDF files.

For Excel Files (.xlsx): Similarly, when it finds an Excel file, it launches a separate, invisible Excel application. It opens your spreadsheet in a read-only mode (to prevent any accidental changes), exports it as a temporary PDF, and then closes that invisible Excel instance.

For existing PDF Files: The macro does not open these files in Word or Excel. It simply makes a direct, clean copy of the original PDF. Your original file is never altered or re-rendered.

After this initial conversion/copying step, the macro uses a blank Word document to assemble these temporary PDFs into the final, combined file.

2> Including the Excel Summary Sheet

You can absolutely include the summary page in the final document. The straightforward way to do this is to save a copy of that sheet as its own file first. Here is the recommended process:

In your Excel, right-click on the summary sheet's tab.

Select "Move or Copy...".

In the "To book:" dropdown, select "(new book)".

Check the "Create a copy" box at the bottom and click OK.

A new Excel workbook containing only your summary sheet will be created. Save this new workbook to a convenient location.

Finally, add the full file path of this new Excel file to your ControlSheet in the main tool, giving it the order number you want (e.g., 1 to make it the first page).

As a final note, I have tested this macro with the three main file types (Word, Excel, and PDF), and it performs well, you can saw picture in my previous comment, step 1. Please feel free to try it with your own documents to ensure it meets your requirements.

Please be aware that the macro's function is to export and merge the files into a single PDF document; it does not automatically print the file. This is an intentional design choice, as it provides an important opportunity to review the final merged document for accuracy and make any necessary changes before committing it to paper.

I hope this provides a complete picture of how the tool works. Please let me know if you have any other questions.
Jason 0 Reputation points

2025-08-21T16:21:28.9033333+00:00

Thank you so much @Gabriel-N , I'm very excited to test this. I've been sidetracked on other projects the past few days, I'll hopefully get some time today to give it a go.

Thanks again

Share via

Including other documents when Exporting/Printing in Excel

1 answer

Your answer