Open protected workbook from Access 1

[geralf]

Hi,

I'll try this here since it uses bothAccess and Excel.

I have automated transfer of records from Access to Excel, using he Excel Object Library. I use the GetObject method to get access to a specific Excel file. This file is protected with a password (which is known). How can I pass this to the workbook object, so the user don't get bothered with the 'password-dialog' box ?

I'm using Access 2000/Excel2000 and Win2000.

Any help greatly appreciated



Regards
Gerhard

 

[VBslammer]

The Excel model exposes all the necessary objects to do what you need. This example shows how to open a password-protected workbook, unprotect the workbook and all the sheets (if all the sheets use the same password). If you only have the 'Open' password to deal with then the function could be simplified:

Function OpenProtectedWorkBook(ByVal strWorkBookFile As String, _
                                ByVal strWorkBookOpenPWD As String, _
                                ByVal strWorkBookProtectPWD As String, _
                                ByVal strSheetProtectPWD As String) As Boolean
On Error GoTo ErrHandler

  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  
  Set xl = New Excel.Application
  
  Set wb = xl.Workbooks.Open(filename:=strWorkBookFile, _
    Password:=strWorkBookOpenPWD, ReadOnly:=False, AddToMru:=False)
  
  wb.Unprotect strWorkBookProtectPWD
  
  For Each sht In wb.Sheets
      sht.Unprotect strSheetProtectPWD
  Next sht
  
  OpenProtectedWorkBook = True
  xl.Visible = True
    
ExitHere:
  Exit Function
ErrHandler:
  Select Case Err
    Case 1004           'Invalid password
      MsgBox "Password(s) invalid, please try again"
      xl.Quit   'Clean up app.
      Resume ExitHere
    Case Else
      MsgBox "Error: " & Err & " - " & Err.Description
      xl.Quit   'Clean up app.
      Resume ExitHere
  End Select
End Function

VBSlammer

Unemployed in Houston, Texas

 

[geralf]

Excellent!

Just what I needed. I have one additional question. I have a function that returns trur or false, whether an instance of Excel is running. How do I open a workbook in the same instance?

Thanks for your help!


Regards
Gerhard

 

[VBslammer]

You can still use your GetObject or CreateObject logic to instantiate xl. Instead of this:

Set xl = New Excel.Application

Use something like this:

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
If Err <> 0 Then
  Err.Clear
  Set xl = CreateObject(&quot;Excel.Application&quot;)
End If
On Error GoTo ErrHandler

VBSlammer

Unemployed in Houston, Texas