L
leerem
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.
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.