Adding password to exported excel files from access

[Piers]

I have a table of data that i export into individual files from a grouped field, so creating 40 files each named as the group filed with a period in the names. I would like a process that will add a password to each of these exported files, can anyone help please. this is the code i use to export the files

Private Sub Command4_Click()
 
  Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
  Set db = CurrentDb
 
  Set rs = db.OpenRecordset("SELECT distinct FundGroup FROM tbl_EFMMI_ReportFeeder ORDER By FundGroup;")
  '**** fieldname, consider this to be how the records are grouped within the dataset.
 
  strDt = [Forms]![Form1]![Period]
 
  rs.MoveLast
  rs.MoveFirst
 
  Do While Not rs.EOF
 
  strCrt = rs.Fields(0)
 
  Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT tbl_EFMMI_ReportFeeder.*  FROM tbl_EFMMI_ReportFeeder WHERE tbl_EFMMI_ReportFeeder.FundGroup = '" & strCrt & "';")
 
 
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\H\EFMMI\ " & strCrt & "_" & strDt & ".xls", True
 
  DoCmd.DeleteObject acQuery, "" & strCrt
 
  rs.MoveNext
 
  Loop
 
  rs.Close
  Set rs = Nothing
  Set db = Nothing
 
 
End Sub

[ADezii]

After each Query is Exported via the TransferSpreadsheet() Method, using Automation Code, Open the Exported Spreadsheet in Excel, and Set the Password for the Workbook to 'SECRET'. Save each Workbook, then Close it. Repeat for each iteration of the Loop. This works well for a single Workbook, but on 40, you'll have to provide the answer to that one! (LOL).

1. Set a Reference to the Microsoft Excel XX.X Object Library
2. In the General Declarations Section of your Form, Declare Object Variables to represent the Excel Application and an Excel Workbook.
   

   Dim appExcel As Excel.Application
   Dim wkb As Excel.Workbook
    

3. In the Form's Open() Event, create an Instance of the Excel Application. The idea here is to create a 'Single' Instance and work with it as opposed to Multiple Instances.
   

   Set appExcel = New Excel.Application

4. Execute the Code similar to that below:
   

   '****************************** Code intentionally omitted ******************************
    
   Do While Not rs.EOF
    
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & _
                               strCrt, "C:\H\EFMMI\ " & strCrt & "_" & strDt & ".xls", True
    
     Set wkb = appExcel.Workbooks.Open("C:\H\EFMMI\ " & strCrt & "_" & strDt & ".xls")
         wkb.Password = "SECRET"
    
         wkb.Save
         wkb.Close
   Loop
    
   '****************************** Code intentionally omitted ******************************
    
   appExcel.Quit
   Set appExcel = Nothing
    

5. When you Open any of these 40 Spreadsheets, you will have to supply the Password (SECRET).
6. Good Luck, and let us know how you make out.