I can't make a wav file to play; the path is wrong I guess;p

[Vera]

I have Excel 2000 and I try using this code from
http://www.j-walk.com/ss/excel/tips/tip87.htm



'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long


Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function


The path of my wave file is:

C:WINNTMediaringout.wav


I replace in the above code as follows:

WAVFile = ThisWorkbook.Path & "C:WINNTMediaringout.wav"

but it doesn't play that sound. It plays like the default sound because it can't find the path for ringout. When running it, the sound card is not in use by any other program.

Thanks a lot for any help.

 

[SamS]

vera try the code without ThisWorkbook.Path as you are inserting the path for the workbook into the path for the WAV file

 

[Vera]

Thank you Sam ;works beatifully

 

[Vera]

Sorry I forgot something; it seems that the condition accepts only integer. I want to make it work whenever, lets say in cell A1 I don't have the word "report". So if in A1 I have any number, or any text, the macro should work. If A1 has only the word "report" or "REPORT", the macro should not play. To make this more clear, I work with spreadsheets and sometimes I notice that somebody else made changes without letting me know; and always in a rush, even if I promise myself before editing it to look in File/properties to make sure that it is my machine from where the file was saved for the last time, many times I foget to do so, that's why I need this macro. I don't want to use "share" the file for some other reasons. Thank you so much.
This message was edited by Vera on 2002-10-10 18:09

 

[SamS]

Vera, I'm a bit rusty with VBA coding at the moment and this is a function with which I am rustier still - I am still trying to understand how this one works - normally I would use and If statement but I'm not sure here - I think I'd better throw this one open to the gurus.

 

[Andrew Poulsom]

This should work:

=Alarm(UPPER(A1),"<>REPORT")

 

[Vera]

I am sorry Andrew but it doesn't work!?! Did you format the cell in certain way/type? Whatever function I use, even if the result is a number (Type, Code etc) the damned think doesn't work; as soon as I use a number it works.

 

[Andrew Poulsom]

Well now I look at it more closely I see that it won't.

In your code change the line:

If Evaluate(Cell.Value & Condition) Then

to

If Evaluate("""" & UCase(Cell.Value) & """" & Condition) Then

Then change your formula to:

=Alarm(A1,"<>""REPORT""")

Lots of quotes are needed to deal with strings!

 

[Vera]

It so puzzeling. I found a work around but still just for the sake of solving it, I would be so curious to see how can be done.

My workaround is: A1 has formula that returns who saved the last the file; when is saved by me, will return, lets say the word REPORT.

In a nearby cell (C1) I put formula

=IF(ISERROR(SEARCH("REPORT",A1,1)),0,IF(SEARCH("REPORT",A1,1)=0,0,1))

In B1 I have

=PERSONAL.XLS!Alarm(B1,"=0")

Thank you all again

 

[Andrew Poulsom]

Vera,

Did you see my last post?

If you are using it in other workbooks by referencing your PERSONAL.XLS then your workaround is the best solution. But your formula in C1 can be shortened to:

=IF(A1="REPORT",1,0)