Export to Excel with a password

[JohnnyB]

Hello All,

I have to create a process that breaks down a dataset by email addresses and puts all of the info for each address into a separate Excel workbook. I've got that taken care of. My process creates around 50 separate .xls files that will be emailed out to the 50 individuals. My users want each workbook to be password protected using a convention like "AWF06032010", basically a set of letters concatenated to the current date in mmddyyyy format. All of the workbooks will have the same password for that day. How do I set the password for these spreadsheets in my VBA code? I've been scouring the 'net for Excel automation to use in Access but aren't having much luck. I'm using Office 2007.

Thanks for any help or tips,

John

 

[Trevor G]

John Look at this snip of code and see if you can adapt it to be included in your code

ActiveWorkbook.Password = "AWF" & Format(Date,"ddmmyyyy")

 

[JohnnyB]

Trevor,

THAT WORKED!! Thanks a lot! I had one minor cultural glitch. We Yanks use a date format of "mmddyyyy" and I was using that for my password. Then I looked at your code and saw that you had "ddmmyyyy". I entered the password that way and my spreadsheet opened just fine. I'll have to make that change in the date formatting code for my Yankee brothers and sisters and we'll be good to go!

Thanks again!

John

 

[Trevor G]

Excellent news John,

Enjoy the weekend across the pound.