Powerpoint Automation using macro's in VB

[Sudhir syal]

My final year project, is to create a "Project Mgmt Review" automation tool. Every month, the company I am working for asseses it's monthly performance's in the form of 14 spread sheets which it in turn incorporates into a 20 slide powerpoint. So at the end of every month, the values in the excel spreadsheet changes,which in turn has to be incorporated into the powerpoint PMR. Until I create, this software, At the end of every month , someone will have to manually cut,copy , paste and replace the old excel sheets in the PMR, with the new one. My project is to automate this entire process, with the click of a button.
I would have to generate a mechanism,whereby I can access the Excel spreadsheets using vb, an incorporate them into powerpoint.
I have been using macro's succesfully , in all my test sheets. But, there is a small problem, when I update the sheet and call the macro again, The size of the worksheet either shrinks dramatically, or it suddenly become's enlargened.Does anyone know why?
Another query of mine, is that I am not very certain as to how, the end user i.e the company I am doing my project with, will utilize this software. Do they have to go to a powerpoint file, with macro's enabled and then run the macro, Or Is their any mechanism whereby, I can create a separate form whereby they can use this tool like a normal Visual basic software. Please reply at the earliest.
Thank you.

[laisengchew]

May be you can make a connection to the excel path. After that open it for export to the powerpont.
Something sound like this:

Dim objExcel As Object
Set objExcel = CreateObject ("Excel.Application")
objExcel.DisplayAlert = False 'so wont prompt out ask user whether want to disabled or enable the macro hide in the excel workbook...

[Sudhir syal]

Thank you , For that. But now, quite expectedly there, has been a modifcation.
The requirement now, is that I have to copy a certain column of an excel spreadsheet into the powerpoint, if and only the certain column has the value "open", ie It has a condition clause. This obviously means, I can no longer use macros, atleast for this part of the project.
I have to update a table which has 4 columns in my powerpoint, from the corresponding 4 columns in a spreadsheet has around 8 columns in the network.
Moreover, I have to update only those entities, which have their field titled as "open", in the spreadsheet.
From, What I understand (which is very little), I probably have to use API'S, or some other sort of mechanism to access this information, and thereby automate the whole process , which is the end goal. Please reply A.S.A.P.
Thank you, Very much. Cheers

[Ecniv]

same as laisengchew posted, make an object to excel to grab the data. You can move and grab rows or just cells from the spreadsheets and loop through as many spreadsheets as you have.

[Sudhir syal]

Hey guys, Thank you very much for yr help so far.
I am doing the updating, using the macro's It generate's a code, with which the project run's error free, according to requirements.
But, The end user would have to open, the powerpoint document , enable macro's and only then run the software, which does'nt really give a professional feel to the software. After doing some reasearch, on MSDN, I came across a method whereby, I can make the automated macro code, work perfectly on a vb platform, If I modify it using the binding process, Apparently there is something called, "Early binding", "Late Binding", etc...which I will have to implement. I was wondering, If you knew anything about it, cause it is a little ambiguous in MSDN, I am pasting the sample code here, If you can give me the modified code using the binding process, I will be very obliged. Thank you very much.

ActiveWindow.View.GotoSlide 
Index:=ActivePresentation.Slides.Add(Index:=1, 
Layout:=ppLayoutBlank).SlideIndex 
  ActiveWindow.Selection.SlideRange.Shapes.AddOLEObject(Left:=120#, 
Top:=110#, Width:=480#, Height:=320#, FileName:="C:\Sudhir\Test\test1.xls", 
Link:=msoFalse).Select 
  With ActiveWindow.Selection.ShapeRange 
      .Left = 239.5 
      .Top = 256.875 
      .Width = 240.875 
      .Height = 26.25 
  End With 
  ActiveWindow.Selection.Unselect 
  ActiveWindow.View.GotoSlide 
Index:=ActivePresentation.Slides.Add(Index:=2, 
Layout:=ppLayoutBlank).SlideIndex 
  ActiveWindow.Selection.SlideRange.Shapes.AddOLEObject(Left:=120#, 
Top:=110#, Width:=480#, Height:=320#, FileName:="C:\Sudhir\Test\test2.xls", 
Link:=msoFalse).Select 
  With ActiveWindow.Selection.ShapeRange 
      .Left = 263.625 
      .Top = 263.25 
      .Width = 192.75 
      .Height = 13.5 
  End With 
  ActiveWindow.Selection.Unselect 
End Sub

Thank you.

[RobDog888]

Which binding do you want? Here are a couple of examples of binding.

'Early Binding:
'Add a reference to MS Powerpoint xx.0 Object Library
'Includes Intellisense for you objects properties and methods, etc.
Private Sub Command1_Click()
    Dim oApp As Powerpoint.Application
    Set oApp = New Powerpoint.Application
    oApp.Visible = True
    'Blah, Blah, Blah...
End Sub
 
 
'Late Binding:
'No reference added
'No Intellisense for object's properties or methods, etc.
Private Sub Command1_Click()
    Dim oApp As Object
    Set oApp = New Powerpoint.Application
    oApp.Visible = True
    'Blah, Blah, Blah...
End Sub

Now with both you will need to have PowerPoint installed or you will generate an error.