ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸s²Õ³]©wª©­±

[µo°Ý] ¸s²Õ³]©wª©­±

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-2-4 19:44 ½s¿è

Dear,
§Ú¦bWorkbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1")«ü©w¤@­Ó¤u§@ªí¦WºÙ
¥Î¨Ó³]©w³o­ÓÀɮתº©Ò¦³Sheet,¨Ïª©­±¤@­P
¦ý³]©w³£¤£¦¨¥\,½Ð°Ý¬O­þ­Ó»yªk¿ù»~?
  1. Sub ¸s²Õ³]©wª©­±()
  2. Dim Wb As Workbook, xActSheet As Worksheet, j As String, xi As String, x As Double, i As Long
  3. Dim k1 As String, k2 As String, k3 As String, k4 As String, k5 As String, k6 As String
  4.     Application.ScreenUpdating = False
  5.     Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
  6.     Application.Calculation = xlManual     '¤â°Ê­pºâ
  7.     Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VB").Range("P1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ

  8.     j = Workbooks("Macro.xlsm").Worksheets("VB").Range("P2").Value
  9.     k1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P3").Value
  10.     k2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P4").Value
  11.     k3 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P5").Value
  12.     k4 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P6").Value
  13.     k5 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P7").Value
  14.     k6 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P8").Value
  15.     x1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P9").Value
  16.     x2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P10").Value
  17.     x = Workbooks("Macro.xlsm").Worksheets("VB").Range("P11").Value
  18.    
  19.     With Wb.ActiveSheet.PageSetup
  20.         For i = 2 To ActiveWorkbook.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
  21.         ActiveWorkbook.Sheets(i).Activate

  22.          ActiveSheet.PageSetup.PrintArea = j
  23.         .TopMargin = Application.InchesToPoints(k1)   '¤WÃä¬É
  24.         .BottomMargin = Application.InchesToPoints(k2)  '¤UÃä¬É
  25.         .LeftMargin = Application.InchesToPoints(k3)  '¥ªÃä¬É
  26.         .RightMargin = Application.InchesToPoints(k4)  '¥kÃä¬É
  27.         .HeaderMargin = Application.InchesToPoints(k5) '­¶­º
  28.         .FooterMargin = Application.InchesToPoints(k6)  '­¶§À

  29.         .Zoom = x '¦C¦LÁY©ñ
  30.         .Orientation = xlLandscape   '¾î¦V¦C¦L
  31.         Next
  32.     End With
  33.         Application.ScreenUpdating = True   '¥´¶}«Ì¹õ§ó·s
  34.         Application.Calculation = xlAutomatic    '¦Û°Ê­pºâ
  35.    
  36. End Sub
½Æ»s¥N½X
¸s²Õª©­±A.rar (260.99 KB)

¦^´_ 1# PJChen
1.¦]¬°µ{¦¡¬O°õ¦æMacro_TªºSheet,©Ò¥H®w¦sªí¤£·|Åܧó,±NActiveWorkbook§ï¬°WB
2.ªþ¥ó¤ºµ{¦¡ªºWorkboos & sheets¦WºÙ»P¹ê»Ú¤£²Å,±NMacro.xlsm§ï¬°Macro_T.xlsm,VB§ï¬°VBA
¸Õ¸Õ¬Ý¬O§_²Å¦X»Ý¨D

Sub ¸s²Õ³]©wª©­±()
Dim Wb As Workbook, xActSheet As Worksheet, j As String, xi As String, x As Double, i As Long
Dim k1 As String, k2 As String, k3 As String, k4 As String, k5 As String, k6 As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
    Application.Calculation = xlManual     '¤â°Ê­pºâ
    Set Wb = Workbooks(Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ

    j = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P2").Value
    k1 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P3").Value
    k2 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P4").Value
    k3 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P5").Value
    k4 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P6").Value
    k5 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P7").Value
    k6 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P8").Value
    x1 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P9").Value
    x2 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P10").Value
    x = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P11").Value
   
        For i = 2 To Wb.Sheets.Count   '¦b¤u§@ªí¤¤´`Àô
        Wb.Sheets(i).Activate   

      With Wb.ActiveSheet.PageSetup
        .PrintArea = j
        .TopMargin = Application.InchesToPoints(k1)   '¤WÃä¬É
        .BottomMargin = Application.InchesToPoints(k2)  '¤UÃä¬É
        .LeftMargin = Application.InchesToPoints(k3)  '¥ªÃä¬É
        .RightMargin = Application.InchesToPoints(k4)  '¥kÃä¬É
        .HeaderMargin = Application.InchesToPoints(k5) '­¶­º
        .FooterMargin = Application.InchesToPoints(k6)  '­¶§À

        .Zoom = x '¦C¦LÁY©ñ
        .Orientation = xlLandscape   '¾î¦V¦C¦L
     End With
        Next
   ' End With
        Application.ScreenUpdating = True   '¥´¶}«Ì¹õ§ó·s
        Application.Calculation = xlAutomatic    '¦Û°Ê­pºâ
   
End Sub

TOP

¦^´_ 2# jcchiang

·PÁ±z,
´ú¸ÕOK¤F,
½Ð°Ý
1) ¥H¤U¥]°_¨Óªº³o­Ó­n«ç»ò°Ï¤À, For/Withªº¶¶§Ç?§Ú¤À¤£²M·¡....
2) §Ú¦³¥t¥~¤@­Ó°ÝÃDhttp://forum.twbts.com/viewthrea ... a=pageD1&page=2
Ãö©ó "§R°£¤j©ó¤ë©³¤éªº¤u§@ªí"¤@ª½§xÂZµÛ§Ú,¯à§_¤]À°¦£¬Ý¤U»yªk,¨º­Óµ{¦¡¥u¦³³o­Ó³¡¥÷¤£¦¨¥\,¨ä¥L³£¨S°ÝÃD
«ô°U¤F....
  1.         For i = 2 To Wb.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
  2.         Wb.Sheets(i).Activate
  3.             With Wb.ActiveSheet.PageSetup
  4. ¬Ù²¤....
  5.             End With
  6.                 Next
½Æ»s¥N½X

TOP

.TopMargin = Application.CentimetersToPoints(???)  
Ãä¬É¥H"¤½¤À"¬°³æ¦ì

TOP

¦^´_ 3# PJChen

°ÝÃD¤@:µ{¦¡±Æª©¨S°µ¦n,³y¦¨»~¸Ñ
For-Next¬O±q²Ä¤G­Ó¤u§@ªí¶}©l°õ¦æ,´`Àô¦ÜWb.Sheets.Count«á°±¤î
¨C¤@¦¸´`Àô¤¤°õ¦æWith-End With§@·~

For i = 2 To Wb.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
      Wb.Sheets(i).Activate
      With Wb.ActiveSheet.PageSetup
¬Ù²¤....
     End With
Next
°ÝÃD¤G:³o­Ó°ÝÃD¤@¶}©l¤£²M·¡¥Î³~,¤§«á¬Ý¦³¥ý¶i¦^ÂÐ,´N¨S¦b¬ã¨s¤F,«áÄò¦³®É¶¡¦A¬ã¨s¬Ý¬Ý

TOP

¦^´_ 4# ­ã´£³¡ªL

½Ð°Ý­ã¤j,

.TopMargin = Application.CentimetersToPoints(???)  
Ãä¬É¥H"¤½¤À"¬°³æ¦ì

©Ò¥H³o­Ó³¡¥÷¤£¯à«ü©w¦bÀx¦s®æ¤¤°õ¦æ ¬O¶Ü? ÁÙ¬O­n¤£¦Pªº»yªk?
    k1 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P3").Value
    k2 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P4").Value
    k3 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P5").Value
    k4 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P6").Value

TOP

¦^´_ 6# PJChen


¦P¼Ë¥i¥ÎÀx¦s®æ­È¬°Ãä¬É­È,
¥u¤£¹L±NÀx¦s®æ­Èµø¬°"¤½¤À", ¦Ó¤£¬O"­^¦T"

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-2-11 17:48 ½s¿è

¦^´_ 7# ­ã´£³¡ªL

­ã¤j,
©Ò¥H..
Àx¦s®æªº­È*2.53¥ª¥k,¬Oª©­±³]©wªº­È

¸s²Õª©­±_.rar (34.03 KB)

TOP

¦^´_ 8# PJChen

.TopMargin = Application.InchesToPoints(2)   Ãä¬É2­^¦T = 2*2.54 = 5.1¤½¤À
.TopMargin = Application.CentimetersToPoints(2)  Ãä¬É = 2¤½¤À

TOP

¦^´_ 9# ­ã´£³¡ªL

­ì¨Ó¥i¥H¥Î¤£¦Pªº³æ¦ì
·PÁ»¡©ú

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD