I need to write to a protected excel worksheet. So far i have code which can
write to an unprotected sheet as below:

Help would be much appreciated

++++++++++++++++++++++++++++++++++++++++++++++

Private Sub WriteIDtoXL(ByVal str)
Dim conn1 As New System.Data.OleDb.OleDbConnection(xlConn(str))
conn1.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [CCID] (ID) values (" &
Session("mCallCentreID") & ")"
cmd.ExecuteNonQuery()
conn1.Close()

End Sub

Private Function xlConn(ByVal FilePath As String) As String

xlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & ";Extended Properties=""Excel 8.0;HDR=YES;MAXSCANROWS=4"""

End Function

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

(Winshent) wrote ...

I need to write to a protected excel worksheet. So far i have code which can
write to an unprotected sheet

It's not possible using ADO because it's ... well, protected.

xlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & ";Extended Properties=""Excel 8.0;HDR=YES;MAXSCANROWS=4"""

Specifying MAXSCANROWS in the connection string has no effect. I guess
you want to override the TypeGuessRows registry key on the local
machine. I wish I was wrong but it simply isn't possible.

Jamie.

--

Hi Jamie

Thanks for your response.

I did write some code which launches excel on the server, unprotects the
sheet and the saves and closes the file. Then the ado.net code inserts
some data. The protection code is then rerun and protects the sheet. But
after saving and then closing the spread, it would trigger the
BeforeClose event in the actual spread! Any ideas?

What i dont understand is why this isnt triggered during the initial
unprotect process?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Winshent P wrote ...

I did write some code which launches excel on the server, unprotects the
sheet and the saves and closes the file. Then the ado.net code inserts
some data. The protection code is then rerun and protects the sheet. But
after saving and then closing the spread, it would trigger the
BeforeClose event in the actual spread! Any ideas?

What i dont understand is why this isnt triggered during the initial
unprotect process?

Are you saying the BeforeClose event doesn't fire when the only
difference is unprotecting rather than protecting? Sorry, I can't
think of a reason for that behaviour unless there is something in in
the workbook conditionally blocking the event.

Jamie.

--