因此,我正在编写这段代码,它应该循环遍历Excel中的几个表并将其粘贴到PowerPoint中。此过程工作正常。但是,在将表格粘贴为范围后,我试图更改形状的尺寸(在我的幻灯片中为#7)。然而,在这一点上,代码Set PPShape = PPSlide.Shapes(7)
并给我以下错误“对象'Shapes'的方法'Item'失败(见图)”
的数据
因此,它不会将刚刚复制到PowerPoint中的形状识别为形状。
但是,一旦我单击debug并再次运行代码而不更改任何一行代码,它就可以工作了。然后VBA检测相应载玻片上的形状#7,并将其尺寸更改为给定参数。我尝试在不同的子例程中编写选择形状和调整大小的过程,然后从原始子例程中调用此子例程。运气不好。而且,On Error似乎也没有克服这个错误。有人知道怎么解决吗?
Option Explicit
Sub DBible()
'Declaring all necessary PowerPoint variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PPShape As PowerPoint.Shape
Dim OriginFile As String 'File path to PowerPoint template
'Declaring all necessary Excek-l variables
Dim adminSh As Worksheet 'Sheet containing the data to be exported
Dim configRng As Range 'Selection of cells used to mark the exported ranges
Dim rng As Range 'Each individual cell within range
Dim vRange$ 'Cell address
Dim expRng As Range 'Range to be exported
'Dimension variables to resize and position exported shapes
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vSlide_No As Double 'Slide in which range will be pasted
'Create and open PowerPoint
Set PP = CreateObject("PowerPoint.Application")
OriginFile = "https://'CompanyName-my.sharepoint.com/personal/CompanyName/Documents/Documents/PLS%20DO%20NOT%20TOUCH%20-%20BRP%20ORIGIN.pptx?web=1"
PP.Presentations.Open (OriginFile)
PP.Visible = True
PP.WindowState = ppWindowMaximized
Set PPPres = PP.ActivePresentation
'Select sheet where data will be exported from and define the origin of all the ranges
Set adminSh = Sheets("PERF-LIQ-VEL-BETA")
Set configRng = adminSh.Range("rng_sheets")
'Loop that starts process of copy pasting the ranges from Excel to PowerPoint
For Each rng In configRng
With adminSh 'Defining value of variables or location in which value of variable can be found in the excel
vRange$ = .Cells(rng.Row, 6).Address
vTop = 127
vLeft = 14.2
vWidth = 692.64
vHeight = 235
vSlide_No = .Cells(rng.Row, 4).Value
End With
PPPres.Windows(1).Activate
PPPres.Windows(1).View.GotoSlide (vSlide_No)
Set expRng = Sheets("PERF-LIQ-VEL-BETA").Range(vRange$).CurrentRegion 'Set range from excel
expRng.Copy 'Set range from excel
PP.CommandBars.ExecuteMso "PasteSourceFormatting" 'Paste
Set PPSlide = PPPres.Slides(vSlide_No)
Set PPShape = PPSlide.Shapes(7) 'Set shape to be altered
With PPShape 'Resize and position range
.Top = vTop
.Left = vLeft
.Width = vWidth
.Height = vHeight
End With
'Clear memory to avoid errors in process of copying next range
Set PPShape = Nothing
Set PPSlide = Nothing
Application.CutCopyMode = False
Next rng 'Repeat
End Sub
字符串
2条答案
按热度按时间kulphzqa1#
尝试更改这些:
1.第一个月
至
PPSlide.Shapes.Paste
个2.
Set PPShape = PPSlide.Shapes(7)
个至
Set PPShape = PPSlide.Shapes(PPSlide.Shapes.count)
个就像这样
字符串
您也可以使用
PPSlide.Shapes.Paste
方法的返回ShapeRange对象来获取PPShape
,如下所示:型
lawou6xi2#
您的计算机很可能太快了,它在复制尚未完成的情况下处理粘贴(关于这种行为有很多问题)。通常提出的解决方案是延迟这个过程,这是可行的,但如果计算机变得更快呢?更多延误?延迟应该持续多久?我发现最有趣和更优雅的hint,我认为它也适用于类级别,是创建单独的过程来处理它,所以我们应该确定,在退出之前,复制和粘贴都已经处理过了。下面是一个示例用法。正如您所看到的,该副本可以直接在PowerPoint和Excel中使用(因为它会搜索父项,无论是工作表还是幻灯片,也可能是Word?我不实用,所以我不能说),而在Excel中粘贴,你需要使用下面的工作表(我还没有测试过,但我想它应该工作)。否则,他们可能只是
Object
s,以缩短它,并采取所有的可能性。字符串
更新
粘贴到Excel有点麻烦
复制的效果是一样的
型
从PowerPoint中粘贴到Excel中,但这是可行的(如何调用):
型
实际粘贴
型
然而,从Excel复制到PowerPoint是不同的,因为我现在有困难:
型
这是唯一的方法,我已经找到了到目前为止,以避免
mismatch
错误的设置,这反过来反映在复制子。所以过程略有不同,需要更多的调整。