Excel macro to make mp3 file help

terry stanley

New Member
Joined
Nov 25, 2017
Messages
3
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
360
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.
 
Last edited:

terry stanley

New Member
Joined
Nov 25, 2017
Messages
3
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

Active Member
Joined
Feb 16, 2017
Messages
360
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.
 
Last edited:

terry stanley

New Member
Joined
Nov 25, 2017
Messages
3
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

Active Member
Joined
Feb 16, 2017
Messages
360
I'm assuming you should have this "Microsoft Speech Object Library" but we wont know for sure until we can get into those options :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top