Powerpoint to Excel

  • Thread starter Thread starter Wi11y13
  • Start date Start date
W

Wi11y13

Ok - I almost have this working now - the idea is to take variable data in a
powerpoint slide presentation and append it to the end of an Excel
spreadsheet to show who has completed a training presentation on-line. I have
it all working except that I can not get the Excel spreadsheet to close
properly. Here is the code I am using...


With CreateObject("Excel.application")
.Application.DisplayAlerts = True
.workbooks.Close FileName:=strPath & "\" & "TrainingRecord.xls"
.Application.Quit
End With

I have also tried the following and many various combinations of both of
these...


CreateObject("Excel.application").Application.DisplayAlerts = False
CreateObject("Excel.application").activeworkbook.Close (True)
CreateObject("Excel.application").Application.Quit

The Macro seems to go into never never land when executing the close command
(either one above).

Any ideas anyone.

Thanks,
 
Hi

Firstly, the CreateObject will create a new instance each time it's run,
whereas you just want to reference one and do stuff to it I guess.

I've got some old code that I've copied here for you - make sure you
test it thoroughly though. Ironically I can't remember why I put in the
checks about the memory leak, which goes to show the only memory leak
here is mine!

If this is in a macro inside PowerPoint, you'll need to reference the
Excel object model, which is done via the Tools, References menu
command. Choose the 'Microsoft Excel x.x Object Model' and you're away.

Then add this to a sub-routine:

Const No_App As Long = 429
Dim myExcel As Excel.Application
Dim LocalCopy As Boolean

On Error Resume Next
'try to reference an existing running instance of Excel
Set myExcel = GetObject(, "Excel.Application")

'if Excel is not running the previous command will have caused an
error which we can check
If Err = No_App Then
Set myExcel = New Excel.Application
'add a workbook to it (it starts with none)
myExcel.Workbooks.Add
LocalCopy = True
End If

'we should have an open Excel object now, so save the workbook
With myExcel
.Workbooks(1).SaveAs FileName:="TrainingRecord.xls"
'quit only if created locally to avoid a memory leak
If LocalCopy Then .Quit
End With

'clear up
Set myExcel = Nothing


Hope this helps, or at least points you in the right direction.
 
Back
Top