Click here to Skip to main content
15,917,328 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to open an excel workbook as a conduit to print out data.
I use the following code;
Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    oXL = CreateObject("Excel.Application")
    If oXL Is Nothing Then
            MessageBox.Show("Excel is not properly installed!!")
            Return
    End If
    oXL.Visible = True
    oXL.UserControl = False ' How to stop excelclosing immediately
        'create workbook
    oWB = oXL.Workbooks.Add 
'select the active worksheet
    oSheet = oWB.ActiveSheet
    ' add the code to populate the form
    ' then print the excel sheet and close  excel
    PrintForm1.PrinterSettings.PrinterName = "Microsoft Print to PDF"
        
        If PrintForm1.PrinterSettings.IsValid Then
            PrinterName = PrintForm1.PrinterSettings.PrinterName
            oSheet.PrintOut(From:=1, To:=1, Copies:=1, ActivePrinter:=PrinterName, Collate:=True, Preview:=True)

            'Make sure that you release object references.
            oRng = Nothing
            oSheet = Nothing
            oWB.Close(SaveChanges:=False)
            oXL.Quit()
            oXL = Nothing

        Else
            MessageBox.Show("Microsoft Print to PDF printer not set up!")
        End If


The code successfully prints out the required form.

The problem is that a singles sheet excel document was generated by
oXL = CreateObject("Excel.Application")

and then a workbook is generated by
oWB = oXL.Workbooks.Add 

Then when I come to closing Excel, firstly,using
oWB.Close(SaveChanges:=False)
to close the workbook without saving. Then using
oXL.Quit()
to close Excel BUT then I am asked if I want to save the Sheet Excel file.

How do I stop the single Excel Sheet being opened or alternately how do I close it without saving?

What I have tried:

I have tried to find info on the internet to sort the problem but failed.
Posted
Updated 6-Feb-18 6:50am

1 solution

The reason you're asking about saving Excel file is that that piece of code responsible for closing workbook and releasing resources is inside If - End if statement, but it have to be outside of it.
VB
 If PrintForm1.PrinterSettings.IsValid Then
     PrinterName = PrintForm1.PrinterSettings.PrinterName
     oSheet.PrintOut(From:=1, To:=1, Copies:=1, ActivePrinter:=PrinterName, Collate:=True, Preview:=True)

     'Make sure that you release object references.
     oRng = Nothing
     oSheet = Nothing
     oWB.Close(SaveChanges:=False)
     oXL.Quit()
     oXL = Nothing
 Else
     MessageBox.Show("Microsoft Print to PDF printer not set up!")
 End If

'Make sure that you release object references.
oRng = Nothing
oSheet = Nothing
oWB.Close(SaveChanges:=False)
oXL.Quit()
oXL = Nothing
 
Share this answer
 
v2
Comments
Dave the Golfer 13-Feb-18 5:33am    
Thank you. Worked perfectly.
Maciej Los 13-Feb-18 5:48am    
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900