Excel macro to make mp3 file help

[terry stanley]

I have a macro that was created by one of the gurus from guru.com. I cannot find the error in it to run on my machine, but my brother says it works great on his. (I think I am too stupid to even know what to ask you guys for). I have a two column XL spread sheet, column a is ID number, column B is text. The macro creates an MP3 file, and saves it in a folder on my machine (or is supposed to). I changed the folder name to fit my machine, but the macro faults out, I think, when it tries to run the speech object library. CAN ANYONE TELL WHAT IS WRONG?

Option Explicit
Sub TextToMp3()
Dim sP As String, sFN As String, sStr As String, sFP As String
Dim myrange As Range
Dim singlecell As Range
Dim fileID As String
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
'so that the macro will automatically work until the last filled row in the ID column (A)
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
'culumn with the story text
Set myrange = Range("B2:B" & LastRow)
'change folder location here
'sP = "C:\DOCUMENTS\A AAWORKING FILE\GURU FILES\TERRY'S MP3 TEST FOLDER" Users\GLORIA\Desktop\mp3"
sP = "C:\DOCUMENTS\A AAWORKING FILE\GURU FILES\TERRY'S MP3 TEST FOLDER"
'starts the macro at B2, and continues to last populated row
For Each singlecell In myrange
singlecell.Select
'from the id column (A)
fileID = singlecell.Offset(0, -1).Value
'creates file name
sFN = fileID & "Story.mp3"
'path and file name
sFP = sP & sFN
'adds hyperlink to column c
ActiveSheet.Hyperlinks.Add Anchor:=singlecell.Offset(0, 1), Address:=sFP, TextToDisplay:=sFN
'string to use for the recording
sStr = singlecell.Value
'make voice wav file from string refers to the function, below
StringToMp3File sStr, sFP
Next singlecell
MsgBox "Conversion Complete"
End Sub


Function StringToMp3File(sIn As String, sPath As String) As Boolean
'Needs reference set to Microsoft Speech Object Library
Dim fs As New SpFileStream
Dim Voice As New SpVoice
'set the audio format
fs.Format.Type = SAFT22kHz16BitMono
'create wav file for writing without events
THIS IS WHERE IT HIGHLIGHTS IN YELLOW WITH AN ARROW
fs.Open sPath, SSFMCreateForWrite, False


'Set wav file stream as output for Voice object
Set Voice.AudioOutputStream = fs
'send output to default wav file "SimpTTS.wav" and wait till done
Voice.Speak sIn, SVSFDefault
'Close file
fs.Close
'wait
Voice.WaitUntilDone (6000)
'release object variables
Set fs = Nothing
Set Voice.AudioOutputStream = Nothing

 

[Coding4Fun]

There is a comment in your code that mentions you need to add the reference for the "Microsoft Speech Object Library"

While you are in the VBA editor click on Tools - References - This will show you a list of all the available references. In this list you should find the "Microsoft Speech Object Library", check the box next to this and then OK.

Try to run your code again. Let me know if this does not resolve the issue.

 

[terry stanley]

There is a comment in your code that mentions you need to add the reference for the "Microsoft Speech Object Library"

While you are in the VBA editor click on Tools - References - This will show you a list of all the available references. In this list you should find the "Microsoft Speech Object Library", check the box next to this and then OK.

Try to run your code again. Let me know if this does not resolve the issue.

Okay. When I hit TOOLS, the reference link is shaded, and does not open.

 

[Coding4Fun]

That appears to be a bug, try to recreate the file but before you save it try going into the Tools / Ref.

You definitely should be able to get to these options, maybe also try just starting a new blank workbook and see if you can get to this menu before doing anything else.

 

[terry stanley]

Thank you. I have not done that, but will do so. By the way, do you think it is possible that I do not have a thing called the speech object library? I do not even know what that is....I know I have the speech narrator, and it works.

 

[Coding4Fun]

I'm assuming you should have this "Microsoft Speech Object Library" but we wont know for sure until we can get into those options