Announcement

Collapse
No announcement yet.

VBA : Use of SAPI Voice When Opening Excel Workbooks

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA : Use of SAPI Voice When Opening Excel Workbooks



    Hi, everyone -

    After speaking with several people, a
    suggestion was made to try and use the SAPI built-in voice to speak in
    Excel rather than wav files. I've included some test code below:

    Sub test()
    Dim s As Object
    Set s = CreateObject("SAPI.SpVoice")
    s.Speak "Greetings. How are you?"
    Set s = Nothing
    End Sub

    I'm currently using Excel 2000, which, I guess, is limited to only one
    voice. I had to do the Tools - References to Microsoft Speech Object
    Library and it works.

    If anyone has tried this, I'm wondering:

    [1] is there a way to modify the code so that I could insert it by way
    of a Workbook_Open placement and the voice would announce the name of
    the file?

    [2] Has anyone had any success in playing with this aspect so that the
    voice sounds more "normal" rather than the typical computer voice?

    Thanks so much for the help,

    Golf

  • #2
    Hi Golf


    Your first question is certainly possible, e.g

    Code:
    Private Sub Workbook_Open()
    Dim s As Object
    Set s = CreateObject("SAPI.SpVoice")
    s.Speak "Greetings. How are you. Welcome to" & ThisWorkbook.Name
    Set s = Nothing
    End Sub
    No too sure on the second one though. You could always add something like;
    Code:
    Private Sub Workbook_Open()
    Dim s As Object
    Set s = CreateObject("SAPI.SpVoice")
    s.Speak "Greetings. How are you. I am Stephen Hawking. Welcome to" & ThisWorkbook.Name
    Set s = Nothing
    End Sub
    :biggrin:

    I meaning nothing distasteful by that, I have the utmost admiration for the great Stephen Hawking.

    Comment


    • #3
      Did You check out Ivanīs reply here:
      http://216.92.17.166/board2/viewtopi...highlight=sapi
      Kind regards,
      Dennis

      .NET & Excel | 2nd edition PED | MVP

      Comment


      • #4
        Hi, guys -

        Thanks for the responses. I really appreciate your help.

        Dave: I don't know how many tries I made to come up with & ThisWorkbook.Name. UUUGGG!!! Code works really well now. While experimenting (scary, I know), I went ahead and inserted the code so that the voice will play simultaneously with my opening splash screen:

        Private Sub UserForm_Activate()

        Application.ScreenUpdating = True
        DoEvents

        Dim s As Object
        Set s = CreateObject("SAPI.SpVoice")
        s.Speak "hi rick??? welcome to" & ThisWorkbook.Name
        Set s = Nothing

        Application.OnTime Now + TimeValue("00:00:02"), "KillTheForm"

        End Sub

        I threw a couple quaetion marks in the text string because it seemed to help with the voice's inflection (at least a little).

        Hi, Dennis -

        I did see Ivan's posting. He is great and has a fantastic website. I went ahead and downloaded his Text-to-Speech workbook and played around with it last night. Because I use Excel 2000 at home (where I do most of my "development"), I can never remember what version of Office we have at work. I thought, at least, using the built-in voice would get me started. I certainly did not mean any insult to Ivan. I'll have to check out the version at work, on Monday, and then decide how to proceed.

        I'll let you guys know how I decide to proceed.

        Thanks again for the help,

        Golf

        Comment


        • #5
          I donīt think Ivan will be insulted but I was curious about the outcome.

          Dennis
          Kind regards,
          Dennis

          .NET & Excel | 2nd edition PED | MVP

          Comment


          • #6
            Hi, Dennis and Dave -

            I played around with my voice code and came up with this:

            Private Sub UserForm_Activate()

            Application.ScreenUpdating = True
            DoEvents
            Dim s As Object
            Set s = CreateObject("SAPI.SpVoice")

            If Sheets("Data_Entry_Sheet").[k43] = "X" Then
            s.Speak "hi jeff??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k44] = "X" Then
            s.Speak "hi jeff??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k45] = "X" Then
            s.Speak "hi mareea??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k46] = "X" Then
            s.Speak "hi shanoona??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k47] = "X" Then
            s.Speak "hi deborah??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k48] = "X" Then
            s.Speak "hi linda??? welcome to" & ThisWorkbook.Name
            Else

            If Sheets("Data_Entry_Sheet").[k49] = "X" Then
            s.Speak "hi frank??? welcome to" & ThisWorkbook.Name
            Else

            Set s = Nothing

            End If
            End If
            End If
            End If
            End If
            End If
            End If
            Application.OnTime Now + TimeValue("00:00:04"), "KillTheForm"
            End Sub

            Within my Data Entry Sheet, the user would place an "X" in the field next to their name. This way, the applicable employee name appears on all of the applicable worksheets and letters that are generated. The use of the code above, kind of, "enhances" the spreadsheet with a greeting rather than my previous practice of inserting individual wav greeting files. I had to adjust the spelling of the employee's names slightly to have the voice pronounce the name correctly.

            I thought I'd post the code in case anyone else is interested in using it.

            Thanks again for the help,

            Golf

            Comment


            • #7
              Hi Golf4:

              Please forgive me for piggybacking on your request. I'm interested in playing around with the speach possibilities and also use Excel 2000 with Windows 2000.

              My reference list does not include Microsoft Speach Object Library, although it does include Speach Recognition, Text to voice, etc.

              Does anybody know how I go about obtaining the Microsoft Speach Object Library without upgrading my operating system to XP?

              Thanks in advance

              Ted..........

              Comment


              • #8


                Hi, Ted -

                Certainly glad to help. In addition to the help provided on this Board, you may want to check out this link:

                http://www.developersdex.com/vb/mess...2677&r=4045191

                As far as finding the Microsoft Speech Object Library, can I assume you looked through the entire list in VB Editor under Tools - References? After the first 5 or so entreis, the others are in alpha order. It should be there (at least on mine here).

                If you're interested in other options, i.e. inserting wavs or objects (for voices), I've been fooling around with this quite a bit. Just let me know and I'll pass on my "wisdom". :o

                HTH,

                Golf

                Comment

                Working...
                X