WIN32COM saving/exporting multiple sheets as PDF

Question

I'd like to select multiple sheets (in a certain order) in a workbook and export them as a single PDF using win32com. I tried:

wb_HC.Sheets(['sheetname1','sheetname2']).ExportAsFixedFormat(0, workdir+'\\'+run_date+'_MD_Summary.pdf')

But this does not work. I know that in VBA sheets can be called as an array of sheets:

Array(sheets(1), sheets(2))

But I think this is not equivalent to list() in Python.

Answer 1

Consider using Python's list [] method which could correspond to VBA's Array(). Apply it to the Worksheets() method passing either Sheet index number or Sheet name. Then in the ExportAsFixedFormat qualify the method with xlApp.ActiveSheet:

import os
import win32com.client

workdir = "C:\\Path\\To\\Working\\Directory"
rundate = '2016-09-11'

try:
    xlApp = win32com.client.Dispatch("Excel.Application")
    wb = xlApp.Workbooks.Open(os.path.join(workdir, 'ExcelFile.xlsx'))

    wb.Worksheets(["Sheet1", "Sheet2"]).Select()

    xlTypePDF = 0
    xlQualityStandard = 0

    xlApp.ActiveSheet.ExportAsFixedFormat(xlTypePDF, 
                                          os.path.join(workdir, run_date+'_MD_Summary.pdf'), 
                                          xlQualityStandard, True, True)    
except Exception as e:
    print(e)

finally:    
    wb.Close(False)
    xlApp.Quit

    wb = None
    xlApp = None

By the way, be sure to wrap your processing in try/except/finally blocks. Otherwise, any error you encounter will leave the Excel.exe running in background process. In this setup, the script always closes workbook and uninitializes the COM object regardless of error. The counterpart in VBA would be the On Error Goto ... handling (another best practice method).