I've used this code to get data from external workbook:
This works fine but my question is, how can I reverse it? I would like to do another button to save the changes (made in local workbook) to the external file Test.xls (overwriting the old data). In other words, how can I copy the whole data range from a local worksheet to Test.xls sheet "Taul1"?
I'm just a beginner with ADO and I've tried different functions and examples for writing external files but I just can't get the hang of it. I'd appreciate it if somebody could show an example code for this.
PS. I'm using Excel 2002 and VB6.
Code:
Private Sub CommandButton1_Click()
GetWorksheetData "Test.xls", "SELECT * FROM [Taul1$];", ThisWorkbook.Worksheets(2).Range("A2")
End Sub
Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & strSourceFile & ";"
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If
TargetCell.CopyFromRecordset rs
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
This works fine but my question is, how can I reverse it? I would like to do another button to save the changes (made in local workbook) to the external file Test.xls (overwriting the old data). In other words, how can I copy the whole data range from a local worksheet to Test.xls sheet "Taul1"?
I'm just a beginner with ADO and I've tried different functions and examples for writing external files but I just can't get the hang of it. I'd appreciate it if somebody could show an example code for this.
PS. I'm using Excel 2002 and VB6.