Selecting/Printing a Named Range via VBA

[M]

I am using a form in Access 97 to open an Excel template
containing various charts. There are several worksheets
with one chart on each sheet. I have code that executes
based on checkbox selections that in turn prints the
selected charts. Here is the code:

If MyForm!chkPrintChart1.Value = True Then
Set xlsWorksheet = xlsWorkbooks.Worksheets(1)
xlsWorksheet.PrintOut
Set xlsWorksheet = Nothing
End If

This code works fine until the user changes the
destination printer. I want to reset the PrintArea to a
named range whenever the printer is changed. So far I have
used this line:

xlsWorksheet.PageSetup.PrintArea="DisbursementsPrintArea1"

inserted before the 'xlsWorksheet.PrintOut' line, but I am
getting a RunTime error 1004 'Unable to set PrintArea
property of the PageSetup class'.

So I tried this:

Set xlsRange=xlsWorksheet.Names
("DisbursementsPrintArea1").RefersToRange

If MyForm!chkPrintChart1.Value = True Then
Set xlsWorksheet = xlsWorkbooks.Worksheets(1)
Set xlsRange=xlsWorksheet.Names
("DisbursementsPrintArea1").RefersToRange
xlsRange.Select
xlsRange.PrintOut
Set xlsRange = Nothing
Set xlsWorksheet = Nothing
End If

But I now get RunTime error 1004 'Application defined or
Object-defined error.

I have also tried selecting the ChartObject itself on the
selected Worksheet but this hasn't worked either.

I know this can work --- How?

Thanks

M

 

[Dave Peterson]

Maybe changing:

xlsWorksheet.PageSetup.PrintArea="DisbursementsPrintArea1"
to
xlsWorksheet.PageSetup.PrintArea _
= xlsworksheet.range("DisbursementsPrintArea1").address

would work.

 

[Guest]

I had the same problem. It worked fine on my computer, but when I ran it from a user's computer I got the same error as you. Selecting the range first before setting the print area seems to have resolved it

I'm using Office 2000 on my computer, my user has Office 97

Hope this helps


----- M wrote: ----

I am using a form in Access 97 to open an Excel template
containing various charts. There are several worksheets
with one chart on each sheet. I have code that executes
based on checkbox selections that in turn prints the
selected charts. Here is the code

If MyForm!chkPrintChart1.Value = True The
Set xlsWorksheet = xlsWorkbooks.Worksheets(1
xlsWorksheet.PrintOu
Set xlsWorksheet = Nothin
End I

This code works fine until the user changes the
destination printer. I want to reset the PrintArea to a
named range whenever the printer is changed. So far I have
used this line

xlsWorksheet.PageSetup.PrintArea="DisbursementsPrintArea1

inserted before the 'xlsWorksheet.PrintOut' line, but I am
getting a RunTime error 1004 'Unable to set PrintArea
property of the PageSetup class'.

So I tried this

Set xlsRange=xlsWorksheet.Name
("DisbursementsPrintArea1").RefersToRang

If MyForm!chkPrintChart1.Value = True The
Set xlsWorksheet = xlsWorkbooks.Worksheets(1
Set xlsRange=xlsWorksheet.Name
("DisbursementsPrintArea1").RefersToRang
xlsRange.Selec
xlsRange.PrintOu
Set xlsRange = Nothin
Set xlsWorksheet = Nothin
End I

But I now get RunTime error 1004 'Application defined or
Object-defined error

I have also tried selecting the ChartObject itself on the
selected Worksheet but this hasn't worked either

I know this can work --- How

Thank