Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Excel from Access and run macro (Office 2000)

    I have several questions that I hope someone can help me with:

    1. What is the difference between GetObject and CreateObject......is it really as easy as it sounds? One will get a new instance of say an excel workbook and the other will open an existing workbook?

    2. I have been trying to use both GetObject and CreateObject to open excel from access and run a macro and I have not having success. I keep getting a Run-Time error 429: ActiveX component Can't Create Object. I have looked at this error on the microsoft site but none of the suggestions seem to help for how to fix it. Has anyone had any experience with this problem and have any suggestions.

    Thank you all for your help.
    Lu

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Excel from Access and run macro (Office 2000)

    Yes, it is that simple, but without seeing the code you're trying to use, it isn't possible to guess what the problem is with any accuracy.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel from Access and run macro (Office 2000)

    This is the chunk of code that I am using: When I run the commented out lines I get the run-time 429 error. When I run it the way it is now I get a run-time error 463 - Class not registered on Local Machine.

    I am sure there is something simple that I am missing or hope anyway.......



    Private Sub ShowExcel_Click()

    Dim strxls As String
    Dim appExcel As Excel.Application

    'Set appExcel = GetObject("C:Program FilesMicrosoft OfficeOffice10XLStartPersonal.xls", Excel.Application)

    Set appExcel = CreateObject("C:O_WorkTesDBBuild2.xls", Excel.Application)

    appExcel.Visible = True
    'strxls = "C:Program FilesMicrosoft OfficeOffice10XLStartPersonal.xls!tss_Run_MultiFun c_Edit"
    strxls = "C:O_WorkTesDBBuild.xls"
    appExcel.Workbooks.Open (strxls)

    Set appExcel = Nothing

    End Sub

    Thanks
    Lu

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Excel from Access and run macro (Office 2000)

    When using CreateObject, you just specify the class, in this case CreateObject("Excel.Application"). You don't specify a workbook here, but use the appExcel object to open a workbook:

    Private Sub ShowExcel_Click()
    Dim strXls As String
    Dim appExcel As Excel.Application
    Dim xlWorkbook As Excel.Workbook

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True

    strXls = "C:O_WorkTesDBBuild.xls"
    Set xlWorkbook = appExcel.Workbooks.Open(strXls)

    ' do something with workbook
    ' ...

    xlWorkbook.Close SaveChanges:=True ' or False, it's up to you
    Set xlWorkbook = Nothing
    appExcel.Quit
    Set appExcel = Nothing
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel from Access and run macro (Office 2000)

    The problem is on this line:

    Set appExcel = CreateObject("C:O_WorkTesDBBuild2.xls", Excel.Application)

    It should be:

    Set appExcel = CreateObject("Excel.Application")

    This will create a new instance of Excel. When you create a new instance, or even get an existing instance, you first need to create (or get) the reference to the instance and then open the workbook.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel from Access and run macro (Office 2000)

    Thanks everyone for your help.....I think I have it working.

Posting Permissions

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