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 文件......因此，我改为使用 copy & 为每个学生导出一个 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屋！