none
VBA language to ask an object to the user and insert RRS feed

  • Question

  • Hi everyone,

    I use a lot of time your forum, but it's the first time I ask a question :)

    I'm not a very good  programer and I made a lot of big mistake.

    My question is, I would like to add a button in a excel program with a macro who ask to the user the address of another file and then put this this file as an object in the document under the button.

    I made some search and try to write my first code (without the rec method :p) but it's not work at all

    Private Sub Test_code2()
       
        Range("B50").Select 'under the button

       Dim PJ As Variant

       PJ = Application.GetOpenFilename() 'open a window to ask the file to choose
         
        txtfile.Text = PJ
        
        ActiveSheet.OLEObjects.Add(Filename:="PJ", Link:=False, _
            DisplayAsIcon:=True, IconFileName:= _
            "PJ", IconIndex:=0, IconLabel:= _
            "PJ").Select ' put the file selected as an object like a icon

    End Sub

    Wednesday, May 21, 2014 3:56 PM

Answers

  • Re:  add file object to worksheet

    Your code is almost there. It just req'd a minor change...
    '---
    Private Sub Test_code3()
      Dim PJ As Variant

      Range("B50").Select
       PJ = Application.GetOpenFilename()

     ' the user might cancel
       If PJ = False Then Exit Sub

     ' I assume this is a textbox on a userform
     ' Me.txtfile.Text = PJ

     ' Removed quote marks from around PJ in two spots   <<<<
    '  Removing just the first set of quotes also works.
      ActiveSheet.OLEObjects.Add(Filename:=PJ, Link:=False, _
                      DisplayAsIcon:=True, IconFileName:= _
                      PJ, IconIndex:=0, IconLabel:="PJ").Select
    End Sub
    '---

     Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)

    • Marked as answer by Pier_joseph Thursday, May 22, 2014 7:49 AM
    • Edited by James Cone Tuesday, October 18, 2016 5:02 PM
    Wednesday, May 21, 2014 7:11 PM

All replies

  • Re:  add file object to worksheet

    Your code is almost there. It just req'd a minor change...
    '---
    Private Sub Test_code3()
      Dim PJ As Variant

      Range("B50").Select
       PJ = Application.GetOpenFilename()

     ' the user might cancel
       If PJ = False Then Exit Sub

     ' I assume this is a textbox on a userform
     ' Me.txtfile.Text = PJ

     ' Removed quote marks from around PJ in two spots   <<<<
    '  Removing just the first set of quotes also works.
      ActiveSheet.OLEObjects.Add(Filename:=PJ, Link:=False, _
                      DisplayAsIcon:=True, IconFileName:= _
                      PJ, IconIndex:=0, IconLabel:="PJ").Select
    End Sub
    '---

     Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)

    • Marked as answer by Pier_joseph Thursday, May 22, 2014 7:49 AM
    • Edited by James Cone Tuesday, October 18, 2016 5:02 PM
    Wednesday, May 21, 2014 7:11 PM
  • That' awesome (and look like magic)

    Thank you so much!!

    Thursday, May 22, 2014 7:50 AM