Prev: Avoiding #N/A result
Next: Protecting range limits
From: leerem on 27 Apr 2010 07:50
I need your assistance once again.

I have a workbook that needs to be updated from a distant PC. I believe this
can be achieved vai ADO. I'm totaly new to ADO so please bear with me.
I need to access the information on a workbook named 'New Inload Final.xlsm'
Sheets("Entry") which has been Mapped to the Z:/ drive. I have been able to
compile the attached code which I have gained through books etc, but i get
the error 'Cannot update. Database or object is read-only.'

Idealy I'd like to say access the worksheet for analytical reasons wherby by
reports can be risen based on the information contained within the sheet,
allowing for the distant PC to be updated by the User, which will then in
turn update my workbook reports. Can this be achieved.

My Code to create connection.

Public Sub QueryWorksheet()
Dim rst As ADODB.Recordset
Dim Connectionstring As String

Connectionstring = _
"Provider=Microsoft.jet.OLEDB.4.0;" & "Data Source=" _
& "D:\Store Returns\Data\CurData.xlsm;" & "Extended Properties=Excel 8.0;"

Dim SQL As String

'Query based on the Worksheet name

SQL = "Select * FROM [Sheet1$]"

Set rst = New ADODB.Recordset

On Error GoTo CleanUp

Call rst.Open(SQL, Connectionstring, _
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
CommandTypeEnum.adCmdText)

Call Sheet1.Range("A1").CopyFromRecordset(rst)

CleanUp:
Debug.Print Err.Description

If (rst.State = ObjectStateEnum.adStateOpen) Then
rst.Close
End If

Set rst = Nothing
End Sub

Can anyone point me in the correct direction please.
 | 
Pages: 1
Prev: Avoiding #N/A result
Next: Protecting range limits