VBA error on PageSteup.PrintArea when using Excel 2007, works fine in Excel 2003

0
votes

I've inherited responsibility for a web app that uses Access for the database and Excel for the reporting (lucky me, I know). Everything has been working fine with Office 2003, however the company has begun pushing out Office 2007 which is now causing issues. So far it looks like Excel 2007 is having issues with one of the reports that is generated.

The report is generated via VBA code in the Access database, the offending line is this:

sht.PageSetup.PrintArea = strPrintArea

strPrintArea in this case had a value of "$C$2:$G$57", which looks valid to me. sht is an Excel Worksheet passed into this function.

However that line will fail withe the following error:

Run-time error '-2147352560(80020010)': 
Method 'PrintArea' of object 'PageSetup' failed

I'm stumped as to why the same line of code will work fine with Excel 2003 and fail with Excel 2007. I found Microsoft issued a hotfix around this issue for Excel 2010 (http://support.microsoft.com/kb/2553436) but I haven't found anything similar for Excel 2007.

I'm at a bit of a loss as to where to go next on this. Any help is much appreciated!

Not sure how helpful it'll be but here's the full function where the error is happening:

Sub SetPrintProperty(ByRef sht As Excel.Worksheet, ByRef strPrintArea As String, ByRef douLeftMargin As Double, ByRef douRightMargin As Double, _
                        ByRef douTopMargin As Double, ByRef douBottomMargin As Double, _
                        ByRef douHeaderMargin As Double, ByRef douFooterMargin As Double)
'*******************************************************************************************************************
'set the print area for a worksheet
'Arguments:
'   sht: the spreadsheet needed to set print area
'   strPrintArea: the address of the print area on a spreadsheet
'   douLeftMargin: left margin
'   douRightMargin: right margin
'   douTopMargin: top margin
'   douBottomMargin: bottom margin
'   douHeaderMargin: header margin
'   douFooterMargin: footer margin
'********************************************************************************************************************

    sht.PageSetup.PrintArea = strPrintArea
    With sht.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With

    With sht.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Excel.Application.InchesToPoints(douLeftMargin)
        .RightMargin = Excel.Application.InchesToPoints(douRightMargin)
        .TopMargin = Excel.Application.InchesToPoints(douTopMargin)
        .BottomMargin = Excel.Application.InchesToPoints(douBottomMargin)
        .HeaderMargin = Excel.Application.InchesToPoints(douHeaderMargin)
        .FooterMargin = Excel.Application.InchesToPoints(douFooterMargin)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
'        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        '.PaperSize = xlPaperLetter
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With

End Sub
2
excelms-accessvba
Where from you run this code? check if you don't mix up references to Access and Excel (do you have set References to Excel properly in Access). I think somewhere there is your problem... – Kazimierz Jawor
This code is executed from a VBA macro that is called via ActiveX in the web app. I'm pretty new to VBA, how would I go about checking the references? – Travis
Try changing the printer and having another go. I've had similar problems and changed the printer to a 'software' printer (i.e. Microsoft Office Document Image Writer), made all the page setup changes and then changed the printer back to a real printer. Seemed to fix things. Besides which, it is a lot faster than communicating with the printer driver directly. – steveo40
Just tried changing the default printer from the one in the office to Microsoft's software XPS one. No dice. I also tried setting it in code before assigning to PageSetup.PrintArea and it's still throwing the same error: For Each prt In Application.Printers If (prt.DeviceName = "Microsoft XPS Document Writer") Then Set Application.Printer = prt End If Next prt sht.PageSetup.PrintArea = strPrintArea – Travis

2 Answers

2
votes

So in the hopes that this will somehow help someone down the road who is faced with this same issue and wants to save themselves the headache of troubleshooting it, here was my eventual solution.

I'll preface this by saying I think that this is a bug in Excel 2007's VBA since the solution doesn't make any sense to me.

Anyways, while trying to figure out exactly what was causing the error, I tried testing hard coded values for sht.PageSetup.PrintArea. At first I did something basic like:

sht.PageSetup.PrintArea = "A1:A3"

Much to my surprise, it worked! Ok I thought, what if I put in the value that I was originally passing into the function via strPrintArea?

sht.PageSetup.PrintArea = "$C$2:$G$57"

Again much to my surprise it worked! Ok so at this point I was confused, how could assigning strPrintArea cause the error, but "$C$2:$G$57" work fine? In the end I don't have an answer, but suspecting that Excel somehow wanted a "different" type of string to be assigned, this is what I ended up going with to try and see if calling a function that would return my string value would work:

sht.PageSetup.PrintArea = Replace(strPrintArea, "-", "")

Since the value being passed in is always in the excel range format like "$C$2:$G$57" this essentially does nothing to the value (there is no "-" to replace). I have no clue why this fixes the error, but I sure am happy that it does!

0
votes

This is for listobjects (excel tables):

Dim ws As Worksheet, LastRow as Integer
Set ws = Sheets("Sheet1")
With ws.ListObjects("My_Table")
  LastRow = .ListRows(.ListRows.Count).Range.End(xlUp).Row - 1
  'If LastRow = 1 Then LastRow = .ListRows(.ListRows.Count).Range.Offset(1, 0).End(xlUp).Row
  ws.PageSetup.PrintArea = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, 12)).Address
End With

In my case this alternative generates an error:

ws.PageSetup.PrintArea = ws.Range("My_Table[#All]").Address

Update: i'm now suspecting that my issue with conflict names in print areas is related with working in the same file in two different pcs: one with Windows 7 and another with Winows XP in Spanish. I seeing that with the spanish XP installation of Office 2007 print area becomes 'Área_de_impresión', perhaps this generates some conflict with the 'Print_Area' named range.