如何打开新的工作簿并使用VBA添加图像？ [英] How to open a new workbook and add images with VBA?

问题描述

我正在尝试为Excel 2007获取一个宏，打开一个包含一堆图像的文件夹。然后创建一个新的工作簿并将图像嵌入到其中。

I'm trying to get a macro for Excel 2007to open a folder with a bunch of images in them. Then Create a new workbook and embed the images into it.

如果我注释掉了 Sheet.Shapes.AddPicture FileName = = F ，linktofile：= msoFalse，savewithdocument：= msoCTrue，Left：= cell.Left + 5，Top：= cell.Top + 5，Width：= 560，Height：= 310 如果我取消注释该行我得到运行时错误434'：需要对象

Everything works if I comment out the line Sheet.Shapes.AddPicture FileName:=F, linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=cell.Left + 5, Top:=cell.Top + 5, Width:=560, Height:=310 If I uncomment that line I get "Run-time error '434': Object required"

我检查了Sheet.Shapes是否返回一个Shapes对象，但是Shapes对象是空。当我在一个在宏之外打开的工作簿上尝试Sheet.Shapes，AddPicture时，它会添加图像。我也检查了Sheet.Shapes.AddShape与在宏中打开的工作簿一起工作，这样做。

I've check that Sheet.Shapes is returning a Shapes object, it is but the Shapes object is empty. When I try Sheet.Shapes,AddPicture on a workbook that is opened outside of the macro, it adds the images. I've also checked that Sheet.Shapes.AddShape works with the workbook opened in the macro, it does.

在这一点上，我失去了什么问题可能是。有人有这样的经验吗？我应该用不同的方法吗？感谢提前获得任何帮助或指导。

At this point, I'm at a lose for what the issue might be. Does anyone have any experience with this sort of thing? Should I be using a different method? Thanks in advance for any help or guidance.

Sub Macro1()
Dim ImagePath, Flist
ImagePath = GetFolder()
If ImagePath = "" Then Exit Sub
Flist = FileList(ImagePath)
Name = "C:\target.xlsm"
Set Book = Workbooks.Add
Set Sheet = Book.Sheets(1)
For i = 1 To 5
    cell = "C" + CStr(i)
    F = ImagePath + "\" + Flist(i - 1)
        Sheet.Shapes.AddPicture FileName:=F, linktofile:=msoFalse, _
            savewithdocument:=msoCTrue, Left:=cell.Left + 5, Top:=cell.Top + 5, Width:=560, Height:=310
    Next
Book.SaveAs FileName:=Name, FileFormat:=52
Book.Close
End Sub

 Function FileList(ByVal fldr As String) As Variant
'Lists all the files in the current directory
'Found at http://www.ozgrid.com/forum/showthread.php?t=71409
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & "*.png")
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
    Loop
    FileList = Split(sTemp, "|")
End Function

Function GetFolder() As String
Folder:
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "New Screenshot Folder"
    .Show
    num = .SelectedItems.Count
    If .SelectedItems.Count = 0 Then
        GetFolder = ""
    Else: GetFolder = .SelectedItems(1)
    End If
End With
End Function

推荐答案

您无法通过创建字符串C1定义单元格，只是地址。你这样做， cell 是一个字符串，一个字符串没有任何属性。你想要的是一个范围对象，所以使用

You can't define a cell by creating the string "C1", that's just the address. The way you did it, cell is a string and a string doesn't have any properties. What you want is a range object so either use

Dim cell As Range
Set cell = sheet.Range("C" & i)

或

Dim cell As Range
Set cell = sheet.Cells(i, 3)

您应始终 Dim 所有变量，使用您的模块顶部的 Option Explicit 你不要忘记它;）

You should always Dim all variables, use Option Explicit on top of your module so you don't forget it ;)

这通常会防止错误。当然，您应该使用正确的类型 Dim ，即 Dim FilePath As String 。

This will often prevent mistakes. Of course you should Dim them with the correct type, i.e. Dim FilePath As String.

这篇关于如何打开新的工作簿并使用VBA添加图像？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！