Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with Excel from Access (Access 97 SR2)

    Hi All,

    I'm attempting to work with Excel from Access. For the most part, it's going smoothly. But that is as long as I give control of Excel to the user at the end:

    Dim xlApp As New Excel.Application
    Dim xlsheet As New Excel.Worksheet

    xlApp.Workbooks.Open strExternalFile
    Set xlsheet = xlApp.Worksheets("Access Data")

    'Do stuff to play with the sheet

    xlApp.Visible = True
    Set xlsheet = Nothing
    Set xlApp = Nothing

    But I also want the option of doing stuff to multiple worksheets and not having them open at the end (or I'll end up with multiple Excels open at the same time, which is just not good on our standard P233s with 64meg of RAM ;-).
    I tried using this to close excel, but the first line generates an error:

    ' xlApp.Workbooks.Close
    xlApp.Quit

    The second line is fine, but I always get a prompt to save the file. Since there will be 12 workbooks created in this routine, I don't want the user to deal with saving the file. xlApp.Workbooks.saveas does not exist at all.

    Does anyone know how I can save and close the workbook and completely close Excel without having the user do anything?

    Thanks,

    Cecilia :-)

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

    Re: Working with Excel from Access (Access 97 SR2)

    A single workbook object has Save, SaveAs and Close methods. They are not methods of the Workbooks collection.

    I think you'd be better off by defining an Excel.Workbook object and several Excel.Worksheet objects within that workbook.

    Dim xlBook As Excel.Workbook
    Set xlBook = xlApp.Workbooks.Open(strExternalFile)
    ...
    xlBook.Close SaveChanges:=True

    If you want Excel to quit regardless of whthere unsaved workbooks are open, you can use

    xlApp.DisplayAlerts = False
    xlApp.Quit
    (normally, you'd set DisplayAlerts = True aftwerwards, but if you're quitting Excel that isn't important)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Excel from Access (Access 97 SR2)

    Access doesn't like this line:
    Set xlBook = xlApp.Workbooks.Open strExternalFile

    It thinks it doesn't need a file name at the end. I can't seem to find a way to open a specific book that way, which _should_ be the way to do it?

    Long explanation:

    Basically, what I'm trying to do is this: my co has given me four weeks to design a reporting databases and build a gazillion reports from a transactional database that was built years ago and there are few people left that know a heck of a lot about it. The people in the dept that I am doing this for already have excel spreadsheets with their end reports, but to use them they use this horrible manual process of moving the data from iSQL and other stuff and massaging it this way and that. Since I don't think I can both design an entire reporting database and all the reports in just four weeks, I'm going with the "good enough for now" approach and designing the reporting database and just filling the excel sheets until I have time to build proper Access reports.

    So basically, my routine is supposed to copy an existing excel spreadsheet (sort of a template), name it a particular way, then fill it with appropriate data. This has to happen for about 12 separate spreadsheets (so far). Since the reports are already in the workbooks, I'm done. All I have to do is figure out how to save the data when the excel workbook isn't passed to the user....

    Cecilia :-)

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

    Re: Working with Excel from Access (Access 97 SR2)

    Cecilia,

    I was getting tired and I was typing directly into the reply window instead of in an Access module. I already noticed the mistake a short while ago and corrected it silently in my post.

    It should have been

    Set xlBook = xlApp.Workbooks.Open(strExternalFile)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Excel from Access (Access 97 SR2)

    Oooh! I thought I had tried that, but evidently I had not. It works very nicely.

    Thanks, Hans!

    One back at you: <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

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