1

I have working code that will convert an xls to pdf, however, the code converts the whole workbook and I really just need to select a single sheet out of the workbook, but I can't figure out how to do it.

The code I currently use is:

Dim fileName As String = "filepath\filename"
Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
xlsApp.ScreenUpdating = False
Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
Dim paramExportFormat As XlFixedFormatType = XlFixedFormatType.xlTypePDF
Dim paramExportQuality As XlFixedFormatQuality = XlFixedFormatQuality.xlQualityStandard
Dim paramOpenAfterPublish As Boolean = False
Dim paramIncludeDocProps As Boolean = True
Dim paramIgnorePrintAreas As Boolean = True
Dim paramFromPage As Object = Type.Missing
Dim paramToPage As Object = Type.Missing
xlsBook = xlsApp.Workbooks.Open(fileName & ".xls", UpdateLinks:=False, ReadOnly:=False)
xlsBook.ExportAsFixedFormat(paramExportFormat, fileName & ".pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish)
xlsBook.Close(SaveChanges:=False)
xlsApp.Quit()

Where in the code do I need to specify the sheet in the workbook? Note that I will need to make it so that depending on an option fed into the app, the sheet will change, though I don't think that should make a difference, but I thought I'd mention it either way.

MaQleod
  • 1,011
  • 6
  • 22
  • 33
  • 1
    Can you use the `paramFromPage`, `paramToPage` parameters? – Robert Harvey Jul 06 '11 at 22:55
  • I don't know, but I will certainly try, not sure that affects the actual sheet in a workbook though. – MaQleod Jul 06 '11 at 23:04
  • 1
    If it's always printing the entire workbook, and the sheet that you want is on a particular page, and the workbook size never changes, you ought to be able to just specify the page you want printed (a lot of assumptions, I know). – Robert Harvey Jul 06 '11 at 23:06
  • That did work, I just changed them to Integer and specified both for page 1 and it only converted the first worksheet (first page). – MaQleod Jul 06 '11 at 23:17
  • I know it's an old question but you don't happen to remember how you specified the sheet do you? – Alex Aug 01 '12 at 13:46

1 Answers1

3

Try to use ExportAsFixedFormat method

Private Sub SaveWorksheetAsPDF()
    Dim mySheet As Microsoft.Office.Tools.Excel.Worksheet = xlsBook(0)

    mySheet.ExportAsFixedFormat( _
        Excel.XlFixedFormatType.xlTypePDF, _
        "c:\myWorksheet", _
        Excel.XlFixedFormatQuality.xlQualityStandard, _
        True, _
        True, _
        1, _
        10, _
        False)
End Sub
Vlad Bezden
  • 83,883
  • 25
  • 248
  • 179
  • If you look at the code I posted, I'm already using that method, but I created a workbook object and tried to export a single sheet, not a worksheet object for just the sheet I was interested in. – MaQleod Jul 15 '11 at 05:33
  • I don't see in your code that you export one sheet. If you look at my code I have xlsBook(0) - which is first sheet. In your code it might be second or third. Try to use xlsBook(x) instead. – Vlad Bezden Jul 15 '11 at 05:43