1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel object required

Discussion in 'Business Applications' started by roe_185, Sep 20, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. roe_185

    roe_185 Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    4
    i have copied this macro from Access, were it worked, to excel changing the appropriate addresses.


    Sub openaccess()

    On Error GoTo Err_openaccess_Click

    Dim stAppName As String

    ActiveWorkbook.save

    stAppName = "C:\Program Files\Microsoft Office\Office10\msaccess.exe C:\Shark-bulk-stationery\sales.mdb"
    Call Shell(stAppName, 1)

    DoCmd.Quit

    Exit_openaccess_Click:
    Exit Sub

    Err_openaccess_Click:
    MsgBox Err.Description
    Resume Exit_openaccess_Click

    End Sub



    When i run this macro Access opens as "read only" because it is it is linked to Excel and Excel can not shut down because it comes up with a message box saying "object required"
    What is this object Excel requires?
    I am running windows XP s/p2 and i am running officeXP s/p1
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    YOu might want to Google for:

    "open access" database from Excel vba

    I found several code samples, none of which use "shell"
     
  3. roe_185

    roe_185 Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    4
    Thankyou. I have done a google search and came up with the following code that seems to work

    Macro Code:
    The macro code looks like this:
    Global oApp As Object
    Sub OpenAccess()
    Dim LPath As String
    Dim LCategoryID As Long
    'Path to Access database
    LPath = "C:\Test\Testing.mdb"
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
    'Open form called Categories filtering by CategoryID
    LCategoryID = Range("A2").Value
    oApp.DoCmd.OpenForm "Categories", , , "CategoryID = " & LCategoryID
    End Sub


    Your help is very much appreciated
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Glad you got it. I did see that one, as well as several others. I'm not a real coder or anything, but I see lots of code and yours didn't look like theirs. :)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/276010

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice