Macro to Insert Object with browsing ability

[S Davis]

Hello all,

I have, unfortunately, entered the realm of VBA limbo, where I
understand how code works by reading it, but have no idea how to write
it (having just picked things up over time).

What I am trying to do is add functionality into my workbook where a
user can click a button to insert an object, much like you would do
via the Insert --> Object menu option.

My current thought was to use Jim Rech's 'Browse for Folder' in
combination with a simple recorded Macro. Basically, the user should
click the button, be prompted with an ability to select the file to
insert, and then have that location of the file be returned back to
the VBA code so that it can go ahead and insert it.

I can work the macro's independently - Jim's will browse for a file,
but only returns a msgbox. My simple recorded macro will insert an
object fine, but only from a static - hardcoded filename. I'd
obviously like to replace the embedded filename with the prompting
from Jim's code so that rather than return a msgbox, it returns the
filename to my own macro and voila, object inserted.

If there is a better method I am very open to it.

Thanks!

Jim's BrowseForFolder (minus all the background info obviously):

Sub BrowseFolders()
Dim RetStr As String, Flags As Long, DoCenter As Boolean
Flags = BIF_RETURNONLYFSDIRS + BIF_BROWSEINCLUDEFILES +
BIF_NEWDIALOGSTYLE
With Sheet1
DoCenter = True
End With
RetStr = GetDirectory(CurDir, Flags, DoCenter, "Please select a
location to store data files")
If RetStr <> "" Then MsgBox RetStr
End Sub

My easy recorded Macro, would like to replace the "c:\...." with what
comes from Jim's, but can't figure it out:

Sub InsertObject()
ActiveSheet.OLEObjects.Add(Filename:= _
"c:\files\file.abc", Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer
\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
IconIndex:=0, IconLabel:= _
"c:\files\file.abc").Select
End Sub

 

[S Davis]

Ok, so it looks like I made things way more complex than they needed.
Application.GetOpenFileName works much better.

Still having issues though - this should be an easy answer for
someone.

I tried putting the Application.GetOpenFileName into the recorded
Macro above and it errors out:

ActiveSheet.OLEObjects.Add(Application.GetOpenFileName("All Files
(*.*), *.*"), Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer
\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
IconIndex:=0, IconLabel:= _
"C:\Documents and Settings\scdav\Desktop\test.xls").Select

What am I doing wrong?

 

[RompStar]

What kinds of objects are you planning on inserting ?

 

[S Davis]

What kinds of objects are you planning on inserting ?

OLE objects

I've got this working after floundering with a bunch of other code. I
now understand how VBA works a bit better... declare your whatever
outside the code, then call it back in when you need it. I was trying
to declare it AS a call (I think) which did not work.

Sub InsertObject()

FileLocation = Application.GetOpenFileName("All Files (*.*), *.*")
ActiveSheet.OLEObjects.Add(Filename:=FileLocation, Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer
\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
IconIndex:=0, IconLabel:= _
"C:\Documents and Settings\scdav\Desktop
\Browseforfolder.xls").Select
End Sub

My challenge now is dealing with the icons that will show up. I want
to display the appropriate icon for the file type selected, without
resorting to looking in the C drive windows folder. Right now I'm
thinking it would be simple enough to just look at the extension of
the filename selected and then associate that with a group of icons
held on our database. Easy and crude.

 

[RompStar]

Not sure about OLEObjects, havn't worked with that extensivaly, but
using:

Workbooks.Open FileName:=FileToOpen
wsOpenName = ActiveWorkbook.Name ' this will get the file name
of the Excel being opened and insert into wsOpenName variable
wsOpenPath = ActiveWorkbook.Path ' this one will do the same and
insert the path instead into wsOpenPath

just have to access the read-only property of the workbook class. To
look things up, in VBA, go to the Object browser, press F2 while in
VBE.