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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sure.

Insert > Object > Create From File > Browse for your wave
Insert into a worksheet before saving as an addin
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top