Working with Excel from Access (Access 97 SR2)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Thanks, Hans!

One back at you:

Reply | Quote