By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

OLE automation: Excel Template question

P: n/a
As a novice VBA programmer, I have a problem with a routine written as
a click procedure from a form to open an Excel template, insert some
information, print, and (ideally) return control back to the form. The
problem is only in that I don't want to answer the question from Excel
"Do you want to save (y/n):". I just want to print the sheet. Can I
do this in such a way that excel will not prompt me to save? I don't
see any alternative but to unload the form, and that is what causes the
prompt to save. I'm sure there is a better way to do this, but as I
said, I'm new to this stuff. Any help is appreciated. Code appears
below.

Craig
cu****@hotmail.com

Option Compare Database
Dim xlObject As Object ' Declare variable to hold the reference to the
Excel Object.

Private Sub AutomateExcel_Click()
'On Error GoTo Err_AutomateExcel_Click

'Create an object for Excel
Set xlObject = CreateObject("excel.application")

'Make the Excel application visible
xlObject.Visible = True
Set xlBook =
xlObject.Workbooks.Add("\\Termsrvr\tshome\******** \********\Dev\TS.xlt")
'Activate the worksheet, select the range, activate a cell in the range
xlObject.Worksheets("Sheet1").Activate
xlObject.ActiveSheet.Range("B5").Select
xlObject.ActiveSheet.Range("B5").Activate

'Set focus to LNF on Access form
LNF.SetFocus
'Place the LNF in the active cell.
xlObject.ActiveCell.Value = UCase(LNF.Text)

xlObject.ActiveSheet.Range("F5").Select
xlObject.ActiveSheet.Range("F5").Activate

'Set focus to TSEmpLocation on Access form
TSEmpLocation.SetFocus
'Place TSEmpLocation in the active cell.
xlObject.ActiveCell.Value = TSEmpLocation.Text

xlObject.ActiveSheet.Range("I5").Select
xlObject.ActiveSheet.Range("I5").Activate

'Set focus to TSEmpNum on Access form
TSEmpNum.SetFocus
'Place TSEmpNum in the active cell.
xlObject.ActiveCell.Value = TSEmpNum.Text

xlBook.PrintOut
On Error Resume Next
xlObject.UserControl = True

Exit_AutomateExcel_Click:
xlObject.Quit
Set xlBook = Nothing
Set xlObject = Nothing
Exit Sub
Err_AutomateExcel_Click:
MsgBox Err.Description
Resume Exit_AutomateExcel_Click

End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Jan 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
All you need to do is insert this line when you are all done with the
workbook:

xlBook.Close False

The "false" value is for the SaveChanges parameter...

Jay Taplin MCP

Jan 16 '06 #2

P: n/a
Just what I needed - Thank you so much

Jan 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.