Using ADODB in MS Excel.

  • Thread starter Thread starter Sunil Pradhan
  • Start date Start date
S

Sunil Pradhan

I have 2 excel files namely Interface.xls & Tracker.xls.

I have used following code to update data from Interface to Tracker.

Public Sub interface2datadump()
Sheet1.Select

Dim cn As ADODB.Connection, RS As ADODB.Recordset, RS1 As ADODB.Recordset, r
As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Tracker\tracker.xls;" & _
"Extended Properties=""Excel 8.0;"""

Set RS = New ADODB.Recordset
RS.Open "select * from [DATABASE$]", cn, adOpenKeyset, adLockOptimistic
r = 2

With RS
.AddNew
.Fields("Name") = Sheet1.Cells(1, 2).Value
.Fields("Emp ID") = Sheet1.Cells(2, 2).Value
.Fields("Phone") = Sheet1.Cells(3, 2).Value
.Update
End With
RS.Close
Set RS1 = Nothing
Set RS = Nothing
cn.Close
Set cn = Nothing

Sheet1.Cells(1, 2).Value = ""
Sheet1.Cells(2, 2).Value = ""
Sheet1.Cells(3, 2).Value = ""

End Sub

Is there any better way to do this? or can I update the data to tracker
without opening the tracker.xls except using ADODB as I have used above.

Also, I want to edit some data in Tracker & I want to use Inerface file to
edit & update & even delete the data in tracker.xls. Please help me to write
code to fetch the data from tracker, edit & update.

Thanks,
 
you don't use the variables RS1 nor r

it might be quicker to open the tracker workbook and use VBA to copy the
data into the table. It might make it easier to update your records too.
 
You are correct Patrick but the data in tracker is updated by almost 20
people simultaneoulsly & it will not be possible to open the tracker & update
the data in tracker. Patrick, if possible pls also reply to my second
question. that is:

I want to edit some data in Tracker & I want to use Inerface file to edit &
update & even delete the data in tracker.xls. Please help me to write code
to fetch the data from tracker, edit & update.

Thanks

Patrick Molloy said:
you don't use the variables RS1 nor r

it might be quicker to open the tracker workbook and use VBA to copy the
data into the table. It might make it easier to update your records too.



Sunil Pradhan said:
I have 2 excel files namely Interface.xls & Tracker.xls.

I have used following code to update data from Interface to Tracker.

Public Sub interface2datadump()
Sheet1.Select

Dim cn As ADODB.Connection, RS As ADODB.Recordset, RS1 As ADODB.Recordset,
r
As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Tracker\tracker.xls;" & _
"Extended Properties=""Excel 8.0;"""

Set RS = New ADODB.Recordset
RS.Open "select * from [DATABASE$]", cn, adOpenKeyset, adLockOptimistic
r = 2

With RS
.AddNew
.Fields("Name") = Sheet1.Cells(1, 2).Value
.Fields("Emp ID") = Sheet1.Cells(2, 2).Value
.Fields("Phone") = Sheet1.Cells(3, 2).Value
.Update
End With
RS.Close
Set RS1 = Nothing
Set RS = Nothing
cn.Close
Set cn = Nothing

Sheet1.Cells(1, 2).Value = ""
Sheet1.Cells(2, 2).Value = ""
Sheet1.Cells(3, 2).Value = ""

End Sub

Is there any better way to do this? or can I update the data to tracker
without opening the tracker.xls except using ADODB as I have used above.

Also, I want to edit some data in Tracker & I want to use Inerface file to
edit & update & even delete the data in tracker.xls. Please help me to
write
code to fetch the data from tracker, edit & update.

Thanks,
Click to expand...
Click to expand...
 
if you have as many as 20 people updating the file, then you are using the
wrong application. For this many, Microsoft Access Database would work well.
For more users, you would need to think of a SQL Server daytabse.

What do you mean by an Interface file?

for updating records, I guess that you use the Employee ID as key?


Sunil Pradhan said:
You are correct Patrick but the data in tracker is updated by almost 20
people simultaneoulsly & it will not be possible to open the tracker &
update
the data in tracker. Patrick, if possible pls also reply to my second
question. that is:

I want to edit some data in Tracker & I want to use Inerface file to edit
&
update & even delete the data in tracker.xls. Please help me to write
code
to fetch the data from tracker, edit & update.

Thanks

Patrick Molloy said:
you don't use the variables RS1 nor r

it might be quicker to open the tracker workbook and use VBA to copy the
data into the table. It might make it easier to update your records too.



Sunil Pradhan said:
I have 2 excel files namely Interface.xls & Tracker.xls.

I have used following code to update data from Interface to Tracker.

Public Sub interface2datadump()
Sheet1.Select

Dim cn As ADODB.Connection, RS As ADODB.Recordset, RS1 As
ADODB.Recordset,
r
As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Tracker\tracker.xls;" & _
"Extended Properties=""Excel 8.0;"""

Set RS = New ADODB.Recordset
RS.Open "select * from [DATABASE$]", cn, adOpenKeyset, adLockOptimistic
r = 2

With RS
.AddNew
.Fields("Name") = Sheet1.Cells(1, 2).Value
.Fields("Emp ID") = Sheet1.Cells(2, 2).Value
.Fields("Phone") = Sheet1.Cells(3, 2).Value
.Update
End With
RS.Close
Set RS1 = Nothing
Set RS = Nothing
cn.Close
Set cn = Nothing

Sheet1.Cells(1, 2).Value = ""
Sheet1.Cells(2, 2).Value = ""
Sheet1.Cells(3, 2).Value = ""

End Sub

Is there any better way to do this? or can I update the data to tracker
without opening the tracker.xls except using ADODB as I have used
above.

Also, I want to edit some data in Tracker & I want to use Inerface file
to
edit & update & even delete the data in tracker.xls. Please help me to
write
code to fetch the data from tracker, edit & update.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
i should add that with the MS Access Database, you can just import the table
directly from the excel workbook that you currently use, and converting the
other workbooks to read from this Access table rather than the Excel table
is simply a matter of changing the connection string. ... the imported table
will have the same name, structure and data


Patrick Molloy said:
if you have as many as 20 people updating the file, then you are using the
wrong application. For this many, Microsoft Access Database would work
well. For more users, you would need to think of a SQL Server daytabse.

What do you mean by an Interface file?

for updating records, I guess that you use the Employee ID as key?


Sunil Pradhan said:
You are correct Patrick but the data in tracker is updated by almost 20
people simultaneoulsly & it will not be possible to open the tracker &
update
the data in tracker. Patrick, if possible pls also reply to my second
question. that is:

I want to edit some data in Tracker & I want to use Inerface file to edit
&
update & even delete the data in tracker.xls. Please help me to write
code
to fetch the data from tracker, edit & update.

Thanks

Patrick Molloy said:
you don't use the variables RS1 nor r

it might be quicker to open the tracker workbook and use VBA to copy the
data into the table. It might make it easier to update your records
too.



message
I have 2 excel files namely Interface.xls & Tracker.xls.

I have used following code to update data from Interface to Tracker.

Public Sub interface2datadump()
Sheet1.Select

Dim cn As ADODB.Connection, RS As ADODB.Recordset, RS1 As
ADODB.Recordset,
r
As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Tracker\tracker.xls;" & _
"Extended Properties=""Excel 8.0;"""

Set RS = New ADODB.Recordset
RS.Open "select * from [DATABASE$]", cn, adOpenKeyset,
adLockOptimistic
r = 2

With RS
.AddNew
.Fields("Name") = Sheet1.Cells(1, 2).Value
.Fields("Emp ID") = Sheet1.Cells(2, 2).Value
.Fields("Phone") = Sheet1.Cells(3, 2).Value
.Update
End With
RS.Close
Set RS1 = Nothing
Set RS = Nothing
cn.Close
Set cn = Nothing

Sheet1.Cells(1, 2).Value = ""
Sheet1.Cells(2, 2).Value = ""
Sheet1.Cells(3, 2).Value = ""

End Sub

Is there any better way to do this? or can I update the data to
tracker
without opening the tracker.xls except using ADODB as I have used
above.

Also, I want to edit some data in Tracker & I want to use Inerface
file to
edit & update & even delete the data in tracker.xls. Please help me
to
write
code to fetch the data from tracker, edit & update.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top