Macro to create a PDF of an external excel workbook

[davidalldred]

Hi peeps, I am pretty sure that this can be done, but I have no idea where to start so I am hoping someone here can.

What I would like to have is a macro-enabled workbook that will allow you to select another workbook with multiple worksheets and either save it as a PDF or print it to a PDF.

My thoughts of a solution would be to have a Macro Workbook that you open, and you would either put the address into a cell and run the macro or you run the macro and a pop up window opens up and you go to the workbook file location that you want to print to PDF. It would then create, save and open the PDF so that it can be checked.

I know how to create a macro to do this within a workbook, but I don't want to make it into a macro-enabled workbook.

I don't have any examples to attach as it's just an idea I have to make it easy to create PDFs.

Thanks in advance for any solutions.

Dave.

 

[mumps]

I know how to create a macro to do this within a workbook, but I don't want to make it into a macro-enabled workbook.

You can't have a macro in a workbook without that workbook being macro-enabled.

 

[davidalldred]

You can't have a macro in a workbook without that workbook being macro-enabled.

Thanks mump. Yes I know that you need a macro enabled workbook to add macro. What I'm looking for is in effect a user interface that will create a PDF of any excel. I've got a macro that does something similar but it combines the first worksheet in multiple workbooks (not macro enabled) into one workbook. I thought I could adapt this coding to what I need but after trying somethings I can't seem to so I came to the conclusion it can't, and so I posted this.

Dave.

 

[mumps]

To clarify, you want to select an Excel file and covert it to a PDF file. Is this correct?

 

[davidalldred]

To clarify, you want to select an Excel file and covert it to a PDF file. Is this correct?

Correct, but doing it through another workbook.

 

[Skyybot]

@davidalldred So you want a user to select a Workbook that they are not currently using to turn into a PDF?

 

[davidalldred]

@davidalldred So you want a user to select a Workbook that they are not currently using to turn into a PDF?

Thanks Skyybot. Yes, that's correct.

 

[mumps]

Change the folder path of the save destination (in red) and the file name (in blue) to suit your needs.

Sub CreatePDF()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer, srcWB As Workbook
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select an Excel File"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set srcWB = Workbooks.Open(FileName)
    ChDir "C:\Test\"
    With ActiveWorkbook
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:="MyFile.pdf"
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

 

[davidalldred]

Thanks Mumps. I will try it out when I can find 5mins. Cheers. Dave

 

[davidalldred]

Change the folder path of the save destination (in red) and the file name (in blue) to suit your needs.

Sub CreatePDF()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer, srcWB As Workbook
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select an Excel File"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set srcWB = Workbooks.Open(FileName)
    ChDir "C:\Test\"
    With ActiveWorkbook
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:="MyFile.pdf"
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

Thanks again Mumps. managed to give it go and it is working exactly how I imagined it would (but I am sure you already know this :~) ) thanks for spending the time to resolve, it is appreciated!

Cheers.

Dave.