Export Access query to Excel via Macro

[Jules Hoppenbrouwers]

In short I'd like a MS Project macro to copy the result of a query in
an MS Excel worksheet. I must admit that that VBA is quite new for me.

Manually the proces goes as follows:
1. I save the project as a Pivot Table
2. I run the Linked Table Manager in MS Access that gets the data from
three worksheets that where created in step 1.
3. The result of the quiry is copied (from MS Access) and must be
pasted in an other MS Excel worksheet.

These are the problems that I encounter:
ad 1. When I run Linked Table Manager from the Project VBA as follows:
strDB = "c:\myAccessDB.mdb"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDB
DoCmd.RunCommand acCmdLinkedTableManager 'Opens the Linked Table
Manager

Everytime you run the Linked Table Manager from the macro a new copy
is created from the Linked Excel Tables. Of course I can make a delete
statements which deletes the copy afterwards, but is this the way it
should be done?

ad 3. I don't seem to be able to actually copy the results from the
Access query as I would manually. This is how I do it manually;
In de object pane I select Queries. Then open my quiry which will show
me the results of the Linked Table Manager action. I select all rows
and I paste them in my Excel document. Their must be a way to fit this
in a macro. Can anybody tell me how?

To conclude I'd like to add that I also tried the
DoCmd.TransferSpreadsheet (DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "Task overview", "export.xls"). But this
leaves me with an Excel spreatsheet where some columns are not
included and others are relocated to somewhere else on this sheet.

 

[Van T. Dinh]

1. Not being familiar with MS Project but if the data is saved as a Table
in MS Project, you should be able to access the data from Excel directly
using MS Query rather than having to go via Access.

Perhaps, you should ask in Excel newsgroups how do you link MS Project data
to an Excel spread sheet.

2. If the Tables are already linked, you don't need to re-link the Tables
to access the new (data). So, I am not sure why you need to use the Link
Table Manager every time.

3. I don't have any problem with TransferSpreadsheet (especially export)
but are you aware that acSpreadsheetTypeExcel8 is Excel97 format?