TransferSpreadsheet to a protected Excel-sheet

[Guest]

Hi!

Is there a way to do a DoCmd.TransferSpreadsheet to a protected Excel-sheet?
The password of the excel-sheet is Stefan and is called T_OE_Ideas_Queried.

Is this possible?

Kind regards, Stefan van der Hooft.

 

[John Nurick]

Hi Stefan,

If I had to do this I'd try the technique at
http://alexdyb.blogspot.com/2005/04/how-to-import-password-protected-excel.html
With luck it will work for exporting as well as importing.

Failing that - or if it's a protected worksheet rather than workbook -
I suspect you'll need to use automation to unprotect and save the
workbook. Then call TransferSpreadsheet, finally restore the protection.

This http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html may
help ensure that what you export ends up in the right place in the
worksheet!

 

[Guest]

Hello John,

Thanks for your reply but it still doesn't work! I can't rebuild the code on
my own to an export module.

Can you help me or does anyone else have suggestions?

Kind regards, Stefan van der Hooft.

 

[John Nurick]

This seems to work, though I've only tested it briefly.

Note that in this form it can easily be tricked into leaving the
workbook without its password. Defensive coding and error trapping could
reduce the likelihood of this happening but - as far as I know - there's
no way of eliminating it.

Public Sub ExportToPasswordedWorkbook(strFile As String, _
strPassword As String, strQryName As String)

Dim oExcel As Excel.Application
Dim oWb As Excel.Workbook

Set oExcel = CreateObject("Excel.Application")

'Open protected workbook and remove password
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
oWb.Password = ""
oWb.Close SaveChanges:=True

'Export
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQryName, strFile, -1

'Restore password
Set oWb = oExcel.Workbooks.Open(FileName:=strFile)
oWb.Password = strPassword
oWb.Close SaveChanges:=True
oExcel.Quit
Set oExcel = Nothing

End Sub