vba 将图片和数据从一个excel文件复制到另一个excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10974793/
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
Copy picture and data from one excel file to another excel file
提问by Rupesh
I have an excel file which contains picture in first five rows and then the data starts. My job is to copy the file as it is into another excel file. Firstly, I open the file and copy the used range. Next I paste the contents into the another excel file.
我有一个 excel 文件，其中前五行包含图片，然后数据开始。我的工作是将文件原样复制到另一个 excel 文件中。首先，我打开文件并复制使用的范围。接下来我将内容粘贴到另一个 excel 文件中。
lobjCurrentWorkSheet.UsedRange.Copy()
lobjTargetExcelWorkSheet.PasteSpecial(XlPasteType.xlPasteAll)
By doing the above I could copy only the data but not the picture? How would I copy the file as it is to another one?
通过执行上述操作，我只能复制数据而不能复制图片？我如何将文件原样复制到另一个文件？
If I use SaveAsoption and if the source file is password encrypted then my target file will also be password encrypted which I don't want.
如果我使用SaveAs选项并且源文件是密码加密的，那么我的目标文件也将是我不想要的密码加密。
回答by Siddharth Rout
I would never recommend using UsedRange
我永远不会推荐使用 UsedRange
Reason:
原因：
Your data might be from Cell A1 to B4 and the image is in say D1. The UsedRangein this case will always be A1:B4and not A1:F6
您的数据可能是从单元格 A1 到 B4，图像在 D1 中。在UsedRange这种情况下总是会A1:B4不A1:F6
If you want your picture to be copied then either find out the "Actual Range" of the worksheet or specify the range
如果要复制图片，请找出工作表的“实际范围”或指定范围
lobjCurrentWorkSheet.Range("A1:F6").Copy()
lobjTargetExcelWorkSheet.Paste()
EDIT: I was trying to lay my hands on VB.Net so that I could give you an exact sample code. I finally did :)
编辑：我试图将我的手放在 VB.Net 上，以便我可以给你一个确切的示例代码。我终于做到了:)
See this (This will copy the actual range without you having to specify it.)
看到这个（这将复制实际范围，而不必指定它。）
Dim shp As Excel.Shape
Dim lCol As Integer = 0
'~~> Loop through all shapes and find the last col of the shape
For Each shp In lobjCurrentWorkSheet.Shapes
If shp.BottomRightCell.Column > lCol Then _
lCol = shp.BottomRightCell.Column
Next
With lobjCurrentWorkSheet
'~~> Find actual last Row
Dim LastRow As Integer = .Cells.Find(What:="*", _
After:=.Range("A1"), _
LookAt:=Excel.XlLookAt.xlPart, _
LookIn:=Excel.XlFindLookIn.xlFormulas, _
SearchOrder:=Excel.XlSearchOrder.xlByRows, _
SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
MatchCase:=False).Row
'~~> Find actual last column
Dim LastColumn As Integer = .Cells.Find(What:="*", _
After:=.Range("A1"), _
LookAt:=Excel.XlLookAt.xlPart, _
LookIn:=Excel.XlFindLookIn.xlFormulas, _
SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
MatchCase:=False).Column
'~~> Check if we have the correct last columnm
If LastColumn < lCol Then LastColumn = lCol
.Range("A1:" & Split(.Cells(, LastColumn).Address,
"$")(1) & LastRow).Copy()
End With
'~~> Copies to the current active cell in lobjTargetExcelWorkSheet
lobjTargetExcelWorkSheet.Paste()
