VBA - Save as PDF using ListBox

[Lee Rabbit]

Hi, I have run into a bit of difficulty with a macro I am running that creates a print preview of selected worksheets from a ListBox. Everything works fine and does exactly what I need with regards to printing the selected worksheets.

However, I am also looking to save the selected sheets as one PDF into "C:\PDF\EMAIL" with values from 3 cells with underscore EG: A1 & "_"

This is the code up to the PrintPreview. Is there anyway of expanding on this to create the PDF file?

Sub print_sh()

    Dim i As Long, c As Long
    Dim SheetArray() As String
    
    With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
                
            
            End If
            
        
        Next i
        
        
    
    End With
    Sheets(SheetArray()).PrintPreview
        
        
End Sub

To anyone who can help, a big thanks in advance.
Lee

 

[ranman256]

sDir = "C:\myFolder\"
sFile = sDir & "Output.pdf"
Sheets.Select 'select all sheets
Sheets(1).Activate

ChDir sDir
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= sFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False

 

[Lee Rabbit]

sDir = "C:\myFolder\"
sFile = sDir & "Output.pdf"
Sheets.Select 'select all sheets
Sheets(1).Activate

ChDir sDir
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= sFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False

Hi, thanks for the reply. However, this process will select all sheets in the workbook and create a PDF based on that.

I am trying to select the values based on the SheetArray to combine into a single PDF.

My ListBox is populated with the active sheets in my workbook. So, if I want to create a print file of maybe 5 sheets out of the 40 listed, I just select the items I need from the ListBox, hit the form button and it will create a print preview.

My problem is that I would like the same form button to save the current selected sheets as a PDF in my chosen directory by using cell values.

Filename:="C:\PDF\" & Range("N2").Text & "_" & Range("P2").Text & "_" & Range("Q2").Text & ".pdf", _

Cheers,
Lee

 

[Domenic]

First select all the sheets specified in SheetArray, and then export them to PDF using the ExportAsFixedFormat method of the ActiveSheet object...

    'set the save folder (change accordingly)
    Dim saveFolder As String
    saveFolder = "C:\Users\Domenic\Desktop\"
   
    'set the saveas filename (change accordingly)
    Dim saveFilename As String
    saveFilename = Range("a1").Value & "_" & Range("b1").Value & "_" & Range("c1").Value & ".pdf"
   
    'select the specified sheets
    Sheets(SheetArray()).Select
   
    'export the selected sheets to PDF
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveFolder & saveFilename
       
    'de-select the selected sheets
    Sheets(SheetArray(0)).Select

Note that if a file with the same name already exists in the folder, it will be overwritten.

Hope this helps!

 

[Lee Rabbit]

First select all the sheets specified in SheetArray, and then export them to PDF using the ExportAsFixedFormat method of the ActiveSheet object...

    'set the save folder (change accordingly)
    Dim saveFolder As String
    saveFolder = "C:\Users\Domenic\Desktop\"
  
    'set the saveas filename (change accordingly)
    Dim saveFilename As String
    saveFilename = Range("a1").Value & "_" & Range("b1").Value & "_" & Range("c1").Value & ".pdf"
  
    'select the specified sheets
    Sheets(SheetArray()).Select
  
    'export the selected sheets to PDF
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveFolder & saveFilename
      
    'de-select the selected sheets
    Sheets(SheetArray(0)).Select

Note that if a file with the same name already exists in the folder, it will be overwritten.

Hope this helps!

Domenic, you are a legend sir!!! Works a treat.

3 days I have been trying to get this to work and now I look at your workaround, I see where I was going wrong. Simple mistake my end. DOH!!!

 

[Domenic]

That's great, I'm glad I could help, cheers!