Im****t from Protected Excel File

This is a multipart message in MIME format.
--=_alternative 005EFF76C1257495_=
Content-Type: text/plain; charset="US-ASCII"

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 ex****t 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 im****t the data but 
when I use this I still need to enter the password manually twice.
I would like to be able to im****t this data in the background.

Ex****t 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

Im****t Code (not working OK):

Public Sub Im****tProtected(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 acIm****t, acSpreadsheetTypeExcel9, "Im****t", 
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 ex****t the 
data in another way and keep the data hidden?

Regards,
Peter
--=_alternative 005EFF76C1257495_=
Content-Type: text/html; charset="US-ASCII"


<br><font size=2 face="sans-serif">Hi,</font>
<br>
<br><font size=2 face="sans-serif">I have a database from which I send
data to several other databases.</font>
<br><font size=2 face="sans-serif">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.</font>
<br><font size=2 face="sans-serif">I have managed to ex****t the data out
of the central database into excel and to password protect this file on
open.</font>
<br>
<br><font size=2 face="sans-serif">I have also found some code on the
internet
on how to im****t the data but when I use this I still need to enter the
password manually twice.</font>
<br><font size=2 face="sans-serif">I would like to be able to im****t this
data in the background.</font>
<br>
<br><font size=2 face="sans-serif">Ex****t code (working OK) :</font>
<br>
<br><tt><font size=2>Public Sub SaveXls(sFile As String)<br>
 &nbsp; &nbsp;On Error GoTo Err_Testing<br>
<br>
 &nbsp; &nbsp;Dim xlApp As Object<br>
 &nbsp; &nbsp;Dim xlSheet As Object<br>
<br>
 &nbsp; &nbsp;Set xlApp = CreateObject(&quot;Excel.Application&quot;)<br>
 &nbsp; &nbsp;Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)<br>
<br>
 &nbsp; &nbsp;With xlApp<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;'Password protext the active worksheet<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.ActiveSheet.Protect
&quot;xxx&quot;,
UserInterfaceOnly:=True<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;'Password protect the workbook strcuture<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.ActiveWorkbook.Protect
&quot;xxx&quot;,
Structure:=True, Windows:=False<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;'Password protect the file [OnOpen
password]<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.DisplayAlerts = False<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.ActiveWorkbook.SaveAs
FileName:=sFile,
Password:=&quot;xxx&quot;, WriteResPassword:=&quot;xxx&quot;<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.DisplayAlerts = True<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.ActiveWorkbook.Save<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Application.ActiveWorkbook.Close<br>
 &nbsp; &nbsp; &nbsp; &nbsp;.Quit<br>
<br>
 &nbsp; &nbsp;End With<br>
<br>
 &nbsp; &nbsp;Set xlApp = Nothing<br>
 &nbsp; &nbsp;Set xlSheet = Nothing<br>
<br>
Exit_Testing:<br>
 &nbsp; &nbsp;Exit Sub<br>
<br>
Err_Testing:<br>
 &nbsp; &nbsp;MsgBox Err.Number &amp; &quot; - &quot; &amp;
Err.Description<br>
 &nbsp; &nbsp;Resume Exit_Testing<br>
End Sub</font></tt>
<br>
<br><font size=2 face="sans-serif">Im****t Code (not working OK):</font>
<br>
<br><font size=2 face="sans-serif">Public Sub Im****tProtected(strFile As
String, strPassword As String)<br>
Dim oExcel As Object, oWb As Object<br>
Set oExcel = CreateObject(&quot;Excel.Application&quot;)<br>
Set oWb = oExcel.Workbooks.Open(FileName:=strFile,
Password:=strPassword)<br>
DoCmd.TransferSpreadsheet acIm****t, acSpreadsheetTypeExcel9,
&quot;Im****t&quot;,
strFile, -1<br>
oWb.Close SaveChanges:=False<br>
oExcel.Quit<br>
Set oExcel = Nothing<br>
End Sub </font>
<br>
<br><font size=2 face="sans-serif">Can someone help me on this issue or
inform me on how I can ex****t the data in another way and keep the data
hidden?</font>
<br>
<br><font size=2 face="sans-serif">Regards,</font>
<br><font size=2 face="sans-serif">Peter</font>
--=_alternative 005EFF76C1257495_=--