Export from Access to Excel with Password

[AndrewKent]

Hi there,

I have automated some simple reports within Access to save them in Excel format:

Function Extract_MissingCCNumbers()
'   =============================================================================================
'   Procedure written by: Andrew Kent
'   =============================================================================================
    DoCmd.OutputTo acOutputQuery, "qryExternalExtract_MissingCC_Edinburgh", _
    acFormatXLS, strReportsPath & "Missing Cost Centre Numbers (Edinburgh - " & strCurrentDate & ").xls"
    DoCmd.OutputTo acOutputQuery, "qryExternalExtract_MissingCC_Glasgow", _
    acFormatXLS, strReportsPath & "Missing Cost Centre Numbers (Glasgow - " & strCurrentDate & ").xls"
    DoCmd.OutputTo acOutputQuery, "qryExternalExtract_MissingCC_Newcastle", _
    acFormatXLS, strReportsPath & "Missing Cost Centre Numbers (Newcastle - " & strCurrentDate & ").xls"
End Function

Is anyone able to help me update this so that when data is exported, the Excel document is password protected.

Kind regards,

Andy

 

[RoryA]

You would have to actually automate Excel, open each workbook and save it with a password.

 

[AndrewKent]

Hi, yeah in the end I ended up extracting the data using VBA in Access then preparing the file using VBA in Excel. Thanks for your help!