Conversion from Access to a protected spreadsheet

[Tom]

Is it possible to convert a data table to a password protected Excel spreadsheet with a mixture of Call Shell/VBA code from a VBA application so you don't have to go through all the steps of exporting, bringing up the Excel application, etc, etc?:help

[stanl]

Is it possible to convert a data table to a password protected Excel spreadsheet

If by 'spreadsheet' you mean workbook; it is possible with ADO - however if it is a protected spreadsheet within a workbook you may be SOL.

I would use something like

SELECT INTO [sheetname$] IN "" [Excel 8.0;Database=" & App.Path & _
"\book1.xls;Password=mypassword] FROM [myAccessTable]"

which will require some tweaking. Stan

[Tom]

Thanks for the quick turn around, Stan. What I was wondering about is that "The Powers That Be" want me to create a table, convert the data table into an Excel spreadsheet, password protect the spreadsheet and send it out. I can convert the data table into an Excel spreadsheet with:

"Call Shell("C:\program files\microsoft office\office11\excel.exe C:\work\locations.xls", 1)"

I was wondering if I might be able to add a password to the spreadsheet by modifing the above code. Or have i been smoking too much cornsilk?

[Tom]

Yup, Stanl,
Been checkin' it out....and your right! It looks like I'm going to have to brush up on my ADO. I'll post it if I come up with an easy answer