Insert/Object/Create From File

[Ahh Dubya]

I have a macro that opens the Insert/Object dialogue box and selects display as Icon.

Code: Select all


Dim sFile As Dialog

Set sFile = Application.Dialogs(xlDialogInsertObject)
                sFile.Show , , , True



The dialog box opens on the "Create New" tab, can anyone tell me how I can force it to open on the "Create from File" Tab?

Thanks

[Don]

It doesn't look like you can do that. But, why do you need to do that anyway?

Also, here is a list of all of the arguments that you can pass for this:

Code: Select all

object_class, file_name, link_logical, display_icon_logical, icon_file, icon_number, icon_label

[User_5]

This should do it:

Code: Select all

 
Dim sFile As Dialog
  Set sFile = Application.Dialogs(xlDialogInsertObject)
  sFile.Show , True, True, True


Change the last two parameters to True or False depending on which checkboxes
you want checked when the Create Form File tab is open.

[Don]

Hey User_5, which version of Excel are you using? This does not work when I try it in Excel 2007.

[User_5]

Hi Don

Thanks for starting this forum, by the way. I am using the code on my netbook, which only has version 2003.
But I don't see why it won't work on later verions as the parameters are the same and in the same order.
What happened when you tried it? Did the Dialog box open at all?

If you forget the first comma after the .Show method , you'll get a run-time error message that says "Show method of dialog class failed".

Please try this pasting this and running this:

Code: Select all

Sub OpenInsertObjectDialogUsingConstant()
'Open the dialog box using the xlBuilt-in constant 259.
  Dim sFile As Dialog
  Dim sFileName As String
  sFileName = "C\Documents\"
  Set sFile = Application.Dialogs(259)       
  sFile.Show , sFileName, True, True
End Sub


The dialog box should open with the "Create from file" tab selected and both checkboxes checked. The File Name textbox should read "C\Documents\".

If this does not happen, then your version may have a different Constant for the dialog.
To check the constant number for this particular dialog, press F2 to open the Object Browser.
In the top DropDown, choose , <All libraries>.
Paste or enter this into the search textbox:

xlDialogInsertObject

Click on the binoculars.
Scroll to the bottom and you'll see the Const number. If its something other than 259, then use that constant
in the code above and run the code again.

Hope this helps.

[Don]

Yea no idea why, but this doesn't work either. When I run the code it just sort of freezes Excel like it would when you run a very big/long macro. It doesn't matter to me though since I never use this.

Anyway, thanks for the tip! Glad to have you here

[Ahh Dubya]

Thanks for the replies, guys. The reason for trying this was to make it slightly more fool proof when users are asked to attach a document. So far I still cannot make it work, excel just freezes, but it is not he end of the world if I cannot make it happen.

I have attached the whole macro so you can see the context in which it is being used.

Code: Select all


Sub ED_LOG_AUTO_FILL()

'=================================================================================================================
' ED_LOG_AUTO_FILL is a Macro that determines whether any attachments are going to be used and positions the page
' and cells such that it is ready to insert an Object
'=================================================================================================================

Dim Attachments As String                                         'Declares variable "Attachments" as a string
Dim BlankLine, Count As Integer                                   'Declares variables "BlankLine & Count" as Integers
Dim Msg, Style, Title
Dim sFile As Dialog

    Msg = "Do you want to add any attachments to this form ?"     'Define message.
    Style = vbYesNo                                               'Define buttons.
    Title = "ATTACHMENTS"                                         'Define title.
   
    Response = MsgBox(Msg, Style, Title)
   
    If Response = vbYes Then                                      'User chose Yes.
   
                Range("M41").Select                               'Selects Cell M41
                ActiveWindow.SmallScroll down:=5                  'Scrolls down so that M42 is in view
                Set sFile = Application.Dialogs(xlDialogInsertObject)
                sFile.Show , , , True
               
    Else
   
    Application.Run ("Auto_Fill")
   
    End If                                      ' User chose No and so carries on sending emails and updating logs.
     
End Sub



Once again thanks for your inputs.

[Don]

Yea I really don't know why it's not working on your end. It's also not working when I try it. I seem to be having the same issue as you. My solution would be to just use your original code, which also seems to work on my machine.