Macro function - 'Output To'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have a macro which runs a query, which makes a table. I then want this
table to be output to a 'Data' page on an Excel spreadsheet held on our
network drive.

The Excel spreadsheet is called "Avoidables Report.xls" (stored at
Y:\Public\reports) and I would like Access to output the table "TP Avoidable
Report" into the sheet named "Data".

On the settings what do I need to put on the Output To settings in the macro?

I'm a complete newb at Access.
 
Adam,

Instead of OutputTo, use theTransferSpreadsheet action, with arguments:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-9 (or whatever is the default)
Table Name: "TP Avoidable Report"
File Name: "Y:\Public\reports\Avoidables Report.xls"
Has Field Names: No
Range: "Data"

Note:
This will overwrite sheet Data every time you run it.
If the only reason why you nee the table is so you can export it, then
you don't need it at all!Just change the make-table query to a simple
select one, and exporty that by putting its name in the Table Name
argument; Access will pick it up just fine. In this case, of course, you
don't need to run the query in an individual step first, the
TransferSpreadsheet action will do it all.
Finally, as a general comment: using drive mappings is not a very good
idea, in that if you change the mapping or try to run your macro on a
different machine with a different mapping, it will not work. Generally
it is advised to use UNC addressing, which is like:
\\ServerName\Folder1\Folder2\...\FileName.ext

HTH,
Nikos
 
I've tried something like this, but instead of overwriting it, it just
creates a new sheet and adds a number to the end. How do I get around this?
 
Back
Top