PDA

View Full Version : Copying excel ranges to powerpoint



arblu7
11-22-2017, 04:21 AM
Hello guys,

I am new in VBA programming and I needed some help. My problem consists of copying excel ranges (from 1 to n), to powerpoint slides, wich are macro-named according to product name. I have tried the code below:




But this one copies my range to a new slide in my open powerpoint. How can I do to copy & paste it to the slide I have renamed (with macro)? Also another issue, how can I copy filtered ranges, in order to have the same header of the table?







Sub ExcelRangeToPowerPoint()


Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object


'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("(c2:h5)")


'Create an Instance of PowerPoint
On Error Resume Next

'Is PowerPoint already opened?
Set PowerPointApp = GetObject(Class:="PowerPoint.Application")

'Clear the error between errors
Err.Clear

'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(Class:="PowerPoint.Application")

'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If

On Error GoTo 0


'Optimize Code
Application.ScreenUpdating = False

'Reference active presentation
Set myPresentation = PowerPointApp.ActivePresentation

Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly


'Copy Excel Range
rng.Copy


'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)


'Set position:
myShape.Left = 180
myShape.Top = 350


'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate


'Clear The Clipboard
Application.CutCopyMode = False

End Sub


Thanks in advance

SamT
11-23-2017, 07:22 AM
Moderator Bump

snb
11-23-2017, 08:41 AM
What do you mean by filtered ranges ?
Can you post a sample Excel file to illustrate what you mean ?

arblu7
11-24-2017, 02:24 AM
Hello snb, thank you for your reply. This is my example. Now I want to copy the header of the table and data range for prod 1 in a powerpoint slide, then the header of the table ande data range for prod 2 in a different powerpoint slide. I have many products and examples like this one below. So how can I do it automatically via VBA?

21037

snb
11-24-2017, 08:36 AM
This is not an Excel file, but .jpg.

arblu7
12-04-2017, 03:13 AM
Here is the excel file.

snb
12-04-2017, 04:28 AM
Start removing all merged cells.
Start always using cel A1 (row 1 & column 1)
Start using an intelligent Table in Excel.