Set Password Encyption for Excel file with Access VB

[AccessNub]

I am trying to use Access VB to open an excel file and encrypt it then close it. The code will open the file, and I am using SaveAs to apply a Password, these work without issue. However when using .SetPasswordEncryptionOptions it doesn't seem to do anything. I don't get any error, but when I open the excel file to confirm the change, it hasn't been set.

Any help on how to make this work would be great. TIA

Dim xlExcelApp  As Excel.Application
Dim xlWorkbook As Excel.Workbook
Set xlExcelApp = New Excel.Application
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlExcelApp.Workbooks.Open(FileName, 0, True)

    xlWorkbook.SetPasswordEncryptionOptions "Microsoft Enhanced Cryptographic Provider v1.0", "RC4", 128, True

    xlWorkbook.SaveAs FileName2, , Password

 

[NigelShaw]

Hi

Just a thought but don't you have to move to the document to do that? I recall something in Access where I wanted a form from a different application to do something but to allow that, I had to move to it through vba.

I may be wrong but I would think you have to move to the excel object itself in order to make the change...

Cheers. N

 

[AccessNub]

Thanks guys,

I figured out the problem, sort of a bone-head thing that I did while creating my Excel exporter. The Excel file in question was created by Access OutputTo, what I didn't do was specify that I wanted it to use the 97+ file format. the older Excel doesn't support encryption so naturally, trying to set it did nothing.