OutputTo MSExcel Password

[benchpolo]

I have a function the outputs file to MSExcel dynamically by date (ex. file_10292007.x ls). How do I embed an MSExcel password in MSAccess VBA?

[Rabbit]

Just make sure you have add a reference to the Excel Objects library.
[Code=vb]
Function SetXLPW()
Dim appXL As Excel.Applicati on
Dim wkbkXL As Workbook

Set appXL = Excel.Applicati on
Set wkbkXL = appXL.Workbooks .Open("C:\test. xls")

With wkbkXL
.Password = "Test1"
.Save
.Close
End With
End Function
[/Code]

[benchpolo]

So, I output the file to excel then call this function to embed the password?

[benchpolo]

Since, these .xls files are dynamic will the code below work? Thanks.
[Code=vb]
Function SetXLPW()
Dim appXL As Excel.Applicati on
Dim wkbkXL As Workbook

Set appXL = Excel.Applicati on
Set wkbkXL = appXL.Workbooks .Open("c:\temp\ contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls")

With wkbkXL
.Password = "Test1"
.Save
.Close
End With
End Function
[/code]

[Rabbit]

Since, these .xls files are dynamic will the code below work? Thanks.
[Code=vb]
Function SetXLPW()
Dim appXL As Excel.Applicati on
Dim wkbkXL As Workbook

Set appXL = Excel.Applicati on
Set wkbkXL = appXL.Workbooks .Open("c:\temp\ contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls")

With wkbkXL
.Password = "Test1"
.Save
.Close
End With
End Function
[/code]

You only need to put it in a separate function if you need to call it from multiple events. If you only need it in one place then you can inline it. That code won't work because the time will change. You'll have to store the filename in a temporary variable, before you create the file, and use it for both the creation of the file and when you open the workbook to set the password.