Problem Printing VB.NET Reports with Excel 2003 "PrintOut method of Workbook class failed"

Posted on 2006-05-01
Last Modified: 2008-02-01
Hi Experts,

I am at a loss with the problem I am having lately.  I am trying to print an excel worksheet to file with acrobat distiller.  I can set the printer with no problem, checked that it was correct.  But, when attempting to print to file, I get the error "PrintOut method of Workbook class failed."  I suppose it could be a COM object or reference that I am missing, but that would be surprising considering I can manipulate all aspects of the excel worksheet without any problems.  The only time I get this error is when I try to print to file.  Using just ".Printout" works fine.  The line of code that causes the error is below.  It worked fine until I had to reformat and reinstall visual studio 2000.


objWksht.Printout(PrintToFile:=True, PrToFileName:=SavePath & "\FILENAME.prn")

The reason I cannot use the ".Printout" method is because this is in a loop creating several files and I cannot have the user needing to type in the file name every time.

Thanks, Experts,
Question by:Ren
    LVL 44

    Expert Comment

    Hi Ren,

    not sure if this goes for VB too but in C# you need to pass null references for all optional params if you do not use them, so i''ve not tried this but you could give it a try

    .PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, True, Nothing, SavePath & "\FILENAME.prn")

    hope this helps a bit

    Author Comment

    Thanks very much for the suggestion, bruintje.  Would have been nice if it were that simple.  Unfortunately it didn't do the trick.

    LVL 44

    Accepted Solution

    took me a while, knew the problem forgot the solution :)

    have no printer installed but this came through in my test

    objSh.PrintOut(Reflection.Missing.Value, Reflection.Missing.Value, Reflection.Missing.Value, Reflection.Missing.Value, Reflection.Missing.Value, True, Reflection.Missing.Value, "c:\test.prn")

    Author Comment

    Ah Ha!  I tried your print string as a test.  When the printout to my C drive folder worked, I started to wonder if it was something other than my excel connection.  So, I paused and made excel visible when the print error occurred.  Then I tried the line of code, modified to run that printout command in the immediate window of Excel's VBA editor.  Same error, but an additional message about an acrobat distiller error.  When I looked back on my SavePath variable, I noticed I had commas in this particular folder name.  Since commas often cause problems, I changed them to dashes and boom, printed without any problem.  Good to know!

    You got me started looking elsewhere, full points to bruintje.  Thanks!

    LVL 44

    Expert Comment

    i'm not sure if leaving out the missing.value is a problem or not, it didn't work on my machine without them

    though your error clearly came from something else, thanks for the grade :)


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now