Re: Macro to Insert Object with browsing ability by RompStar
RompStar
Fri May 09 17:31:44 CDT 2008
Not sure about OLEObjects, havn't worked with that extensivaly, but
using:
Workbooks.Open FileName:=3DFileToOpen
wsOpenName =3D ActiveWorkbook.Name ' this will get the file name
of the Excel being opened and insert into wsOpenName variable
wsOpenPath =3D 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.
On May 9, 3:20=A0pm, S Davis <theseanda...@gmail.com> wrote:
> On May 9, 2:47 pm, RompStar <RompS...@gmail.com> wrote:
>
>
>
> > What kinds of objects are you planning on inserting ?
>
> > On May 9, 1:19 pm, S Davis <theseanda...@gmail.com> wrote:
>
> > > 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:
>
> > > =A0 =A0ActiveSheet.OLEObjects.Add(Application.GetOpenFileName("All Fil=
es
> > > (*.*), *.*"), Link:=3DTrue, _
> > > =A0 =A0 =A0 =A0 DisplayAsIcon:=3DTrue, IconFileName:=3D _
> > > =A0 =A0 =A0 =A0 "C:\WINDOWS\Installer
> > > \{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
> > > =A0 =A0 =A0 =A0 IconIndex:=3D0, IconLabel:=3D _
> > > =A0 =A0 =A0 =A0 "C:\Documents and Settings\scdav\Desktop\test.xls").Se=
lect
>
> > > What am I doing wrong?
>
> > > S Davis wrote:
> > > > 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 wri=
te
> > > > 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()
> > > > =A0 =A0 Dim RetStr As String, Flags As Long, DoCenter As Boolean
> > > > =A0 =A0 Flags =3D BIF_RETURNONLYFSDIRS + BIF_BROWSEINCLUDEFILES +
> > > > BIF_NEWDIALOGSTYLE
> > > > =A0 =A0 With Sheet1
> > > > =A0 =A0 DoCenter =3D True
> > > > =A0 =A0 End With
> > > > =A0 =A0 RetStr =3D GetDirectory(CurDir, Flags, DoCenter, "Please sel=
ect a
> > > > location to store data files")
> > > > =A0 =A0 If RetStr <> "" Then MsgBox RetStr
> > > > End Sub
>
> 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()
>
> =A0 =A0 FileLocation =3D Application.GetOpenFileName("All Files (*.*), *.*=
")
> =A0 =A0 ActiveSheet.OLEObjects.Add(Filename:=3DFileLocation, Link:=3DTrue,=
_
> =A0 =A0 =A0 =A0 DisplayAsIcon:=3DTrue, IconFileName:=3D _
> =A0 =A0 =A0 =A0 "C:\WINDOWS\Installer
> \{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
> =A0 =A0 =A0 =A0 IconIndex:=3D0, IconLabel:=3D _
> =A0 =A0 =A0 =A0 "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.
>
>
>
> > > > My easy recorded Macro, would like to replace the "c:\...." with wha=
t
> > > > comes from Jim's, but can't figure it out:
>
> > > > Sub InsertObject()
> > > > =A0 =A0 ActiveSheet.OLEObjects.Add(Filename:=3D _
> > > > =A0 =A0 =A0 =A0 "c:\files\file.abc", Link:=3DTrue, _
> > > > =A0 =A0 =A0 =A0 DisplayAsIcon:=3DTrue, IconFileName:=3D _
> > > > =A0 =A0 =A0 =A0 "C:\WINDOWS\Installer
> > > > \{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
> > > > =A0 =A0 =A0 =A0 IconIndex:=3D0, IconLabel:=3D _
> > > > =A0 =A0 =A0 =A0 "c:\files\file.abc").Select
> > > > End Sub- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -