Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Question Unanswered: Invalid Procedure Name in function module embedding spreadsheet in MS Access

    Hi All,

    I am relatively new at programming VB in MS Access. I wanted to test creating a function to embed an Excel spreadsheet in a form in Access. Well, I found some code online that would do it so I could understand and learn how to create a function like that one, but after following all the steps on the example, I got an "Invalid Procedure Name" error. I was wandering If any of you guys could give me some pointers so I could understand the error, and how to actually make the code work.
    Here is the code I found:

    from:
    http://casadebender.com/reference/ot...ers/ba11_3.htm

    Creating an Empty Embedded Object

    You can create an empty embedded OLE object in which the user can enter data. For example, you can embed an empty Microsoft Excel worksheet in a Microsoft Access form.
    The following examples create a new embedded Microsoft Excel spreadsheet in an unbound object frame. To try these examples, first enter the following code in a standard module and save the module. This function creates a new form with an empty unbound object frame which you can use to try the examples in this section and the next section of this chapter.


    Function CreateFormWithFrame(strControlName As String)
    Dim frm As Form, ctlFrame As Control, ctlCommand As Control
    Dim intLeft As Integer, intRight As Integer
    Set frm = CreateForm
    Set ctlFrame = CreateControl(frm.Name, acObjectFrame)
    intLeft = ctlFrame.Left + ctlFrame.Width + 200
    intRight = 200
    Set ctlCommand = CreateControl(frm.Name, acCommandButton, , , , _
    intLeft, intRight)
    With ctlCommand
    .Name = strControlName
    .Caption = strControlName
    .OnClick = "[Event Procedure]"
    End With
    DoCmd.Restore
    End Function


    Once you’ve saved this code, run it in the Debug window by entering the following line of code and pressing ENTER.

    CreateFormWithFrame(“EmbedEmpty”)

    After Running it I got the error "Invalid Procedure Name"


    Microsoft Access creates a new form with an unbound object frame and a command button named EmbedEmpty. Open the form module and enter the code in the following example. Then switch to Form view. When you click the EmbedEmpty button, Microsoft Access embeds a new, empty Microsoft Excel spreadsheet in the unbound object frame. You can double-click the spreadsheet to edit it.

    Private Sub EmbedEmpty_Click()
    Dim ctl As Control
    Set ctl = Me!OLEUnbound0
    With ctl
    ' Enable control.
    .Enabled = True
    ' Set Locked property to False.
    .Locked = False
    ' Allow embedded objects only.
    .OLETypeAllowed = acOLEEmbedded
    ' Specify the OLE server and the type of object.
    .Class = "Excel.Sheet"
    ' Create an embedded object.
    .Action = acOLECreateEmbed
    End With
    End Sub


    In the preceding example, the embedded object you create is a brand new OLE object that has not been saved to disk. You can’t create a new linked object because a linked object must be a document that already exists in a file separate from your Microsoft Access database.

    Thanks in advance.

    George D

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Change it from a function to a Sub
    Code:
    Sub CreateFormWithFrame(strControlName As String)
    .....
    End Sub

  3. #3
    Join Date
    Apr 2008
    Posts
    5

    Nah, it didn't work

    Still getting the same error.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Where have you pasted the code?

  5. #5
    Join Date
    Apr 2008
    Posts
    5
    In a new module in Access VB that I named CreateFormWithFrame

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is the sub / function declared public in that module. I forget what the default visibility is in Access these days
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    There was not declared visibility on that function when I copied it from the site, I really don't know if there is a default visibility, I tried testing both but it gives me the same error even if I declare it as Public or Private. I think that the only way to figure this one is to try it out. I also noticed that it would not work with MS Access 2000 or MS Access 2003. I also noticed that the intellisense is letting me know that there is something wrong, it is not giving me hints, I had to dim ctlFrame as ObjectFrame and ctlCommand as CommandButton to make it work, but, allegedly, it is a function that already works and should have worked as is. I think I have all the libraries needed and there is only something missing, but, I really don't know.
    Last edited by George D; 05-23-08 at 08:19.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Default is public.

    I've copied and pasted exactly what you put in post #1 and it works fine (2003). Can you upload a db with this module that fails?

  9. #9
    Join Date
    Apr 2008
    Posts
    5

    Talking OOps, Nevermind.. It's working too

    Thank you very much for the help, you guys are amazing. It was just a beginner’s mistake, The function is working fine, I never would've tried to run it again without your help. I was using the wrong window to run the module. It is embarrassing, I really don't want to go into details, but I have to just for the record and because there could be some other goomba like me who makes the same mistake. Without too much laughter, this one goes for you:

    The debug window they mean in the example is the Immediate Window, not the Run Sub/UserForm. You can get to the "Immediate Window" by pressing Ctrl+G or go to view in the Access VB menu and select "Immediate Window", there you can run any procedure, just by adding a "?" at the beginning, so for the example you would enter:

    ?CreateFormWithFrame(“EmbedEmpty”)

    and press Enter.

    Thank you pootle flump and healdem

    Thank you,

    George D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •