Export access to multiple password protected excel files

[graveyard]

Hi people.

have a problem here.

i was trying to export an access table to multiple excel files grouped by a field (country). Below is the code i have built into a command button in form so the export can be done by clicking on it

there is an issue here - i need access to export the data to password protected excel files. is there a way to do so?

i have read some related posts here and not too sure if i get whats advised - if i read correctly, some members here say this has to be done from excel by vba and that access cant do that (export to excel and at same time protect the files so anyone wanting to access the excel needs to have the password)?

Pls advise- appreciate any help here. thanks



Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim rsCountries As DAO.Recordset
Dim rsCountry As DAO.Recordset
Dim dDao As DAO.QueryDef
Dim lblStatus_OrigCaption As String
Dim strFile As String

With lblStatus
.Visible = True
lblStatus_OrigCaption = .Caption
.Caption = "Status: Extraction in progress ... "
Me.Repaint

Set dDao = CurrentDb.QueryDefs("data extraction By Country")

Set rsCountry = CurrentDb.OpenRecordset("SELECT distinct(table1.[country]) from table1 order by 1")

If rsCountry.EOF And rsCountry.BOF Then
Exit Sub
End If

While Not rsCountry.EOF
.Caption = "Status: Extraction in progress ... " & rsCountry.Fields(0)
Me.Repaint

strFile = GetDBPath & rsCountry.Fields(0) & "_table1.xls"
If File_Exists(strFile) Then
Kill strFile
End If
dDao.SQL = "SELECT * from table1 where ConsolidatedCCRraw.[country] = '" & rsCountry.Fields(0) & "'"
DoCmd.TransferSpreadsheet 1, 8, "data extract By Country", _
GetDBPath & rsCountry.Fields(0) & "_table1.xls", True

rsCountry.MoveNext


Wend
.Caption = "Status: All extract completed"
Me.Repaint
Set rsCountries = Nothing
Set rsCountry = Nothing

MsgBox "data extract By Country - Complete!", vbInformation + vbOKOnly, "data Extract"
.Caption = lblStatus_OrigCaption
Me.Repaint
.Visible = False
End With
End Sub

 

[boblarson]

You can't use TransferSpreadsheet to send it to a password protected sheet (unless you are putting it there to begin with BEFORE the password is set. To do the password you would need to use the Excel Common Object Model (COM) also known as Automation programming.

 

[graveyard]

hi bob, thanks for the reply. the files need to be overwritten over time so i guess the transfer spreadsheet wont work?

this sounds complciated. let me check on the EOM concept and get back . thanks again!

 

[AccessNub]

If the file needs to be overwritten, I personally would delete or archive the file, then run the exporter