Strange problem exporting query to excel

[Ceebaby via AccessMonster.com]

Hi all

I have a query in an access 2003 database which is exported to excel to use
as the datasource for mail merges on a ntetworked drive.

If it is exported manually, the query is exported and overwrites the existing
excel file is which is fine.

However I wanted to automate this and used the following:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel7, "qry mailmergeowners", _
"X:\Housing\HousingRen\stock\Empty\STANDARD LETTERS\QRY MailMergeOwners.xls",
True

When it is run, I get an error message saying it cannot update the "address
field" and the field is not updatetable (which is a concantenated field of
address lines in the query).

Any thoughts on what I am doing wrong. Any help much appreciated on this.
Cheers
Ceebaby
London

 

[J_Goddard via AccessMonster.com]

Hi -

You are using the TransferSpreadsheet to export data, but you did not
indicate that is was for export. TransferSpreadsheet defaults to Import (see
MS Access Help), so you need to use:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qry
mailmergeowners", _
"X:\Housing\HousingRen\stock\Empty\STANDARD LETTERS\QRY MailMergeOwners.xls",
True

HTH

John

 

[Ceebaby via AccessMonster.com]

Hi John

Many thanks for your response, had already left the office so did not see
your response until today. Duh, can't believe I missed that. It worked
perfectly.

Have a good day.
Cheers once again
Ceebaby
London

Hi -

You are using the TransferSpreadsheet to export data, but you did not
indicate that is was for export. TransferSpreadsheet defaults to Import (see
MS Access Help), so you need to use:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qry
mailmergeowners", _
"X:\Housing\HousingRen\stock\Empty\STANDARD LETTERS\QRY MailMergeOwners.xls",
True

HTH

John

Hi all

[quoted text clipped - 18 lines]

Ceebaby
London