OutputTo MSExcel Password

[benchpolo]

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

[Rabbit]

Just make sure you have add a reference to the Excel Objects library.

Function SetXLPW()
    Dim appXL As Excel.Application
    Dim wkbkXL As Workbook
 
    Set appXL = Excel.Application
    Set wkbkXL = appXL.Workbooks.Open("C:\test.xls")
 
    With wkbkXL
        .Password = "Test1"
        .Save
        .Close
    End With
End Function
 

[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.

Function SetXLPW()
    Dim appXL As Excel.Application
    Dim wkbkXL As Workbook
 
    Set appXL = Excel.Application
    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
 

[Rabbit]

Since, these .xls files are dynamic will the code below work? Thanks.

Function SetXLPW()
    Dim appXL As Excel.Application
    Dim wkbkXL As Workbook
 
    Set appXL = Excel.Application
    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
 

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.