[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:
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
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:
- The excel range selected is quite big and too big for the slide
- 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
Last edited:
