Access into Excel into Powerpoint chart size issues. (1 Viewer)

[SOLVED] Access into Excel into Powerpoint chart size issues.

I have an access database which produces as reports a series of financial reports comprising multi sheet excel workbooks with many tables and charts. These are created in VBA. Management have decided they want the information as a powerpoint presentation.

I have modified the vba code that generates the Excel workbooks to "copy and paste" the tables as ranges into powerpoint (charts will follow). This is all fairly new to me.

There are two issues:

1. The excel range selected is quite big and too big for the slide
2. I would like to save the finished powerpoint under a new name

The first point is the biggest issue. What I don't understand is if I check the dimensions of the range selected they are 825 x 404, if i then check the dimensions of the chart shape receiving them it is 1376 x 676 yet the range completely overflows.

Can anyone advise how I can get the range to fit. Code for doing the powerpoint bit shown below:


'Below are variables associated with powerpoint manipulation

Dim rngToCopy As Excel.Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object
Dim myShapeRange As Object
Dim dblRngHeight As Double
Dim dblRngWidth As Double
Dim dblShapeHeight As Double
Dim dblShapeWidth As Double
Dim intSlideCount As Integer

'Create Powerpoint test code


Set rngToCopy = wsht5.Range("A3:O30")
dblRngHeight = rngToCopy.Height
dblRngWidth = rngToCopy.Width

If PowerPointApp Is Nothing Then
Set PowerPointApp = CreateObject(Class:="PowerPoint.Application")
End If

Set myPresentation = PowerPointApp.Presentations.Open(FileName:="C:\temp\test.pptx")
intSlideCount = myPresentation.Slides.Count
Set mySlide = myPresentation.Slides.Add((intSlideCount + 1), 11) '11 = ppLayoutTitleOnly
mySlide.Name = "My Slide"

rngToCopy.Copy
Set myShapeRange = mySlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile)
dblShapeHeight = myShapeRange.Height
dblShapeWidth = myShapeRange.Width
With myShapeRange
.Left = 10
.Top = 10
End With

'???? NB I tried setting myShapeRange .height and .width to smaller value but it didn't work

PowerPointApp.Visible = True
PowerPointApp.Activate

'??? How do I save back into same location with different name?

'End Create Powerpoint test code

are you copying the chart?
why not copy it as a Picture (CopyPicture) and Paste it in the new Shape object of ppt.

arnelgp said:

are you copying the chart?
why not copy it as a Picture (CopyPicture) and Paste it in the new Shape object of ppt.

Click to expand...

Thanks that works better. How can I determine the size of the pasted image and resize it?

same as the one you have, range.
if you paste it it will "zoom" to fit the shape.