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.