Here's the code.
Public Declare Function sndPlaySound32 _ Lib "winmm.dll" _ Alias "sndPlaySoundA" ( _ ByVal lpszSoundName As String, _ ByVal uFlags As Long) As Long
Sub test()'sndPlaySound32 "C:\Windows\Media\Chimes.wav", 0&sndPlaySound32 "C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish\Testing123.wav", 0&'c:End Sub
I got it from a website recommended by Ingo U. But the problem is not with this code, which works. The problem is that I want to create a folder called "Sounds" in the folder which contains the Worksheet which runs the code. I want to be able to move the folder which contains the both worksheet and the folder "Sounds" around to different computers.
I need the VBA code to access sound in the folder "Sounds" which is inside the folder containing the worksheet. It is no good if the string containing the path to these audio files is written in the VBA code and can't change when the folder which contains both the worksheet and "sounds"is moved around.
Suppose the folder containing the worksheet is c:\folder1\folder2
What I need is code like
Function PathToThisWorksheet() as string
' this function returns "c:\folder1\folder2\ in the example mentioned above"
So That I can play audio file "MyAudio1.wav"with the following code
sndPlaySound32 PathToThisWorksheet & "Sounds\" & "MyAudio1.wav"
In the Sub test()
add this line at the beginning of the code
myPath = "C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish\Testing123.wav"
then in the line that follows replace that text between the quotes. e.g.
"C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish\Testing123.wav"
with the variable myPath
This way you will be able to retrieve the path by referring to the variable myPath
Hope this helps.
John, I guess I didn't make my situation clear. Imagine I create the workbook "SpanishTest.xlm" and save it in folder
C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish\
Imagine further that this folder, besides containing "SpanishTest.xlm" also contains the folder "Sounds". If I want to play the sound Testing123.wav, I could do so using the following code
mypath = "C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish\Sounds\Testing123.wav"
sndPlaySound32 mypath, 0&
Now Imagine that I save the entire folder
C:\Users\George\My Documents\Timothy's Academics\Timothy's Spanish
to a thumb drive and copy it to the desktop of Timothy's computer. I go into this folder on the desktop of Timothy's computer and doubleclick on SpanishTest.xlm. Now the code to play Testing123.wav won't work because the specified path is no longer relevant. I don't want to have to change the VBA code every time I move the folder
to a new location.
Recall that when you try to do a "Save As" in Excel, the program remembers the path from which the workbook was loaded and suggests this path as the location to which the new workbook will be saved. What I am trying to do is find out how I might use VBA to grab hold of that suggested path, put the path into a string variable, and use this string variable to create the path to the folder "Sounds" where the file Testing123.wav is located. The folder "Sounds"will always be in the same folder as the folder from which Spanishtest.xlm, the active workbook, was loaded.