What is the VBA code to insert an Object into an Excel File
Hi,
I have recently been requested to create a button on an Excel spreadsheet that will insert an Object (usually a MS Word Document but not always) into the Spreadsheet as as Object. The user would need to browse for the document and select the file, then the file would attach and be displayed as an icon.
In short, the smae as using the Insert > Object >Create From File (Display as Icon) functionalily that is available from the Standard Toolbar.
My VBA skills are extremely limited and I would appreciate any assistance that I can get on this one.
Thanx in advance,
Amanda
Hi Shasur, thanx fo rthe code but unfortunately I was able to work that one out previously. the issue that I have is that the document that is attached will need to be selected so I cannot have a hard coded Filename in place. I was wanting code that would open the Browse window and the user could select the file.
Hi,
use the common dialog version 6.0 control.
then write the following code either in a Macro or on a button click.
dim FPath as string
CD1.ShowOpen
FPath = CD1.Filename
If FPath <> "" Then
ActiveSheet.OLEObjects.Add(Filename:=FPath, Link:= _
False, DisplayAsIcon:=True, IconIndex:=0, IconLabel:=FPath).Select
Range("G23").Select
End If
This code worked well for me in one of my VBA programs on Excel.
Private Sub Browsebttn_Click()
dim UF as variant
UF = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xls),*.xls", Title:="DDTS Excel Files")
txtfile.Text = UF
End Sub
it opens a browse window. Then you'll need another button to load the file they selected.
Private Sub loadbttn_Click()
Workbooks.Open Filename:=UF
End Sub
Or you could always have it loaded by the first button.
Good luck!
Are you sure Amanda that you are using CD1 within the scope of the object. At times you would have declared CD1 in a form and would be calling the object in some Bas Module.
This type of error occurs, when the object could not be found.
Regards
Shasur
