Import from Protected Excel File

[peter.pallen]

Hi,

I have a database from which I send data to several other databases.
Because in the other databases the suppliers may only see their own data
and I was looking into how I could send the data to the suppliers in an
automated way without that they can see the data when opening the file.
I have managed to export the data out of the central database into excel
and to password protect this file on open.

I have also found some code on the internet on how to import the data but
when I use this I still need to enter the password manually twice.
I would like to be able to import this data in the background.

Export code (working OK) :

Public Sub SaveXls(sFile As String)
On Error GoTo Err_Testing

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

With xlApp

'Password protext the active worksheet
.Application.ActiveSheet.Protect "xxx", UserInterfaceOnly:=True

'Password protect the workbook strcuture
.Application.ActiveWorkbook.Protect "xxx", Structure:=True,
Windows:=False

'Password protect the file [OnOpen password]
.Application.DisplayAlerts = False
.Application.ActiveWorkbook.SaveAs FileName:=sFile,
Password:="xxx", WriteResPassword:="xxx"
.Application.DisplayAlerts = True

.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit

End With

Set xlApp = Nothing
Set xlSheet = Nothing

Exit_Testing:
Exit Sub

Err_Testing:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Testing
End Sub

Import Code (not working OK):

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import",
strFile, -1
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
End Sub

Can someone help me on this issue or inform me on how I can export the
data in another way and keep the data hidden?

Regards,
Peter