Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I save an Excel spreadsheet to PDF using VB.net

Posted on 2014-03-11
7
Medium Priority
?
4,966 Views
Last Modified: 2014-03-16
This is the code I use to save a Word document to a PDF.  I need similar code for an Excel spreadsheet.  Any help will be appreciated!!

Thanks!

Dim savePath As String = "c:\ticket.pdf"
Dim saveAsFormat As Word.WdSaveFormat = Word.WdSaveFormat.wdFormatPDF
                oDoc.SaveAs(savePath, saveAsFormat, , , , , , , , , , , , , , )

If My.Computer.FileSystem.FileExists(savePath) = True Then
  System.Diagnostics.Process.Start(savePath)
Else
  MsgBox("")
End If

oWord.ActiveDocument.Close(Word.WdSaveOptions.wdDoNotSaveChanges)
oWord.Quit()
0
Comment
Question by:Karen Wilson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39920280
Use ExportAsFixed:

oExcel.ActiveSheet.ExportAsFixedFormat( Excel.XlFixedFormatType.xlTypePDF, _
        "C:\Temp", _
        Excel.XlFixedFormatQuality.xlQualityStandard, True, True, 1, 10, False)

Open in new window

0
 

Author Comment

by:Karen Wilson
ID: 39920342
I get an error message, "Document not saved.  The document may be open, or an error may have been encountered when saving."  

I am trying different configurations to figure it out.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39920441
Please show the code you are using.
0
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 

Author Comment

by:Karen Wilson
ID: 39920497
objApp.ActiveSheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, "C:\Temp", Excel.XlFixedFormatQuality.xlQualityStandard, True, True, 1, 10, False)
0
 

Author Comment

by:Karen Wilson
ID: 39920843
I got it to save to a PDF.  Now I can't get it to close automatically.  Here is the code:

Dim dow As String = Today.Date.ToString("s")

Dim useDate As String = dow.Remove(10)
useDate = useDate.Replace("-", "_")


Dim savePathPDF As String = "\\wstf-fs01\envr-files\5YearPlans\" & useDate & ".pdf"
Dim saveAsFormat As Excel.XlFixedFormatType = Excel.XlFixedFormatType.xlTypePDF

objBook.ExportAsFixedFormat(saveAsFormat, savePathPDF, Excel.XlFixedFormatQuality.xlQualityStandard, True, True, 1, , False, )


objBook.ActiveSheet.Close(Excel.XlSaveAction.xlDoNotSaveChanges) - this not working
objApp.Quit()
0
 

Accepted Solution

by:
Karen Wilson earned 0 total points
ID: 39921131
The fix:

Dim dow As String = Today.Date.ToString("s")

Dim useDate As String = dow.Remove(10)
useDate = useDate.Replace("-", "_")


Dim savePathPDF As String = "\\wstf-fs01\envr-files\5YearPlans\" & useDate & "_5YearPlan.pdf"

Dim saveAsFormat As Excel.XlFixedFormatType = Excel.XlFixedFormatType.xlTypePDF

objBook.ExportAsFixedFormat(saveAsFormat, savePathPDF, Excel.XlFixedFormatQuality.xlQualityStandard, True, True, 1, , True, ) - the last true opens the PDF for the user to review


Me.objApp.ActiveWorkbook.Close(SaveChanges:=False)
objApp.Quit()
0
 

Author Closing Comment

by:Karen Wilson
ID: 39932365
I have spent hours doing trial and error.  This is mine!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question