Export Query to Excel with Encryption (1 Viewer)

[Jgr4ng3]

Hi all,

So the task in hand is that I need to email an encrypted xlsx sheet to a predefined party from Access.

I figure this is not possible to do immediately in access, but I thought it'd at least be possible to export an encrypted file.

At the moment, all I can do automatically is export the file, which leaves the encrypton and mailing open to human error which I'd rather avoid.

Does anyone have any ideas on how I can make this work, if at all?

Thanks in advance,
Jake.

 

[Ranman256]

export with the access command , then reopen it in excel for encryption.
NOTE, remember to add in the excel reference in VBE menu: tools, references.

Sub SavePassFile()
Dim vFile
Dim XL As Excel.Application

vFile = "c:\folder\file.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsMyQuery", vFile, True

Set XL = CreateObject("excel.application")
With XL
    .Application.DisplayAlerts = False
    .Workbooks.Open vFile
    .ActiveWorkbook.SaveAs vFile, xlOpenXMLWorkbook, "password"
    .ActiveWorkbook.Close
    .Quit
End With
Set XL = Nothing
End Sub