Printing Excel Sheet from Access 2000...

  • Thread starter Thread starter Kjartan Valdimarsson
  • Start date Start date
K

Kjartan Valdimarsson

I have not done this before, struggled for hours but can't figure it out or
find any readings about it...

From a form, I want to open an Excel worksheet, set a date in a cell (and
some other changes not listed here), print the sheet and then close Excel
without saving the changes. The last version of my code is as follows. I
appreciate your help solving the problems I still have:

Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\PM\PMs"
.Sheets("Daily Checklist").Select
.Cells(3, 2).Formula = Date
'*** Prepare the sheet for printing does NOT work... ***
.ActiveSheet.PageSetup.Orientation = "xlPortrait"
'*** Print sheet - Works, but comes out as Landscape... ***
.ActiveWindow.SelectedSheets.PrintOut
'*** The workbook closes, but I don't want the dialog asking if I
want to save the changes...***
'*** also, I want Excel to close if there are no other workbooks are
open in Excel...***
.Workbooks.Close
End With
Set objXL = Nothing

Thank's in advance,
Kjartan
 
It would be much simpler for you to create an Access report that looked
exactly like your Daily Checklist!
 
I am trying to avoid that. The person creating the checlists, preferes
making them in Excel and changes the contents and look frequently. The
Access application will be making out work orders. Depending on the day, one
of 15+ different checklist (worksheet) is selected from an Excel workbook.
From the Access work order form, they want to print the Excel worksheet with
work order number, date and name of the person that will perform the work...

So I need either code or information where I can read about:
a) Changing the page orientation or simply print using the page setup
already defined in the Excel work sheet
b) Know how to close the Excel application without being asked to save the
changes that I had my code make before printing
 
Kjartan Valdimarsson said:
I have not done this before, struggled for hours but can't figure it out or
find any readings about it...

From a form, I want to open an Excel worksheet, set a date in a cell (and
some other changes not listed here), print the sheet and then close Excel
without saving the changes. The last version of my code is as follows. I
appreciate your help solving the problems I still have:

Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\PM\PMs"
.Sheets("Daily Checklist").Select
.Cells(3, 2).Formula = Date
'*** Prepare the sheet for printing does NOT work... ***
.ActiveSheet.PageSetup.Orientation = "xlPortrait"
'*** Print sheet - Works, but comes out as Landscape... ***
.ActiveWindow.SelectedSheets.PrintOut
'*** The workbook closes, but I don't want the dialog asking if I
want to save the changes...***
'*** also, I want Excel to close if there are no other workbooks are
open in Excel...***
.Workbooks.Close
End With
Set objXL = Nothing

Thank's in advance,
Kjartan
Click to expand...
 
Sorry about the empty post above -
my finger got in motion before my brain.

It looks as if you've got most of what you want.
xlPortrait should not be in quotations; it's a built-in Excel constant
with a value of 1.
(If your code won't compile with xlPortrait without the quotation marks,
just use 1 -
although if you do this, I'd suggest you comment it for future
reference.)

To close the workbook without saving changes, you can use this:
.ActiveWorkbook.Close SaveChanges:=False

As for closing Excel itself,
be sure you issue
objXL.Quit
after you close the workbook, but before you
set objXL = Nothing

Since you started a new instance of Excel for this job, you can terminate it
(with Quit and = Nothing as above); this will not affect any other Excel
workbooks which may or may not be open.

HTH
- Turtle
 
Back
Top