Exporting from MS Access to a protected spreadsheet

[taps54]

I would like to know how to export a table to a specified spreadsheet that is
password protected. I know the password for the spreadsheet. Basically I
have the spreadsheet protected with a password and when I try to export the
access table to it i get an error stating that the data cannot be
overwritten. is there some way to embed the password in the transfer
spreadsheet code to allow the data to be exported and maintain the protection
when the sheet is opened within excel.

DoCmd.TransferSpreadsheet acExport, 8, "weeklyclist",
"\\tsyr1\shared\BCS\CLIST_Approval\approval.xls", True, "sheet1!a620000"

 

[Clifford Bass via AccessMonster.com]

Hi,

Three thoughts here:

1) Use automation to open the document in Excel and manipulate the data
directly.

Dim appExcel As New Excel.Application

appExcel.Workbooks.Open _
"workbk.xls", , , , "Password"
' Code to update the contents of the
' appropriate part of the appropriate sheet
' instead of the TransferSpreadsheet command
appExcel.Quit

2) Put the code to update the spreadsheet inside the spreadsheet and run
it from within Excel.

3) Use a query from within the spreadsheet to pull the data into the
spreadsheet from the Access table(s) and set it to auto-refresh when the
spreadsheet is opened.

Clifford Bass