麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 群組設定版面 [打印本頁]

作者: PJChen 時間: 2020-2-4 19:39 標題: 群組設定版面

本帖最後由 PJChen 於 2020-2-4 19:44 編輯

Dear,
我在Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1")指定一個工作表名稱
用來設定這個檔案的所有Sheet,使版面一致
但設定都不成功,請問是哪個語法錯誤?

Sub 群組設定版面()
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 '在程序執行過程中使出現的警告框不顯示
Application.Calculation = xlManual '手動計算
Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VB").Range("P1").Value) '儲存格的值為檔案名稱
j = Workbooks("Macro.xlsm").Worksheets("VB").Range("P2").Value
k1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P3").Value
k2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P4").Value
k3 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P5").Value
k4 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P6").Value
k5 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P7").Value
k6 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P8").Value
x1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P9").Value
x2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P10").Value
x = Workbooks("Macro.xlsm").Worksheets("VB").Range("P11").Value
With Wb.ActiveSheet.PageSetup
For i = 2 To ActiveWorkbook.Sheets.Count '在工作表中循環
ActiveWorkbook.Sheets(i).Activate
ActiveSheet.PageSetup.PrintArea = j
.TopMargin = Application.InchesToPoints(k1) '上邊界
.BottomMargin = Application.InchesToPoints(k2) '下邊界
.LeftMargin = Application.InchesToPoints(k3) '左邊界
.RightMargin = Application.InchesToPoints(k4) '右邊界
.HeaderMargin = Application.InchesToPoints(k5) '頁首
.FooterMargin = Application.InchesToPoints(k6) '頁尾
.Zoom = x '列印縮放
.Orientation = xlLandscape '橫向列印
Next
End With
Application.ScreenUpdating = True '打開屏幕更新
Application.Calculation = xlAutomatic '自動計算
End Sub

複製代碼

[attach]31678[/attach]

作者: jcchiang 時間: 2020-2-5 13:28

回復 1# PJChen
1.因為程式是執行Macro_T的Sheet,所以庫存表不會變更,將ActiveWorkbook改為WB
2.附件內程式的Workboos & sheets名稱與實際不符,將Macro.xlsm改為Macro_T.xlsm,VB改為VBA
試試看是否符合需求

Sub 群組設定版面()
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 '在程序執行過程中使出現的警告框不顯示
Application.Calculation = xlManual    '手動計算
Set Wb = Workbooks(Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1").Value) '儲存格的值為檔案名稱

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 '在工作表中循環
      Wb.Sheets(i).Activate

   With Wb.ActiveSheet.PageSetup
      .PrintArea = j
      .TopMargin = Application.InchesToPoints(k1) '上邊界
      .BottomMargin = Application.InchesToPoints(k2)  '下邊界
      .LeftMargin = Application.InchesToPoints(k3)  '左邊界
      .RightMargin = Application.InchesToPoints(k4)  '右邊界
      .HeaderMargin = Application.InchesToPoints(k5) '頁首
      .FooterMargin = Application.InchesToPoints(k6)  '頁尾

      .Zoom = x '列印縮放
      .Orientation = xlLandscape '橫向列印
   End With
      Next
' End With
      Application.ScreenUpdating = True '打開屏幕更新
      Application.Calculation = xlAutomatic '自動計算

End Sub

作者: PJChen 時間: 2020-2-8 21:15

回復 2# jcchiang

感謝您,
測試OK了,
請問
1) 以下包起來的這個要怎麼區分, For/With的順序?我分不清楚....
2) 我有另外一個問題http://forum.twbts.com/viewthrea ... a=pageD1&page=2
關於 "刪除大於月底日的工作表"一直困擾著我,能否也幫忙看下語法,那個程式只有這個部份不成功,其他都沒問題
拜託了....

For i = 2 To Wb.Sheets.Count '在工作表中循環
Wb.Sheets(i).Activate
With Wb.ActiveSheet.PageSetup
省略....
End With
Next

複製代碼

作者: 准提部林 時間: 2020-2-9 11:53

.TopMargin = Application.CentimetersToPoints(???)
邊界以"公分"為單位

作者: jcchiang 時間: 2020-2-10 10:54

回復 3# PJChen

問題一:程式排版沒做好,造成誤解
For-Next是從第二個工作表開始執行,循環至Wb.Sheets.Count後停止
每一次循環中執行With-End With作業

For i = 2 To Wb.Sheets.Count '在工作表中循環
   Wb.Sheets(i).Activate
   With Wb.ActiveSheet.PageSetup
省略....
   End With
Next
問題二:這個問題一開始不清楚用途,之後看有先進回覆,就沒在研究了,後續有時間再研究看看

作者: PJChen 時間: 2020-2-10 16:24

回復 4# 准提部林

請問准大,

.TopMargin = Application.CentimetersToPoints(???)
邊界以"公分"為單位

所以這個部份不能指定在儲存格中執行是嗎? 還是要不同的語法?
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

作者: 准提部林 時間: 2020-2-11 12:50

回復 6# PJChen

同樣可用儲存格值為邊界值,
只不過將儲存格值視為"公分", 而不是"英吋"

作者: PJChen 時間: 2020-2-11 17:33

本帖最後由 PJChen 於 2020-2-11 17:48 編輯

回復 7# 准提部林

准大,
所以..
儲存格的值*2.53左右,是版面設定的值

作者: 准提部林 時間: 2020-2-12 13:38

回復 8# PJChen

.TopMargin = Application.InchesToPoints(2) 邊界2英吋 = 2*2.54 = 5.1公分
.TopMargin = Application.CentimetersToPoints(2) 邊界 = 2公分

作者: PJChen 時間: 2020-2-12 21:52

回復 9# 准提部林

原來可以用不同的單位
感謝說明

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)