Close file that was inserted in excel as object as an icon

[emmieh]

I need some help on a VBA code. I want to insert other files in an excel file which is displayed as an icon and give the icon a specific name. I used this code:

Dim fn
fn = Application.GetOpenFilename
If fn = False Then
Exit Sub
End If

name = InputBox("give the file a name")

ActiveSheet.OLEObjects.Add(Filename:=fn, Link:=False, DisplayAsIcon:=True, IconFileName:= _
"C:\Program Files\Internet Explorer\iexplore.exe", _
IconIndex:=4, IconLabel:=name).Select
Selection.Left = 1000
End Sub

The file that is inserted opens in the background when it is inserted. It is not visible but it remains open. I would like the macro to automatically close the file without saving changes after it was inserted in the excel file .

Does anybody know how to add this to the code?

thank you for the help!!

 

[Andrew Poulsom]

Welcome to the Board.

Is it an Excel workbook that is being inserted? This closes it, but there also seems to be a copy open.

Sub Test()
    Dim fn
    fn = Application.GetOpenFilename
    If fn = False Then
        Exit Sub
    End If
    Name = InputBox("give the file a name")
    ActiveSheet.OLEObjects.Add(Filename:=fn, Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\Program Files\Internet Explorer\iexplore.exe", _
        IconIndex:=4, IconLabel:=Name).Select
    Selection.Left = 1000
    fn = Right(fn, Len(fn) - InStrRev(fn, "\"))
    Workbooks(fn).Close False
End Sub

 

[emmieh]

Hi Andrew,
it can actually be any kind of file that is inserted, i think that makes it so hard!

 

[Andrew Poulsom]

Can you give an example? For me it's only Excel workbooks that are opened.

 

[emmieh]

e.g. I want to insert a Word file in the excel file. You can do this in excel through Insert--> Object --> Create from file --> Display as icon and browse to the document you want to insert. The code I posted does this too. If you insert a word file, this is opened on the background when it is inserted. You do not see Word as an active application but it is running at the background. It would like the Word application to close after the file was inserted. But this should also happen with acrobat reader when a pdf file is inserted. Is that clear enough?

 

[Andrew Poulsom]

How do you know Word is running in the background? I can't see it as an Application or a Process in Task Manager. The same applies to Acrobat Reader.

 

[emmieh]

If i run the macro I can see a process running in the Task Manager, I see e.g. Winword.exe. I cannot explain why this does not happen when you run the Macro.

 

[Andrew Poulsom]

If i run the macro I can see a process running in the Task Manager, I see e.g. Winword.exe. I cannot explain why this does not happen when you run the Macro.

Neither can I. I am using Excel 2000.