excel到pdf会忽略打印区域并调用打印机 [英] excel to pdf ignores print area and calls printer

问题描述

这将遍历学生列表，但在打印区域上失败，该区域在导出行中进行了设置和编码-当每个学生只能打印一个页面时，它将为每个学生打印130页.将调用所有打印机，这将打开一个对话框(登录6的密码)，并停止宏-打印机是网络上的工作打印机，并不总是可用的. 有没有一种方法可以阻止打印机被呼叫? 并控制页面到打印区域?

This loops through a list of students, but fails on the print area, which is set and coded in the export line - it prints 130 pages for each student when it should only be one. All the printer gets called which opens a dialogue box (login 6 password) and that stops the macro - the printer is a workprinter on a network which is not always available. Is there a way to stop the printer getting called? And controlling the pages to the print area?

Option Explicit

Sub PdfExportMacro()
Dim rCell As Range, rRng As Range

'Student numbers in cells A7:A160
Set rRng = Worksheets("studentlist").Range("A7:A160") '<--| set your "students" range

With Worksheets("Feedback") '<--| reference "Feedback" worksheet
    For Each rCell In rRng '<--| loop through "students" range
    .Range("A1").Value = rCell.Value '<--| write current student number to cell A1 on Feedback sheet

       ' Export & save file as pdf using SNum as filename:
        .ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        "Macintosh HD:Users:Michael:Desktop:" & rCell.Value, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next rCell
End With

End Sub

推荐答案

所以我更改了曲目-excel vba似乎不满意使用打印机集来生成pdf文件，因为它是现状... 因此，我更改为使用副本&导出每个学生的excel文件.粘贴特殊值和格式.这是我所做的代码(很多其他答案都从这里偷走了！谢谢...)欢迎提出任何有关改进代码的意见-我认为这样做的范围很大！

so I changed track - excel vba does not seem to be happy producing pdf files with the printer set as it is... So, i changed to export an excel file per student using copy & paste special values and formats. Here is the code i did (lots stolen from other answers on here! thanks...) Any comments about improving the code are welcome - I think there is a lot of scope for that!!

    Option Explicit

Sub Exportmacro()
    Dim rCell As Range, rRng As Range 'define loop names
    Dim NewCaseFile As Workbook 'give a name to new work book for duplicate sheet
    Dim wks As Worksheet 'name of the copy of feedback
    Dim sPath As String
    sPath = MacScript("(path to desktop folder as string)")
'turn off screen
With Application
'        .ScreenUpdating = False  ‘only removed while testing
'        .EnableEvents = False
'        .Calculation = xlCalculationManual  ‘disabled for the moment
End With

    'Student numbers in cells A7:A160 WARNING SET TO 3 STUDENTS ONLY FOR TEST
    Set rRng = Worksheets("studentlist").Range("A7:A9")

    With Worksheets("Feedback") '<--| reference "Feedback" worksheet

        For Each rCell In rRng '<--| loop through "students" range
            .Range("A1").Value = rCell.Value '<--| write current student number to cell A1 on Feedback sheet

           'do copy ready for paste spec vals to destroy links & calculations
               ActiveSheet.Range("A2:W77").Copy

            'now open new workbook then pastespecial values and formats
             Set NewCaseFile = Workbooks.Add
             NewCaseFile.Sheets(1).Range("A1").PasteSpecial xlPasteValues
             NewCaseFile.Sheets(1).Range("A1").PasteSpecial xlPasteFormats

            'now save as xls with student number as filename Filename:=sPath & rCell.Value & ".xlsx"
             ActiveWorkbook.SaveAs Filename:=rCell.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

            'now close duplicate file
             ActiveWorkbook.Close False

        Next rCell   '<-- next student number
    End With         '<-- once all done
'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

这篇关于excel到pdf会忽略打印区域并调用打印机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！