error 1004 when transfering Query results to Excel 1

[cneill]

Hi I am getting

error 1004 application-defined or object-defined error

I am getting the error at the following point in the code
ActiveSheet.PageSetup.PrintArea = Range("A1", "P30")
Any thought on how to correct this?

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryAttendeeSheet", acViewNormal
DoCmd.OpenQuery "QryAttendeeSheetNames", acViewNormal
DoCmd.SetWarnings True

'Creating the Recordset
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim AttendeeNames As String
Dim AttendeeTel As String
Dim MySheetPath As String
'Variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordset.ActiveConnection = cnn

'Venue Attendee List woodside Park for Spring Period
If Me.Period1 = 1 Then
If Me.Venue1 = 1 Then

'SQL statements to extract the data required for the report - From a query
AttendeeNames = "TblAttendeeSheetNames"
MyRecordset.Open AttendeeNames
' Tell it location of actual Excel file
MySheetPath = "C:\Users\Home\Documents\Dee\Tai Chi\In Harmony\Woodside Park Attendance Sheets\Tai Chi Attendee Sheet Woodside Park.xlsx"
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets("Spring")
XlSheet.Select

XlSheet.Range("A2:A30").ClearContents
XlSheet.Range("O2:O30").ClearContents
XlSheet.Range("P230").ClearContents

'Insert the Recordset in the excel sheet starting at specified cell
XlSheet.Range("A2").CopyFromRecordset MyRecordset
'Clean up and end with worksheet visible on the screen
MyRecordset.Close

'SQL statements to extract the data required for the report - From a query into Spring tab in workbook
AttendeeTel = "TblAttendeeSheet"
MyRecordset.Open AttendeeTel
'Insert the Recordset in the excel sheet starting at specified cell
Set XlSheet = XlBook.Worksheets("Spring")
XlSheet.Range("O2").CopyFromRecordset MyRecordset
MyRecordset.Close

ActiveSheet.PageSetup.PrintArea = Range("A1", "P30")
XlSheet.PrintOut


XlBook.Save
XlBook.Close
Xl.Application.Quit
Set cnn = Nothing
Set XlSheet = Nothing
Set XlBook = Nothing
Set Xl = Nothing

End If
End If

 

[Andrzejek]

Did you try:
[blue]
Xl.[/blue]ActiveSheet.PageSetup.PrintArea = [blue]XlSheet.[/blue]Range("A1", "P30")


Have fun.

---- Andy

 

[SkipVought]

with XlSheet
  .PageSetup.PrintArea = .Range("A1", "P30").Address
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your Print Area will ALWAYS and FOREVER be Range("A1", "P30")???

Unlikely!

A much better approch...

with XlSheet
  .PageSetup.PrintArea = .Cells(1,1).CurrentRegion.Address
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cneill]

Skip your a star, works a treat

one other question
I have set my printer default to print in Black and white, but when it prints the worksheet it prints in colour how do I make it print in black & White?

Thanks

Neill

 

[PHV]

With XlSheet
  .PageSetup.PrintArea = .Cells(1,1).CurrentRegion.Address
  .PageSetup.BlackAndWhite = True
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886