Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open/Close Excel Document from Access (97 SR2)

    Why would I want to do this? Simple. I've got an excel workbook that automatically compiles sets of data through VBA... So, my question is, how can I open this excel document from Access?

    That might not be the challenge though, once the workbook is open, I need Access to take a nap while Excel completes it's processes. I can use a sleep command, but i'd rather use some kind of trigger... Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  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/Close Excel Document from Access (97 SR2)

    Take a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=58031&page=& view=&sb=&o=&vc=1#Post58031>this thread</A> for a link to the automation files for 97 and 2000.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close Excel Document from Access (97 SR2)

    Unfortunately, that link only contains a direct download for Word.. do you know where I might find specific details for Excel?

    Is this the only way to call an Excel Spreadsheet from Access?

    Thanks Charlotte!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Open/Close Excel Document from Access (97 SR2)

    "The Access Developers Handbook" (Ken Getz et. al.) has Automation examples for Word, Excel and Powerpoint as well as a fair bit of how-to info. I believe Helen Feddema's new book "Access 2002 Inside Out" has some good into, but my copy is in my home office. Also KB article Q129304 gives a fair bit of info on driving Excel from Access9x. The bottom line is that once you get a session between the two apps going, you can just about anything you could do if you were working inside Excel. There are some syntax changes that have to occur so VBA knows you are talking to Excel via Access, but they seem pretty intuitive (at least to me <img src=/S/grin.gif border=0 alt=grin width=15 height=15> - I've been at it for several years). Hope this helps.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close Excel Document from Access (97 SR2)

    Here's a chunk of code (names changed to protect the innocent) that has worked for me. I think it can be adapted/embellished fairly easily to meet your needs. If I remember correctly (I haven't used it in a while), Access does wait since you're initiating the Excel VBA subroutine call from Access.
    <font face="Georgia">
    Dim appExcel As Excel.Application

    Set appExcel = New Excel.Application
    appExcel.Visible = True

    ' The following is not needed if your VBA routines are in your workbook, but note that this
    ' is not opened automatically when Excel is launched this way
    appExcel.Workbooks.Open "C:Program FilesMicrosoft Office 2000OfficeXLStartpersonal2000.xls"

    appExcel.Workbooks.Open "C:My DocumentsMyExcelWorkbook.xls"

    ' 'ExcelRoutines' is the Excel VBA module name and 'MyExcelVBaRoutine' is
    ' the subroutine/function name
    appExcel.Run "Personal2000.xls!ExcelRoutines.MyExcelVBARout ine"
    ' or appExcel.Run "MyExcelWorkbook.xls!ExcelRoutines.MyExcelVBARouti ne"
    ' if your routines are in your workbook

    ' Done with Excel
    appExcel.Quit
    </font face=georgia>

    Hope this helps.

Posting Permissions

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