Convert an Excel file into a PowerPoint file with one click of a button

[GaryDrummSr]

Part of a new assignment is to create an EIS Report for management.
EIS is an acronym fro Executive Information Summary.

We are a factory that makes multiple products, and we endeavor to keep track of the frequency of errors, and the hours of production lost in the process, once every 24 hours.

I pull the data and make an Excel spreadsheet, with charts in Excel to show trends, etc.

I must then convert the whole thing into a PowerPoint presentation, which gets posted on a board by the machine.

We probably have over 100 machines, but I am only responsible for about 1/2 of that getting done.

Is there a way to automated the conversion of Excel files (sheets at the bottom of one page)?

I am vaguely familiar with Macros, but I'm hoping there is something already in place that we just,don't know about.

I use Excel 2016.

Thanks,
Gary

 

[gravanoc]

Did you find a solution yet? I think I know how, but got to try it out in a bit when home.

 

[GaryDrummSr]

Thanks gravanoc for your reply.
I have not had any other replies so far, so I am anxious to hear what you have in mind.

I'm not very proficient with macros and the like.
Guys like Aladin Aqueric fascinate me, but I get lost pretty quickly when reading some of their solutions.

So if your a guru like he is please bare with me.

Thanks again,
Gary

 

[gravanoc]

I'm definitely not a guru, but I'm familiar with the syntax needed. This page describes the basic mechanism, so I can probably get something for you that works later tonight. https://docs.microsoft.com/en-us/of...erence/user-interface-help/getobject-function

 

[gravanoc]

Ok, I got something working, but I need a bit more info on what you want to be transferred to the Powerpoint pptx. Is it just table data, or does it involve charts, etc.?

Can you tell me what ranges are involved? Such as, Sheet 3, "A1:F30".

 

[GaryDrummSr]

Thanks for the reply.
It does involve the Charts, as a matter of fact, it's only the charts that I need to show up on the PowerPoint.
I had something similar to a solution late yesterday, but it involved too many steps.
It was a cut/copy/paste approach.

Here is the process:
I create the charts in Excel, and I then need to make those individual charts (perhaps as many as 30) into individual PowerPoint slides.

 

[gravanoc]

This is what I have so far.

Sub ExcelChartsIntoPPT()

Dim wkSht(0 To 30) As Worksheet
Dim cRng(0 To 30)
Dim pptApp As Object
Dim newPPTX As Object
Dim cSlide(0 To 30) As Object
Dim cShape As Object


Set wkSht(0) = Worksheets("Sheet1")
  
  On Error Resume Next
    
      Set pptApp = GetObject(Class:="PowerPoint.Application")
      
      If pptApp Is Nothing Then Set pptApp = CreateObject(Class:="PowerPoint.Application")
    
  On Error GoTo 0


  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
  End With
  


  Set newPPTX = pptApp.Presentations.Add


For i = 0 To 5


  Set cRng(0) = wkSht(0).ChartObjects("Chart 1")
  Set cSlide(i) = newPPTX.Slides.Add(i + 1, 11)
  cRng(0).Copy
  cSlide(i).Shapes.PasteSpecial DataType:=2


Next


  pptApp.Visible = True
  pptApp.Activate


  Application.CutCopyMode = False
  
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
 End With
  
End Sub

Make a new workbook & on Sheet1 put down some data, then make a chart out of it called Chart 1. Do you already have Developer tab enabled? If not, go to Options & customize your Ribbon then mark the appropriate box. Go to the developer tab and View Code. From there, place the above code into Sheet1 under Microsoft Excel Objects. Try running it to see if it works on your end. After that, I can help you customize it more to fit what you are trying to do.

 

[GaryDrummSr]

Thanks gravanoc,
I haven't tried it yet, but I will get to it as quickly as possible this morning.

I'll post back as soon as I get a chance.

Thanks a million.

Gary

 

[GaryDrummSr]

When I tried to save it it said I cannot save it in a Macro Free zone.Re: Convert an Excel file into a PowerPoint file with one click of a button

gravanoc,

I was able to get the code in the right place I think, but when I ran it, it gave me a compile error which said it is an invalid outside procedure.
The company I work for may have a block on macros for security reasons.

Is that a possibility?
Gary
When I tried to save it said I cannot do so in a Macro Free Zone.

Perhaps I had better call the Help Desk here at work and ask them about it.

I'll post back
Thanks

 

[gravanoc]

Re: When I tried to save it it said I cannot save it in a Macro Free zone.Re: Convert an Excel file into a PowerPoint file with one click of a button

Sorry, I forgot to mention there are some other steps you can take to try and get around that. You might be right though, if your company has a security policy it can be a headache.

The file has to be saved as a .xlsm instead of a .xslx. Also, on the Developer tab there is a macro security command that lets you configure what is allowed. Let me know if I can help more.

The code is only a prototype as it is because I put in placeholders for the arrays. I can change that later.