I have a macro where I wanted to use referenced cells for the name of the tab and the range and chart name and telling it to paste into specific slide numbers. I keep getting errors because I'm not familiar with VBA and I've been trying to put together stuff from different coders. I have the Settings page picture for reference. Please help me!
I've added to the code but my error keeps happening in " Set expRng = Sheets(vSheet$).Range(VRange$)" This line gets an object error I believe I tried adding On Error resume Next but that still doesn't work because I'll get an error later.
```'Prior to running macro, enable Microsoft Powerpoint
````Object Library in Tools - Reference
````'Identifies selection as either range or chart
````If TypeName(Selection) = "vRange" Then
``` Call RangeToPresentation
Else
Call ChartToPresentation
End If
End Sub`
` Sub PowerPoint_Range()
'Copy and paste specific cell range
Dim ppt_app As New PowerPoint.Application
Dim pre As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim wb As Workbook
Set wb = ThisWorkbook
Dim rng As Range
Dim vSheet$
Dim VRange$
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vSlide_No As Long
Dim expRng As Range
Dim SettingsSh As Worksheet
Dim configRng As Range
Dim xlfile As String
Dim pptfile As String
Application.DisplayAlerts = False
Set SettingsSh = ThisWorkbook.Sheets("Settings")
Set configRng = SettingsSh.Range("Rng_sheets")
'defining the path file location
Dim pptPth As String
pptPth = SettingsSh.Range("pptPth").Value
Set pre = ppt_app.Presentations.Open(pptPth)
For Each rng In configRng
'---------set Variables on Settings Sheet
With SettingsSh
vSheet$ = .Cells(rng.Row, 2).Value
VRange$ = .Cells(rng.Row, 3).Value
vWidth = .Cells(rng.Row, 4).Value
vHeight = .Cells(rng.Row, 5).Value
vTop = .Cells(rng.Row, 6).Value
vLeft = .Cells(rng.Row, 7).Value
vSlide_No = .Cells(rng.Row, 8).Value
End With
'--------------EXPORT TO PPT
wb.Activate
Sheets(vSheet$).Activate
Set expRng = Sheets(vSheet$).Range(VRange$)
expRng.Copy
Set sld = pre.Slides(vSlide_No)
sld.Shapes.PasteSpecial ppPasteOLEObject
Set shp = sld.Shapes(1)
With shp
.Top = vTop
.Left = vLeft
.Width = vWidth
.Height = vHeight
End With
Set shp = Nothing
Set sld = Nothing
Set expRng = Nothing
Application.CutCopyMode = False
Set expRng = Nothing
Next rng
pre.Save
Set pre = Nothing
Set ppt_app = Nothing
wb.Close False
Set wb = Nothing
Application.DisplayAlerts = True
End Sub`
`Sub ChartToPresentation()
'Uses Late Binding to the PowerPoint Object Model
'No reference required to PowerPoint Object Library
Dim PPApp As Object 'As PowerPoint.Application
Dim PPPres As Object 'As PowerPoint.Presentation
Dim PPSlide As Object 'As PowerPoint.Slide
'Error message if chart is not selected
If ActiveChart Is Nothing Then
MsgBox "Please select a chart and try again."
Else
'Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
'Reference active presentation
Set PPPres = PPApp.ActivePresentation
'PPApp.ActiveWindow.ViewType = 1 ' 1 = ppViewSlide
'Reference active slide
Set PPSlide = PPPres.Slides _
(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
'Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture
'Paste chart
PPSlide.Shapes.Paste.Select
'Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub`
