Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADODB in MS Excel.
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADODB in MS Excel.
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" wrote in 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADODB in MS Excel.
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" wrote: 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" wrote in 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADODB in MS Excel.
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" wrote in message ... 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" wrote: 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" wrote in 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADODB in MS Excel.
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" wrote in message ... 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" wrote in message ... 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" wrote: 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" wrote in 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Create New Database from Excel | Excel Programming | |||
How to use ADODB in Excel 2003 | Excel Programming | |||
share adodb connection in excel | Excel Programming | |||
adodb.recordset with excel | Excel Programming | |||
Export from Excel to Access ADODB | Excel Programming |