How to convert excel and word files together to the same pdf using excel VBA?

[vergab]

Hi,
I want to convert several files (word and excel mixed) from a folder to a pdf file. This folder contains 3 word files and 9 excel file. I need to create 3 pdf files each contain 1 word and 3 excel files in the same order as in the folder. The word file contains only 1 page and the excel files contain only 1 sheet. I know the code for converting word and excel to pdf separately, but how can I convert them together?

 

[Perpa]

Hi,
I want to convert several files (word and excel mixed) from a folder to a pdf file. This folder contains 3 word files and 9 excel file. I need to create 3 pdf files each contain 1 word and 3 excel files in the same order as in the folder. The word file contains only 1 page and the excel files contain only 1 sheet. I know the code for converting word and excel to pdf separately, but how can I convert them together?

vergab,
One method would be to Select All of the Word file, then copy and pastespecial (As a Picture or bitmap) each Word file into an Excel worksheet in the order you want to see it, and then run one of the two following macros:

Sub Export_All_Sheets_To_1pdf()
   'Sheets(Array("Sheet1", "Sheet5")).Select    'if not all sheets...OR
   Sheets.Select    'for all sheets in the workbook
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
   "C:\Users\myuser\Desktop\NewBook.pdf", Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True
   Sheets(1).Select
End Sub

OR....A little different....
The following macro loops thru all sheets and selects the print area on each sheet
then copies it to a new sheet and appends all of these areas, then you can then do
what you like with the final sheet, make a pdf, or print it. The macro selects all print
areas from all sheets apart from one called 'setup' , you can amend accordingly.

Sub Copy_Print_Areas()
    Dim wshTemp As Worksheet, wsh As Worksheet
    Dim lDestRw As Long
    
    Application.ScreenUpdating = False
    Set wshTemp = Sheets.Add(After:=Worksheets(Worksheets.Count))
    For Each wsh In ActiveWorkbook.Worksheets
        With wsh
            If .Name <> wshTemp.Name And .Name <> "Setup" Then   ' Change this line accordingly
                If .ProtectContents = True Then
                    .Unprotect
                End If
                If .PageSetup.PrintArea <> "" Then
                    With wshTemp.UsedRange
                        lDestRw = .Row + .Rows.Count + 2
                    End With
                    .Range(.PageSetup.PrintArea).Copy
                    wshTemp.Cells(lDestRw, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    wshTemp.Cells(lDestRw, 1).PasteSpecial Paste:=xlPasteColumnWidths
                    wshTemp.Cells(lDestRw, 1).PasteSpecial Paste:=xlPasteFormats
                    Cells.EntireColumn.AutoFit
                End If
            End If
        End With
    Next wsh
    Application.ScreenUpdating = True
End Sub

See if one of these methods will work for you. Be sure to try on a copy of your workbook first to avoid any unintended loss of data. Good luck!
Perpa

 

[vergab]

Hi Perpa,
I am a little bit confused. Which of them open and copy the word files from the folder? The folder contain the files like this: f1.doc, f11.xlsx, f12.xlsx, f13.xlsx, f2.doc, f21,xlsx, f22.xlsx, f23.xlsx, and so on. The pdf should be like this: f1.pdf (contains: page1->f1.doc, page2->f11.xlsx, page3->f12.xlsx, page3->f13.xlsx) and so on.

 

[Perpa]

Hi Perpa,
I am a little bit confused. Which of them open and copy the word files from the folder? The folder contain the files like this: f1.doc, f11.xlsx, f12.xlsx, f13.xlsx, f2.doc, f21,xlsx, f22.xlsx, f23.xlsx, and so on. The pdf should be like this: f1.pdf (contains: page1->f1.doc, page2->f11.xlsx, page3->f12.xlsx, page3->f13.xlsx) and so on.

vergab,
In answer to your question: "Which of them open and copy the word files from the folder?"
My answer: Neither.

You had not indicated in your first post how your files would be named, nor what folder they would be in...so I gave you a 'manual' method whereby you individually copied the Word (.doc) file and pastespecial it into a new worksheet. Likewise each xlsx file would have been manually copied to another worksheet in that same new workbook with the doc file. You would then run the macro in the new workbook which contains those 4 worksheets to convert all 4 to one pdf. If there are only 12 files total, this would be a fairly simple task.

How many files are you wanting to convert to pdfs?
Will you be using the 'f1' name again, or will you keep incrementing?

As I understand from your latest post, you are looking for the macro to do the opening, copying, and pasting and converting the 4 sheets into one pdf.

Are the files all named as you have shown, ie. 'f1.doc, f11.xlsx, f12.xlsx, f13.xlsx' and 'f2.doc, f21,xlsx, f22.xlsx, f23.xlsx'?

Are there always just 4 files?

When these questions are answered perhaps we can move forward and automate the process.
Perpa

 

[Macropod]

Depending on the size of the sheets, the simplest method might be to copy each one and paste it into the corresponding Word document using the Paste Special method, with whichever paste format best suits your needs. If you use the Paste Link option, the embedded worksheets will even update to reflect changes in the source workbooks. You can then save the Word document as a PDF without the need for any further drama. Do note that an Excel object pasted into a Word document cannot span a page break. You could, however, paste a series of suitably-sized worksheet segments into the Word document so that each fits within a page at a reasonable zoom level.

 

[Perpa]

Depending on the size of the sheets, the simplest method might be to copy each one and paste it into the corresponding Word document using the Paste Special method, with whichever paste format best suits your needs. If you use the Paste Link option, the embedded worksheets will even update to reflect changes in the source workbooks. You can then save the Word document as a PDF without the need for any further drama. Do note that an Excel object pasted into a Word document cannot span a page break. You could, however, paste a series of suitably-sized worksheet segments into the Word document so that each fits within a page at a reasonable zoom level.

Paul,
Thank you for getting into this issue. I was working on, and have a macro solution, but it is not as automated as I would like, nor is it one of my better macro efforts.
You solution seems straight forward, less 'dramatic'. Thank you again.
Perpa

 

[vergab]

Hi Paul,

How can I code these steps in to a VBA? In case of 100 pdf (it means 100 doc and 400 xls) it is necessary to automate the copy and convert steps.

 

[Macropod]

You previously said there were 3 documents and 9 worksheets. Now it's 100 & 400, respectively. Perhaps you could explain exactly what you're trying to achieve. Producing 100 PDFs does not necessarily mean you need to have 100 documents and however many worksheets (is it 3, or 4?) each document links to.

 

[Perpa]

Hi Paul,

How can I code these steps in to a VBA? In case of 100 pdf (it means 100 doc and 400 xls) it is necessary to automate the copy and convert steps.

verbag,
Here is some code I have been working on...I finally got the Word file code working.
I assumed for each docx file there would be 3 xlsx files, numbered as per your 1st post.

HOW TO RUN THIS MACRO AND CREATE MULTIPAGE PDF FILES:
To start make sure you have 4 Sheets present in the file that will hold the macros, I called that file 'Master.xlsm'.You will be asked to enter the last file number in an inputbox. All the files to be converted to pdf must exist in the same folder:

f1.docx, f11.xlsx, f12.xlsx, and f13.xlsx; f2.docx, f21.xlsx, f22.xlsx, and f23.xlsx etc, and the file from which you run the macro, in this case it was named 'Master.xlsm'

Copy and paste the following 2 macros into a general code module using the Alt+F11 to open the Visual Basic Editor. Close the Editor, then SaveAs a macro enabled (xlsm) file.

As always, run on a copy of your workbook first so you don't lose any data.
Using Alt+F8, select 'ConsolidateSheetsToPDF' then 'Run' the macro.
The pdf files will be placed in the same folder with all the other files, f1,f11, f12, f13, and Master.xlsm
Perpa

Sub ConsolidateSheetsToPDF()
Dim n, LR1, LR2, LastF As Long
Dim FName As String
    Set wb = ThisWorkbook
    LastF = InputBox("Enter the last number for the f-values, ie, for 'f10' you enter 10")
    
    Application.ScreenUpdating = False
    For f = 1 To LastF      'Uncomment to run pdfs rapid fire
        Range("B1").Value = "f" & f
        wb.Sheets("Sheet1").Range("A2:J50").ClearContents
        wb.Sheets("Sheet2").UsedRange.ClearContents
        wb.Sheets("Sheet3").UsedRange.ClearContents
        wb.Sheets("Sheet4").UsedRange.ClearContents
    
        n = 1
    
        xDirect$ = ThisWorkbook.Path & "\"
        xFname$ = Dir(xDirect$)
        
            Do While xFname$ <> ""
                If xFname$ = "Master.xlsm" Then GoTo Passem     'Skip the Workbook you are copying data to, ie. "Master.xlsm"
                If xFname$ = wb.Sheets("Sheet1").Range("B1").Value & ".docx" Then
                    Call WordFileInsert
                    n = n + 1
                    GoTo Passem
                End If
    
                If xFname$ = wb.Sheets("Sheet1").Range("B1").Value & "1.xlsx" Or xFname$ = wb.Sheets("Sheet1").Range("B1").Value & "2.xlsx" Or xFname$ = wb.Sheets("Sheet1").Range("B1").Value & "3.xlsx" Then
                    Workbooks.Open (xDirect$ & xFname$), UpdateLinks:=False
                    
                    Application.Workbooks(xFname$).Activate
                    Set srcwb = Workbooks(xFname$)
                    
                    LR1 = srcwb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
                    wb.Sheets(n).Range("A1").Value = xFname$   'Filename of copied xlsx file
                    wb.Sheets(n).Range("A2").Value = "Sheetname: " & srcwb.ActiveSheet.Name
                    srcwb.Sheets(1).Range("A1:I" & LR1).Copy Destination:=wb.Sheets(n).Range("A3")
    
                    Application.CutCopyMode = False 'Clear Clipboard
                    srcwb.Close savechanges:=False
                    n = n + 1     'Advance the count to the next sheet number
                End If
             
Passem:
                xFname$ = Dir
                
            Loop
        
        wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select    'if not all sheets...OR
       'Sheets.Select    'for all sheets in the workbook
    
        FName = xDirect$ & wb.Sheets("Sheet1").Range("B1").Value & ".pdf"    'PDF Filename
    
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Sheets(1).Select
    Next f
    Application.ScreenUpdating = True
End Sub

Sub WordFileInsert()
'This macro will pastespecial the Word document (docx file) that is copied to the clipboard
Dim wdApp As Object
Dim wdDoc As Object
Dim FName As String
Set wb = ThisWorkbook
FName = wb.Sheets("Sheet1").Range("B1").Value
Application.ScreenUpdating = False
'open the word document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "\" & FName & ".docx")
    wdDoc.Range.Select   'select everything in the word document
    wdDoc.Range.Copy
    wdApp.Visible = True    'show the word document
    
'Go back to Excel
    wb.Sheets("Sheet1").Range("A2").Select
    
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    
'Make the background grid all white
    Range("A2:I44").Select
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    wdApp.Quit    'Close Word
    Application.ScreenUpdating = True
    Range("B1").Select
End Sub