使用VBScript代码更改Excel中的边距 [英] Change margins in Excel using VBScript Code

问题描述

我有一个脚本，目前正在使用Excel文档并将其转换成一个报表（仍然是Excel格式），分为两部分：UPLOAD和PRINTOUT。

上传表格显示了从原始Excel文档中获取的所有信息，并将其过滤到报表的正确列/行。打印输出工作表采用上传工作表，进一步格式化以准备实际打印输出。

问题是，我正在尝试应用Orientation和Margin PageSetup参数，但它只是忽略它们。不管我做什么，页面格式都不会改变。

主要是，我需要改变这些参数（边距为英寸）：

• 景观方向


• 左边距（0.36）


• 右边距（0.25）


• （0.5）的顶部和底部边距


• （0.25）的页眉和页脚边距

这里是代码，我认为有一个问题：

 '开始打印输出页
 
设置objLastSheet = objWorkbook.Worksheets（上传）
设置objWorksheet = objWorkbook.Worksheets（上传）
 objWorksheet.Copy，objLastSheet 
 
设置objWorksheet2 = objWorkbook.Worksheets（Upload（2））
 objWorksheet2.Name =打印输出
 
设置objRange = objWorksheet2.Pagesetup 
设置objRange.PageSetup.Orientation = xlLandscape 
设置objRange.PageSetup.LeftMargin = Application.InchesToPoints（0.36）
设置objRange.PageSetup.RightMargin = Application.InchesToPoints（0.25）
设置objRange.PageSetup.TopMargin = Application.InchesToPoints（0.5）
设置objRange.PageSetup.BottomMargin = Application.InchesToPoints（0.5）
设置objRange.PageSetup.HeaderMargin = Application.InchesToPoints（0.25）
设置objRange.PageSetup.FooterMargin = Application.InchesToPoints（0.25）
  

我没有写上传的其余代码，我只是被要求使利润率工作，所以我一直在尝试添加任何需要的现有代码。不知道它是否是语法，或者我只是错过了一些关键的东西。谢谢你提前！

编辑：当前代码。方向是横向，现在适合一页宽，但是边距参数仍然不采取（当我去打印预览时，它仍然说正常边距）。

  Const xlLandscape = 2 
 
带objWorksheet2.Pagesetup 
 .Orientation = xlLandscape 
 .Zoom = False 
 .FitToPagesWide = 1 
 .FitToPagesTall = False 
 .LeftMargin = Application.InchesToPoints（0.5）
 .RightMargin = Application.InchesToPoints（0.5）
 .TopMargin = Application.InchesToPoints（0.5）
 .BottomMargin = Application.InchesToPoints（0.5）
 .HeaderMargin = Application.InchesToPoints（0.5）
 .FooterMargin = Application.InchesToPoints（0.5） 
结束
  

FINAL EDIT

由于某些原因，我的Application.InchesToPoints（）函数不起作用。也许我没有一个图书馆的东西，但是，当我手动转换价值并输入他们的积分值，它的工作。

最终解决方案：

  Const xlLandscape = 2 
 
带有objWorksheet2.Pagesetup 
。方向= xlLandscape 
 .Zoom = False 
 .FitToPagesWide = 1 
 .FitToPagesTall = False 
 .LeftMargin = 26 
 .RightMargin = 18 
 .TopMargin = 36 
 .BottomMargin = 36 
 .HeaderMargin = 18 
 .FooterMargin = 18 
结束
  

解决方案

ObjRange = objWorksheet2.Pagesetup，所以你应该使用：

 code>设置objRange.Orientation = xlLandscape 
  

等。

或者：

 使用objWorksheet2.Pagesetup 
 .Orientation = xlLandscape 
 .LeftMargin = Application.InchesToPoints（0.36）
 .RightMargin = Application.Inches ToPoints（0.25）
 .TopMargin = Application.InchesToPoints（0.5）
 .BottomMargin = Application.InchesToPoints（0.5）
 .HeaderMargin = Application.InchesToPoints（0.25）
 .FooterMargin = Application.InchesToPoints（0.25）
结束
  

I have a script that currently takes in an Excel document and turns it into a report (still in the Excel format), broken up in to two sheets: UPLOAD and PRINTOUT.

The Upload sheet shows all of the information that was taken in from the original Excel doc and filters it into the right columns/rows for the report. The Printout sheet takes the Upload sheet and formats it further to be ready for an actual printout.

The problem is, I'm trying to apply Orientation and Margin PageSetup parameters, but it is just ignoring them. No matter what I do, the page format won't change.

Mainly, I need these parameters changed (margins are in inches):

• Landscape Orientation
• Left Margin (0.36)
• Right Margin (0.25)
• Top and Bottom Margins at (0.5)
• Header and Footer Margins at (0.25)

Here's the Code where I think there's an issue:

'Start printout sheet

Set objLastSheet = objWorkbook.Worksheets("Upload")
Set objWorksheet = objWorkbook.Worksheets("Upload")
objWorksheet.Copy, objLastSheet

Set objWorksheet2 = objWorkbook.Worksheets("Upload (2)")
objWorksheet2.Name = "Printout"

Set objRange = objWorksheet2.Pagesetup
Set objRange.PageSetup.Orientation= xlLandscape
Set objRange.PageSetup.LeftMargin = Application.InchesToPoints(0.36) 
Set objRange.PageSetup.RightMargin = Application.InchesToPoints(0.25)
Set objRange.PageSetup.TopMargin = Application.InchesToPoints(0.5)  
Set objRange.PageSetup.BottomMargin = Application.InchesToPoints(0.5) 
Set objRange.PageSetup.HeaderMargin = Application.InchesToPoints(0.25) 
Set objRange.PageSetup.FooterMargin = Application.InchesToPoints(0.25) 

I didn't write the rest of the code for the upload, I was just asked to make the margins work so I've been trying to add whatever need be to the existing code. Not sure if it's syntax or if I'm just missing something crucial. Thank you in advance!

EDIT: Current code. Orientation is Landscape and is fitting to one page wide now, but the Margin parameters are still not taking (When I go to print preview, it still says "Normal Margins").

Const xlLandscape = 2

With objWorksheet2.Pagesetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.LeftMargin = Application.InchesToPoints(0.5) 
.RightMargin = Application.InchesToPoints(0.5) 
.TopMargin = Application.InchesToPoints(0.5) 
.BottomMargin = Application.InchesToPoints(0.5) 
.HeaderMargin = Application.InchesToPoints(0.5) 
.FooterMargin = Application.InchesToPoints(0.5)
End With

FINAL EDIT

For some reason, my Application.InchesToPoints() function wasn't working. Maybe I didn't have a library included or something, but nonetheless, when I manually converted the values and entered their point values, it worked.

Final Solution:

Const xlLandscape = 2

With objWorksheet2.Pagesetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.LeftMargin =  26
.RightMargin =  18
.TopMargin =  36
.BottomMargin = 36
.HeaderMargin = 18
.FooterMargin = 18
End With

解决方案

ObjRange = objWorksheet2.Pagesetup so you should be using:

Set objRange.Orientation= xlLandscape

etc.

or rather:

 With objWorksheet2.Pagesetup
   .Orientation= xlLandscape
   .LeftMargin = Application.InchesToPoints(0.36) 
   .RightMargin = Application.InchesToPoints(0.25)
   .TopMargin = Application.InchesToPoints(0.5)  
   .BottomMargin = Application.InchesToPoints(0.5) 
   .HeaderMargin = Application.InchesToPoints(0.25) 
   .FooterMargin = Application.InchesToPoints(0.25) 
End With

这篇关于使用VBScript代码更改Excel中的边距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！