protecting the spreadsheet causes xlConn fail

Hi.

I need to protect a spreadsheet and protecting the spreadsheet causes the .open of xlConn to fail.

I need to check the protection status of the spreadsheet in vb and if protection is enabled, disable it, passing it the sheets unprotect password. Should be relatively simple using xlobjects.


Can anyone help? :)

Can you determine if a sheet is password protected before
opening in Excel?

You can not determine anything of a workbook until it is opened.
So are we talking about the workbook having a
password or just a particular sheet?

If you just want to check if a sheet is Protected after the workbook is open then you can use
the .Protect and .UnProtect for the sheets objects and the .Locked and .Protect for the range object.

Thanks for your help. Can you show me what that would look like?

Fire

Here is the code so far.....

VB Code:

Public Sub ConnectToServer(objConn, objSrv)
 
Dim strSrvName, strDBSName As String ' Declare Server Name and DB Name
 
    strSrvName = "****" 'Assign name to string
    strDBSName = "****"
    
    Set objSrv = New SQLDMO.SQLServer
    objSrv.Connect "BRCINT01", "****", "******"
 
    ' Instantiate an ADO Connection object and open it to the '
    ' designated database on the designated server for the    '
    ' designated database.                                    '
    Set objConn = New ADODB.Connection 
    With objConn
        .Provider = "sqloledb" 
        .ConnectionString = "data source = " & strSrvName & _
                ";" & "Initial Catalog = " & strDBSName & ";" & _
                "user id = *****; Password=******" 
 
        .Open 
    End With
 
End Sub
_________________________________________________________________________________________________________
Public Sub GetXLRecordset(strXLFileName, strSQL, rsXl As ADODB.Recordset, xlConn As ADODB.Connection)
 
Dim strDefaultPath As String
'Populate a recordset from an excel spreadsheet.
 
'This subroutine relies on the user correctly passing the name of the sheet withing
'the selected workbook.
 
'If the workbook is protected it will not open.
 
'On Error GoTo RECORDSETERROR
 
    With xlConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        strDefaultPath = splitFilePath(strXLFileName)
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & strXLFileName & ";DefaultDir=c:\mypath;"
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strXLFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"
        .ConnectionString = "Data Source=" & strXLFileName & _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"
        .Open
    End With
        
    With rsXl
        .ActiveConnection = xlConn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = strSQL
        .Open
    End With
    
    Exit Sub
    
RECORDSETERROR:
    MsgBox "Unable to get records from Excel workbook: " & strXLFileName & vbCr & _
            vbCr & _
            "Please check the format of the selected spreasheet and that no-one has the spreadsheet open. Error number: " & Err.Number
 
    
End Sub

Try testing your connectstring this way.

VB Code:

With xlConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    strDefaultPath = splitFilePath(strXLFileName)
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & strXLFileName & ";DefaultDir=c:\mypath;"
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strXLFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"
    .ConnectionString = "Data Source=" & strXLFileName & _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"
    Debug.Print .ConnectionString '<-- 
    .Open
End With

You need to concatinate your connectionstring or else only the last line assignment will be what
it contains. Check the debug.print output.

Hey,

I am a bit of a novice. How would I run this as a macro? I dont know how to make this recognised as a macro on the 'play list'.

Sounds silly I know...

Code for unprotecting and then protecting a worksheet !!

VB Code:

With Sheet1         ' < This should be the name of the YOUR worksheet 
    .Unprotect '(password - if required)
     
    'do stuff here !
 
    .Protect '(password - if required)
End With

From the look of your connectionstring, its to correct. Checkout connectionstrings.com
for the correct one to use.

Quote:

---

Originally Posted by Firestart

Hey,

I am a bit of a novice. How would I run this as a macro? I dont know how to make this recognised as a macro on the 'play list'.

Sounds silly I know...

---

As soonn as you record a Macro, and stop the recording, whatever you recorded will be in the playlist. You can then edit that or play it back to make sure that it is correct. When it is, you can copy/paste it into your VB code, where it will require some modifications, but you can get the general idea of how to perform your task.

David,

I think I may have not communicated my question clearly. The code that I have inserted above in itself is not a macro, because it doesn't appear on my recording list. How do I make it a macro? In other words, How do I get
Public Sub ConnectToServer(objConn, objSrv)

Public Sub GetXLRecordset(strXLFileName, strSQL, rsXl As ADODB.Recordset, xlConn As ADODB.Connection)

To run as a macro?