VBA in Access to close extra instance of Excel running

[sagain2k]

I'm asking this in both Excel and Access forums: I have a button on an Access switchboard form that when clicked runs a query that outputs the results into an Excel workbook called Courses.xls, replacing the previous file of the same name (saving the result).

What it's doing is creating a whole new instance of Excel running, despite the fact that Excel was already open. This creates problems for me since Excel macros that need to be run that belong to a workbook from the first occurrence Excel aren't able to switch to the Courses.xls workbook since it "belongs" to a diffrent occurence of Excel.

What code can I add to this so it will either close Excel after creating this Courses.xls workbook, or have it be able to create the workbook without having to open up a new occurrence of Excel? Any ideas welcome...thanks!

Here's the Access code for the Switchboard form button:

Private Sub cmdCourses_Click()
On Error GoTo Err_cmdCourses_Click

'THIS CREATES THE COURSES.XLS FILE AND REPLACES THE OLD FILE
'BUT OPENS A NEW OCCURRENCE OF EXCEL
DoCmd.OutputTo acQuery, "qryCourses", "MicrosoftExcel(*.xls)", _
"q:\qryResults\Courses.xls", True, ""

Exit_cmdCourses_Click:
Exit Sub

Err_cmdCourses_Click:
MsgBox Err.Description
Resume Exit_cmdCourses_Click
End Sub

 

[BrianB]

This code transfers data to an Excel workbook from an Access table without opening Excel. To achieve this you will need to make your Access query a "Make Table Query" via the Query menu in design mode.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
    "MYTABLE", "MYTABLE.XLS", True, ""

 

[Norie]

To achieve this you will need to make your Access query a "Make Table Query" via the Query menu in design mode.

Brian

ASFAIK you don't need to do this.

From Access Help:

Syntax

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

The TransferSpreadsheet method has the following arguments.
...
tablename A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into,
export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

 

[BrianB]

There are numerous things in MS Office that are supposed to work, and may even work when originally set up, but they do not always work every time over a long period - as you are discovering.

My solution is the most stable one I have discovered after many years experimentation.

 

[Norie]

Brian

What exactly is your solution?

Is it to run the make table query then export the table rather than just exporting the query?

Surely that adds to processing time and also the make table could fail if there was a problem with the original query.

 

[BrianB]

Is it to run the make table query then export the table rather than just exporting the query?

Yes.

Surely that adds to processing time

In the long run - No, because it makes for less hassle when things go wrong. and believe me they do.

One of my tasks for the past 2 years has been to run a similar process on the first 10 days of each month. The VB module code has not changed. I extract a text file to Excel, put it into Access to process, then back to Excel. It might seem longwinded, but things frequently go wrong - usually some sort of corruption in the files or Text/Number formatting problems. It takes a couple of minutes to find the fault, rectify, and continue. When things go right, 20 minutes overall to do a job that used to last all day. If it goes to 30, so what ?

For example, if the final worksheet corrupts it can be remade in a couple of minutes by exporting the file manually - which keeps me within my deadline, and remakes the sheet ready for next time too.

and also the make table could fail if there was a problem with the original query

Don't see your point. It would fail anyway then. You can check your table to see if the query is correct. (I include code to do this before exporting). With this link in the "chain" you know there is no Excel problem yet. So you only have to post your question in the Access forum.

 

[Norie]

Brian

Maybe I'm against the make table bit because some of the make table and append queries that I have to run each month can take over an hour each.