VBA Print Preview works on PC, not mac, ExportasFixedFormat gives no love...

[Jotekman]

So, friend called me for some help, but I'll admit, I'm stumped.

He/his helper wrote some code which quite elegantly gets what is needed to print out the right things, dynamically, from a 70+ tab document. And when running the following macro, it works beautifully on PC. However. On Mac, it first wouldn't run without a printer installed. And when installing a pdf printer, it runs like it does on PC. BUT. The client he is sending it to isn't that computer savvy, and expecting the guy to install a PDF printer, in MacOS, on the many computers in his office isn't really feasible, especially since Adobe PDF doesn't install a printer anymore.

I tried to fix it to work using ExportasFixedFormat instead of PrintPreview (with the Type and everything else, but no dice, mostly 438 or 1004 errors.

The idea is that they want to create a succinct document to be able to make a pdf out of. I would be happy with a save as situation as well, but I cant even get it to do that.

As I understand it, its a rather HUGE database of medical codes and data, with multiple summary sheets collated from raw data sheets. They then only take the sheets which have data, and only pull the information from lines that don't have a 0 out character. Their idea was to be able to print to any source, including PDF, by opening the print preview dialogue, which totally works in Windows (which is what I am on, using Teamviewer to test the Mac tries. They would, however be just as happy at least for the mac version, for it to only go out to pdf, OR to be outputted to another created sheet within the workbook, and print/make pdf manually from there.
Anyone have any ideas? Surely appreciated!

Sub PrintPreviewPC() ' 'this function selects '"1&2 Cover Page", '"3 Executive Summary Page" and 'non blank provider summary pages 'and gives print preview command ' 'variable declaration Dim ws As Worksheet Dim c As Long, last_row As Integer Dim sheetarray() As String Dim print_range As String 'looping through all the worksheets in the workbook For Each ws In Worksheets        'accessing cover page with name    If ws.Name = "1&2. CoverPages" Then        ReDim Preserve sheetarray(c)        'appending the cover page in sheetarray()        sheetarray(c) = ws.Name        'array index increment        c = c + 1                'Setting dynamic Print Area        last_row = ws.Cells(1, 2).Value        print_range = "C3:P" & last_row        ws.PageSetup.PrintArea = Range(print_range).Address            'accessing executive summary page with name    ElseIf ws.Name = "3. ExecutiveSummary" Then        ReDim Preserve sheetarray(c)        'appending the executive summary page in sheetarray()        sheetarray(c) = ws.Name        'array index increment        c = c + 1                'Setting dynamic Print Area        last_row = ws.Cells(1, 2).Value        print_range = "A6:L" & last_row        ws.PageSetup.PrintArea = Range(print_range).Address    'accessing Provider Summary Pages with name    ElseIf ws.Name Like "Prov Sum. *" Then        'checking if the page contains data or not, cell "I24" is not 0        If ws.Cells(24, 9).Value <> 0 Then            ReDim Preserve sheetarray(c)            'append the provider summary sheet in sheetarray()            sheetarray(c) = ws.Name            'array index increment            c = c + 1            'Setting dynamic Print Area            last_row = ws.Cells(5, 3).Value            print_range = "D6:Y" & last_row            ws.PageSetup.PrintArea = Range(print_range).Address        End If    End If     Next 'Activate "PDF Setting" sheet Worksheets("PDF Settings").Activate 'Print Preview: all the sheets in sheetarray() ThisWorkbook.Worksheets(sheetarray()).PrintPreview End Sub

 

[Jotekman]

Okay, so update, now when adding a printer, it starts to print automatically physically, rather than giving any sort of print preview... Any thoughts?
This also happens with the Printout preview:=true method

 

[Jotekman]

After relooking, I realize NO ONE would answer that crappy post with the code like that.... :D Sorry, my mistake

Sub PrintPreviewPC()
'
'this function selects
'"1&2 Cover Page",
'"3 Executive Summary Page" and
'non blank provider summary pages
'and gives print preview command
'

'variable declaration
Dim ws As Worksheet
Dim c As Long, last_row As Integer
Dim sheetarray() As String
Dim print_range As String

'looping through all the worksheets in the workbook
For Each ws In Worksheets
    
    'accessing cover page with name
    If ws.Name = "1&2. CoverPages" Then
        ReDim Preserve sheetarray(c)
        'appending the cover page in sheetarray()
        sheetarray(c) = ws.Name
        'array index increment
        c = c + 1
        
        'Setting dynamic Print Area
        last_row = ws.Cells(1, 2).Value
        print_range = "C3:P" & last_row
        ws.PageSetup.PrintArea = Range(print_range).Address
        
    'accessing executive summary page with name
    ElseIf ws.Name = "3. ExecutiveSummary" Then
        ReDim Preserve sheetarray(c)
        'appending the executive summary page in sheetarray()
        sheetarray(c) = ws.Name
        'array index increment
        c = c + 1
        
        'Setting dynamic Print Area
        last_row = ws.Cells(1, 2).Value
        print_range = "A6:L" & last_row
        ws.PageSetup.PrintArea = Range(print_range).Address

    'accessing Provider Summary Pages with name
    ElseIf ws.Name Like "Prov Sum. *" Then
        'checking if the page contains data or not, cell "I24" is not 0
        If ws.Cells(24, 9).Value <> 0 Then
            ReDim Preserve sheetarray(c)
            'append the provider summary sheet in sheetarray()
            sheetarray(c) = ws.Name
            'array index increment
            c = c + 1

            'Setting dynamic Print Area
            last_row = ws.Cells(5, 3).Value
            print_range = "D6:Y" & last_row
            ws.PageSetup.PrintArea = Range(print_range).Address
        End If
    End If
    
Next

'Activate "PDF Setting" sheet
Worksheets("PDF Settings").Activate

'Print Preview: all the sheets in sheetarray()
ThisWorkbook.Worksheets(sheetarray()).PrintPreview

End Sub

 

[Jim Gordon]

Hi

A much simpler code example is:

Sub test()
ActiveSheet.PrintPreview
End Sub

I can confirm that PrintPreview prints the document instead of displaying a preview window.

You can notify Microsoft about this problem using the Menu Bar in Excel. Choose Help > Feedback > I Don't Like Something and explain the issue. I'll do the same.

A work-around might be to change the view to Page Break Preview