0

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.

Jonathan Mak
  • 69
  • 2
  • 8

1 Answers1

4

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).

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I believe there is a better way. Excel allows something like this: Sheets(Array("Sheet1", "Sheet2")).Select Activesheet.(code to export) The problem is how do we "select" multiple sheets in win32com and pass it to the next line as "Activesheet". – Jonathan Mak Sep 12 '16 at 04:28
  • I stand corrected, good sir. Indeed you can save [multiple sheets with your Array() selection](http://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf). See update using `[]` for `Array()` substitute. – Parfait Sep 12 '16 at 23:52