powerpoint notes exported to excel

[Guest]

Hi all,
This is actually a question about using a macro to export from
Powerpoint Notes to an Excel file. If there is a better forum for
this, please advise.
Meanwhile, I am having difficulty in removing the paragraph (enter)
breaks from the text output from Powerpoint Notes. If the text in the
Notes contained "enter" breaks, then the text is exported to the
spreadsheet, divided amongst different cells, where the "enter" breaks
occur. I need to remove these breaks somehow, so that I can have the
notes for each slide in a single cell.
thanks for any help you can provide! Code is below.

Regards,
Louis

===============================================

Sub ExportNotesText7()

Dim oSlides As Slides
Dim oSl As Slide
Dim oSh As Shape
Dim strNotesText As String
Dim strNotesText2 As String
Dim strFileName As String
Dim intFileNum As Integer
Dim lngReturn As Long

' Get a filename to store the collected text
strFileName = InputBox("Enter the full path and name of file to
extract notes text to", "Output file?")

' did user cancel?
If strFileName = "" Then
Exit Sub
End If

' is the path valid? crude but effective test: try to create the
file.
intFileNum = FreeFile()
On Error Resume Next
Open strFileName For Output As intFileNum
If Err.Number <> 0 Then ' we have a problem
MsgBox "Couldn't create the file: " & strFileName & vbCrLf _
& "Please try again."
Exit Sub
End If
Close #intFileNum ' temporarily

' Get the notes text
Set oSlides = ActivePresentation.Slides
For Each oSl In oSlides
For Each oSh In oSl.NotesPage.Shapes

If oSh.PlaceholderFormat.Type = ppPlaceholderBody Then
If oSh.HasTextFrame Then
If oSh.TextFrame.HasText Then
strNotesText2 = Replace(strNotesText2, Chr$(13),"")
strNotesText2 = Replace(strNotesText2, Chr(11), "")
strNotesText2 = Replace(strNotesText2, Chr(10),"")
strNotesText2 = strNotesText2 & "Slide: " &
CStr(oSl.SlideNumber) & vbTab & oSh.TextFrame.TextRange.Text & vbCrLf

Else: strNotesText2 = strNotesText2 & "Slide: " &
CStr(oSl.SlideNumber) & vbTab & " " & vbCrLf


End If
End If
End If
Next oSh
Next oSl

' now write the text to file
Open strFileName For Output As intFileNum
Print #intFileNum, strNotesText & strNotesText2
Close #intFileNum

' show what we've done

' lngReturn = Shell("EXCEL.EXE " & strFileName, vbNormalFocus)

End Sub

 

[Chirag]

Hi,

You might want to directly automate Excel while exporting the slide notes to
it. The following macro will export a presentation to new excel workbook:

---
Sub ExportNotesToExcel(ByVal Pres As PowerPoint.Presentation, _
ByVal Excel As Excel.Application)

Dim Sld As PowerPoint.Slide
Dim WrkBook As Excel.Workbook
Dim WrkSheet As Excel.Worksheet
Dim Row As Long

Row = 1
Set WrkBook = Excel.Workbooks.Add
Set WrkSheet = WrkBook.Sheets.Add
WrkSheet.Name = Pres.Name
For Each Sld In Pres.Slides
WrkSheet.Cells(Row, 1) = "Slide: " + CStr(Sld.SlideIndex)
WrkSheet.Cells(Row, 2) = GetNotesText(Sld)
Row = Row + 1
Next
End Sub
---

I have used the GetNotesText() macro from
http://officeone.mvps.org/vba.html.
Use this ExportNotesToExcel() like the one used in Test() macro below:

---
Sub Test()
Dim Excel As Excel.Application

Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
ExportNotesToExcel ActivePresentation, Excel
End Sub
---

- Chirag

PowerShow - View multiple PowerPoint slide shows simultaneously
http://officeone.mvps.org/powershow/powershow.html

 

[Brian Reilly, MVP]

Chirag,
It's not fair copying code from yourself (vbg).
Happy Diwali to you and your family. Have a good time.

Brian Reilly, MVP

 

[Guest]

Hi Charag,
Unfortunately, neither macro worked for me, as both returned compile error
messages. I have tried them separately and together, but with the same
results - any ideas as to what might be done to correct it?
Thanks,
Louis

 

[Chirag]

You need to set reference to Microsoft Excel Object Library.
After pasting the macro in the presentation, do the following:
1. Select "Tools" | "References..." menu item.
2. Locate and check mark "Microsoft Excel x.y Object Library" box.
3. Click OK.

Now the macros should compile fine.

- Chirag

PowerShow - View multiple PowerPoint slide shows simultaneously
http://officeone.mvps.org/powershow/powershow.html

 

[Steve Rindsberg]

Hi Charag,
Unfortunately, neither macro worked for me, as both returned compile error
messages.

You can see the computer, we can't. You'll have to tell us what the error
messages say.

But at a guess, you need to add a reference to Excel before the code will
compile. Tools, References, put a check next to Microsoft Excel, then try it
again.

I have tried them separately and together, but with the same

 

[Guest]

Chirag,
Thanks so much for your help! This works great.
I was wondering what, in this code, as opposed to the code which I initially
pasted in my first question, allows all of the notes for each slide to wind
up together in a cell, rather than being divided into different cells, as a
result of carriage return feeds, etc?

Thanks again for your help,
Louis

 

[Guest]

Thanks Steve

You can see the computer, we can't. You'll have to tell us what the error
messages say.

But at a guess, you need to add a reference to Excel before the code will
compile. Tools, References, put a check next to Microsoft Excel, then try it
again.

I have tried them separately and together, but with the same

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================