Embedding a resource into VBA addin

tracks

New Member
Joined
Aug 31, 2005
Messages
21
Hi,

Does anyone know if its possible to embed a resource (such as a wav file) into a VBA xla addin?



It's definitely possible in regular VB, but can't seem to find any info on it for VBA.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
L

Legacy 98055

Guest
Sure.

Insert > Object > Create From File > Browse for your wave
Insert into a worksheet before saving as an addin
 

tracks

New Member
Joined
Aug 31, 2005
Messages
21
thanks for the reply.

any idea on how to access the object without calling the OLE.verb? (my reservations about ole is that I don't want to call the app w/ the registered to file type for opening it).

for example: if its a wav, is it possible to play it through the PlaySound library as a "SND_RESOURCE"? e.g., PlaySound NameStr, hInt, SND_RESOURCE
 
L

Legacy 98055

Guest
Code:
Sub Example()
    Me.OLEObjects("Object 1").Verb Verb:=xlPrimary
End Sub

No can do? This used to be dependant upon Sound Recorder. I do not know if it still is. In other words, irregardless of the registration properties for "wav", the Dound Doc. Object would play thru Sound Recorder. I have no clue how to use this as a resource in a win Dll. You might copy the object to the clipboard which creates a shell scrap and then create a memory stream. I have no idea how to create a memory stream in VBA... Sorry.
 

tracks

New Member
Joined
Aug 31, 2005
Messages
21

ADVERTISEMENT

hi, thanks for the replies

For me, OLE .verb brings up the registered server application, which in my case is winamp (XL 2002 SP2). However, even if it was sound recorder, it's not the behavior I prefer.

I tried playing around with copying it to the clipboard, coversions between dataobject and object, but unfortunately I can't get it into the resource handle that the PlaySound lib is looking for. I'm too much of a novice to figure it out.

I did read up on memorystreams, but I couldn't find any examples of where it was used in VBA (only VB).

Does anyone know of any controls that embed a sound similar to how a picture is embeded with the picture form control?



it's really a shame that microsoft didn't include a resource editor into vba... it would really come in handy for a lot of things.

thanks again
 

Mitchys

New Member
Joined
Oct 20, 2004
Messages
24

ADVERTISEMENT

G'day tracks,

You may have already found the answer to this, but here's one that I found.

First, insert your sound into a worksheet that will not be used (except, perhaps, to store other sounds) - I also copied the sound file into the same folder as the workbook as per insructions on the other forum. Make sure you write down the object number (if you don't know, by opening up vba after the next step, you can see its name there).

Next, record a new macro, then play the file, then stop the recorder.

The next step is one where I'm not sure it's optional as I just followed the instructions I found on another forum. Hide the worksheet with the sound.

Now, open vba and copy the lines of the macro code into the vba code where you want the sound to play.

Next, alter your code so that it looks like this (and keep it to one line only by joining the two lines and deleting/adding vba code accordingly):
Code:
        Sheets("Sounds").OLEObjects("Object 2").Verb Verb:=xlPrimary

"Sounds" is the name of the hidden sheet containing the wave sound.

"Object 2" is the name of the object pasted into the hidden sheet when you inserted the sound.

Now, run your code and it should work. I don't know enough about vba to answer the question as to why the sound doesn't work when the recorded macro is pasted into vba, but I eventually found that the above line worked.

Regards,

Mitch
 

tracks

New Member
Joined
Aug 31, 2005
Messages
21
Hi,

thanks for responding all.

Mitchys:
The OLEObjects/Verb method evokes the registered windows application, so that method unfortunately won't work for my purposes. I am trying to get the sound to play in background but still be contained in the excel file. thanks for the detailed instructions though. I am certain your instructions will help others who searching for the OLE solutions.

Right_Click:
I ended up keeping the sound file separate. I was thinking that I could contain the wav within the excel as text and output it to temp wav, but that's not all the different from just keeping the file separate to begin with. It still seems like there must be a way to put a string into memory and associate a file handle with it, but I'm not sure how to do that. I've only played with a couple memory APIs and nothing with how they relate to file handles.

thanks again everyone, I very much appreciate all the consideration.
 
L

Legacy 98055

Guest
Same here. I have only worked with streams within the net framework and not directly by way of API functions. You can create a TextStream using the MS Scripting Runtime library's TextStream class, but I'm sure you would need a binary stream with any audio file. If you simply need portability and wish to store a very small wav file within your workbook, you could convert it to binary, save it in worksheet cells, in workbook names, or in several other targets. The application could check for the file on the target system or create the file if it does not exist and then use the PlaySound API. That might be overkill but it is an idea. Do your users have internet access? Maybe you should provide a single remote location and simply download the file to the target machine when needed.

Tom
 

Mitchys

New Member
Joined
Oct 20, 2004
Messages
24
G'day Tracks,

The way I describe above does play the sound in the background in my workbook. My OS is Windows XP, and my version of Excel is 2002.

Regards,

Mitchys
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,147
Members
412,306
Latest member
fabio6
Top