Excel VBA 页脚图像
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9926690/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Footer Image
提问by roshanK
Is there a way to use Excel-VBA code in order to make a picture object in a sheet, so as to insert it as a footer image. I have tried to do so by creating a chart object and pasting it in a picture-format, and then exporting the chart to an image file and setting the image as the footer. Is there a better way to insert a picture object as a footer image, and if so, how do I do it?
有没有办法使用 Excel-VBA 代码在工作表中制作图片对象，以便将其作为页脚图像插入。我尝试通过创建图表对象并将其粘贴为图片格式，然后将图表导出到图像文件并将该图像设置为页脚来做到这一点。有没有更好的方法将图片对象作为页脚图像插入，如果是这样，我该怎么做？
回答by Tony Dallimore
I started the macro recorder. I clicked Page Setupthen Header/Footerthen Custom Footer. I clicked the centre section and then Format Picture(button with image of sun over mountains). I browsed for an image and clicked Insert. "&[Picture]" appeared in the centre section. I clicked OKtwice. I switched the macro recorder off.
我启动了宏记录器。我点击Page Setup然后Header/Footer再Custom Footer。我单击了中间部分，然后单击了Format Picture（带有山上太阳图像的按钮）。我浏览了一张图片并点击了Insert。“&[图片]”出现在中间部分。我点了OK两次。我关掉了宏记录器。
I printed the page and the selected image appeared at the bottom.
我打印了页面，所选图像出现在底部。
The important code saved by the macro recorder was:
宏记录器保存的重要代码是：
ActiveSheet.PageSetup.CenterFooterPicture.Filename = _
"C:\Users\Public\Pictures\Sample Pictures\Desert Landscape.jpg"
Replace "C:\Users\Public\Pictures\Sample Pictures\Desert Landscape.jpg"with filename of your choice.
替换"C:\Users\Public\Pictures\Sample Pictures\Desert Landscape.jpg"为您选择的文件名。
The macro recorder is usually the easiest way of discovering statements like this.
宏记录器通常是发现此类语句的最简单方法。
回答by Kevin Whalen
For anybody viewing this in the future, I'll share my code to copy a range and save it as a file on your computer, which can then be added to the footer. You can eliminate whatever bits you don't want =)
对于将来查看此内容的任何人，我将分享我的代码以复制范围并将其另存为计算机上的文件，然后可以将其添加到页脚。你可以消除任何你不想要的位 =)
Dim objPic As Shape
Dim objChart As Chart
Dim strTimeStamp As String
Dim strFileDest As String
20 Sheets(2).Activate
30 Sheets(2).Columns("R:T").AutoFit
40 Sheets(2).Rows("17:21").AutoFit
50 ActiveWindow.DisplayGridlines = False
60 Call Sheets(2).Range("S17", "U21").CopyPicture(xlScreen, xlPicture)
70 ActiveWindow.DisplayGridlines = True
80 Sheets(2).Shapes.AddChart
90 Sheets(2).Activate
100 Sheets(2).Shapes.Item(1).Select
110 Set objChart = ActiveChart
120 ActiveChart.Parent.Name = "FooterChart"
' For some reason, Excel occasionally tries to make an actual chart out of these strings.
' It's just a nonsensical chart that messes the footer up but I'm having trouble duplicating the issue and figuring out what causes it.
' This should always work. Don't use .Clear, it crashes.
130 ActiveChart.ChartArea.ClearContents
140 objChart.Paste
150 Selection.Name = "FooterImage"
160 ActiveSheet.ChartObjects("FooterChart").Activate
170 Sheets(2).Shapes.Item(1).Line.Visible = msoFalse
180 Sheets(2).Shapes.Item(1).Height = Range("S17", "U21").Height
190 Sheets(2).Shapes.Item(1).Width = Range("S17", "U21").Width
200 ActiveChart.Shapes.Range(Array("FooterImage")).Height = Range("S17", "U21").Height
210 ActiveChart.Shapes.Range(Array("FooterImage")).Width = Range("S17", "U21").Width
220 Sheets(2).Shapes.Item(1).Height = Sheets(2).Shapes.Item(1).Height * 1.25
230 Sheets(2).Shapes.Item(1).Width = Sheets(2).Shapes.Item(1).Width * 1.25
240 ActiveChart.Shapes.Range(Array("FooterImage")).Height = ActiveChart.Shapes.Range(Array("FooterImage")).Height * 1.2
250 ActiveChart.Shapes.Range(Array("FooterImage")).Width = ActiveChart.Shapes.Range(Array("FooterImage")).Width * 1.2
260 strTimeStamp = CStr(Format(Now(), "yyyymmddHhNnSs"))
270 strFileDest = "D:\Temp" & strTimeStamp & ".jpg"
280 objChart.Export strFileDest
290 InsertPicture strFileDest
300 If Len(Dir$(strFileDest)) > 0 Then
310 Kill strFileDest
320 End If
330 Sheets(2).Shapes.Item(1).Delete
回答by zartag
Try this:
尝试这个：
Dim ws as Worksheet
Set ws = Worksheets("YourWorksheetName")
With ws.PageSetup
.CenterFooterPicture = "&G" 'Specifies that you want an image in your footer
.CenterFooterPicture.Filename = "C:\Pictures\MyFooterImage.jpg" 'specifies the image file you want to use
End With
The code generated by the macro recorder will get you part of the way there, but as is often the case, it doesn't provide the whole or most appropriate solution. It also sounds like you are trying to insert an image generated by Excel (such as a chart) into the footer? if that's the case, I believe you will have to same the object as an image and then reference that image file.
宏记录器生成的代码将帮助您完成部分任务，但通常情况下，它不会提供完整或最合适的解决方案。听起来您还想将 Excel 生成的图像（例如图表）插入页脚？如果是这种情况，我相信您必须将对象与图像相同，然后引用该图像文件。
