Paste Excel Range as HTML format into Powerpoint using VBA

[MitchelK]

Dear all,

I am trying to copy and paste multiple tables from Excel to PPT, using VBA. This works if I paste it as an Enhanced Metafile Picture. However, I would like to paste it as HTML format, so I can adjust the table later on in Powerpoint. Changing the PasteSpecial parameter in the code to "8" does not work with this code.

Adding the References of Powerpoint Objects in VBA doesn't work for some reason, so I need to find a different way.

Does anybody know the solution?

Many thanks in advance.

Here is a similar example of the code (taken from: http://www.clearlyandsimply.com/cle...icrosoft-excel-dashboards-to-powerpoint.html:

NB: I am using name references in the code, and not direct Ranges
Option Explicit
Dim PP As Object
Dim PP_File As Object
Dim PP_Slide As Object
Private Sub CopyandPastetoPPT(myRangeName As String,
myTitle As String, _
myScaleHeight As Single, _
myScaleWidth As Single)
Dim NextShape As Integer
Dim ReportDate As String
ReportDate = Range("myReportDate").Value & " / Week " & _
Range("myReportWeek").Value & " - "
Application.GoTo Reference:=myRangeName
Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture Range("A1").Select
PP.ActivePresentation.Slides.Add PP.ActivePresentation.Slides.Count + 1, 11
Set PP_Slide = _
PP_File.Slides(PP.ActivePresentation.Slides.Count)
PP_Slide.Shapes.Title.TextFrame.TextRange.Text = _
ReportDate & myTitle
NextShape = PP_Slide.Shapes.Count + 1
PP_Slide.Shapes.PasteSpecial 2
PP_Slide.Shapes(NextShape).ScaleHeight myScaleHeight, 1
PP_Slide.Shapes(NextShape).ScaleWidth myScaleWidth, 1
PP_Slide.Shapes(NextShape).Left = _
PP_File.PageSetup.SlideWidth \ 2 – _
PP_Slide.Shapes(NextShape).Width \ 2
PP_Slide.Shapes(NextShape).Top = 90
End Sub
Sub ExportToPPT()
Dim ActFileName As Variant
Dim ScaleFactor As Single
On Error GoTo ErrorHandling
ActFileName = Application.GetOpenFilename _
("Microsoft PowerPoint-Files (*.ppt), *.ppt")
ScaleFactor = Range("myScaleFactor").Value
Set PP = CreateObject("Powerpoint.Application")
If ActFileName = False Then
PP.Activate
PP.Presentations.Add
Set PP_File = PP.ActivePresentation
Else
PP.Activate
Set PP_File = PP.Presentations.Open(ActFileName)
End If
PP.Visible = True
CopyandPastetoPPT "myDashboard01", _
Range("myInputStartTitles").Offset(1, 0).Value, _
ScaleFactor, ScaleFactor
CopyandPastetoPPT "myDashboard02", _
Range("myInputStartTitles").Offset(2, 0).Value, _
ScaleFactor, ScaleFactor
CopyandPastetoPPT "myDashboard03", _
Range("myInputStartTitles").Offset(3, 0).Value, _
ScaleFactor, ScaleFactor
Set PP_Slide = Nothing
Set PP_File = Nothing
Set PP = Nothing
Worksheets(1).Activate
Exit Sub
ErrorHandling:
Set PP_Slide = Nothing
Set PP_File = Nothing
Set PP = Nothing
MsgBox "Error No.: " & Err.Number & vbNewLine & _
vbNewLine & "Description: " & Err.Description, _
vbCritical, "Error"
End Sub