Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.odbc
|
|||
|
|||
Data from Excel into SQL
Please excuse the cross posting I was not sure which newsgroup would produce
the required advice. I have a requirement to update a SQL table with data from an Excel worksheet. Can anyone suggest the best way to do this....ADO????? PWS |
#2
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.odbc
|
|||
|
|||
Data from Excel into SQL
There are numerous variables here. Is this a one-time requirement, or an on-going need? Do you
want to write a program, or just use a utility? If programming, what languages are you open to using? "Paul Smith" wrote in message ... Please excuse the cross posting I was not sure which newsgroup would produce the required advice. I have a requirement to update a SQL table with data from an Excel worksheet. Can anyone suggest the best way to do this....ADO????? PWS |
#3
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.odbc
|
|||
|
|||
Data from Excel into SQL
ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links: 'Exhaustive list of objects, properties, methos etc. http://www.devguru.com/Technologies/...cts_index.html 'getting started http://msdn.microsoft.com/library/de...tml/sqlado.asp 'a good FAQ for commonly experienced problems - beats waiting for replies on an NG! http://www.oblique.ch/ms/ADO_Faq.html And here's some sample code to get you started. Note, there's many ways to work with ADO and Access - I use RecordSet to retrieve data and Command to insert or update. Seems to work ok.... HTH Gareth '----------------------------------- Private Const myDBConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0" Private Const myDBLocation As String = "C:\myDB.mdb" Private rs As ADODB.Recordset Private cn As ADODB.Connection 'Once we've opened a connection we leave it open! 'This is closed upon closing the workbook. Private Function fcnConnectToDB() As Boolean 'Check whether the connection is already open. If Not cn Is Nothing Then If cn.State < adStateClosed Then fcnConnectToDB = True Exit Function End If End If 'Open connection the database Set cn = New ADODB.Connection With cn .Errors.Clear On Error Resume Next .CursorLocation = adUseClient .connectionString = myDBConnectionString .Open myDBLocation On Error GoTo 0 If .Errors.Count = 0 Then fcnConnectToDB = True End With End Function ' 'Returns False if there was a error 'Else returns array containing of returned records Public Function ADO_RunQuery(mySQL As String) As Variant 'Check we're connected to the database fcnConnectToDB 'Open the recordset. Set rs = New ADODB.Recordset With rs .ActiveConnection = cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockPessimistic 'adLockBatchOptimistic .Open mySQL 'cmd End With 'Dump the record set into an array If rs.RecordCount = 0 Then ADO_RunQuery = False Else ADO_RunQuery = rs.GetRows End If rs.Close 'tidy up Set rs = Nothing '(We leave the connection permanently open - you may like to ' close it afterwards) End Function Public Function ADO_UpdateDB(mySQL As String) As Boolean 'Check we're connected to the database fcnConnectToDB 'Set the command text and execute Set cmd = New ADODB.Command With cmd .ActiveConnection = cn .CommandText = mySQL .CommandType = adCmdText On Error Resume Next .Execute If Err.Number = 0 Then ADO_UpdateDB = True On Error GoTo 0 End With 'tidy up Set cmd = Nothing '(We leave the connection permanently open) End Function Paul Smith wrote: Please excuse the cross posting I was not sure which newsgroup would produce the required advice. I have a requirement to update a SQL table with data from an Excel worksheet. Can anyone suggest the best way to do this....ADO????? PWS |
#4
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.odbc
|
|||
|
|||
Data from Excel into SQL
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table * insert records * select records, * update records, * delete records, * delete a table, * delete a database. DAO and ADO files available. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Paul Smith" wrote in message ... Please excuse the cross posting I was not sure which newsgroup would produce the required advice. I have a requirement to update a SQL table with data from an Excel worksheet. Can anyone suggest the best way to do this....ADO????? PWS |
#5
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.odbc
|
|||
|
|||
Data from Excel into SQL
I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks Andy Wiggins wrote: This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table * insert records * select records, * update records, * delete records, * delete a table, * delete a database. DAO and ADO files available. You can also download the demonstration file called "excelsql.zip". The code is open and commented. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Write Macro to Fix Data Alignment (Data dump from Crystal to Excel | Excel Discussion (Misc queries) | |||
Email (LDAP) data download into a single Excel cell - data separat | Excel Worksheet Functions | |||
excel 2007, how to select a data point and cycle through data points | Charts and Charting in Excel | |||
Eliminating rows of data in excel spreadsheet that have blank cell in row A and data in row B - E | Excel Programming |