Zolf
asked on
export excel data into sql server 2005 using macros
Hello there,
i am using SSRS to create report which i get from excel sheet into my db and then i create my report from the db.now i want to create a macro which when the user will click will export the excel sheet data into the db table.then i can create the report.please help me create this.
thanks
cheers
zolf
i am using SSRS to create report which i get from excel sheet into my db and then i create my report from the db.now i want to create a macro which when the user will click will export the excel sheet data into the db table.then i can create the report.please help me create this.
thanks
cheers
zolf
you can create an ADO connection to your SQL server and add the records into whatever SQL table.
The following text and sample code illustrates how, from Excel VBA, to:
-> open a database connection
-> use a stored procedure to perform a query
-> execute any SQL command against a database
-> open a recordset using a custom query
-> open a recordset using a table name
-> check for an empty recordset
-> read all records in a recordset
-> add a record to a recordset
-> delete a record from a recordset
-> copy a recordset with headers to a worksheet
-> close a recordset and database
This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.
After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.
Value = MyRecordset!Field1
MyRecordset!Field2 = Value + 1
When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.
Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).
For additional information on the ADODB interface see the MSDN pages at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp
Sample code:
Dim MyDatabase As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim MyRecordset As ADODB.RecordSet
Dim Column As Long
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
' For more information about Open syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Connection)
' Additional help constructing connection strings can be found at http://www.connectionstrings.com/
' Query database using stored procedure (requires command object)
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection = MyDatabase
MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
MyCommand.CommandType = adCmdStoredProc
With MyCommand
.Parameters.Refresh
.Parameters.Append .CreateParameter("QueryTex tParam", adVarChar, adParamInput, 10, "Value")
.Parameters.Append .CreateParameter("QueryLon gParam", adBigInt, adParamInput, , LongValue)
.Parameters.Append .CreateParameter("QueryDat eParam", adDate, adParamInput, , DateValue)
.Parameters.Append .CreateParameter("QueryDat eTimeStamp Param", adDBTimeStamp, adParamInput, , DateTimeValue)
.Parameters.Append .CreateParameter("BooleanP aram", adBoolean, adParamInput, , BooleanValue)
' For more information about CreateParameter syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to CreateParameter Method
End With
' Open recordset using command object
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
' For more information about Open method syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Recordset)
' Build a custom query using command object
Set MyCommand = New ADODB.Command
With MyCommand
Set .ActiveConnection = MyDatabase
.CommandType = adCmdText
.CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
End With
MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly
' Execute any SQL statement
MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"
' Open a recordset by specifying specific table (no query)
MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
' Open a recordset by specifying query without using command object
MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic
' Test for no records
If MyRecordset.BOF And MyRecordset.EOF Then
MsgBox "No records in table"
End If
' Determine total records
MsgBox "Total records: " & MyRecordset.RecordCount
' Look at all records in record set
While Not MyRecordset.EOF
MsgBox "Record number: " & MyRecordset.AbsolutePositi on
MyRecordset.MoveNext
Wend
' Copy the entire recordset to a worksheet (this technique does not copy field names)
Sheets("Sheet1").[A2].Copy FromRecord set MyRecordset
' Create headers and copy data
With Sheets("Sheet1")
For Column = 0 To MyRecordset.Fields.Count - 1
.Cells(1, Column + 1).Value = MyRecordset.Fields(Column) .Name
Next
.Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)) .Font.Bold = True
.Cells(2, 1).CopyFromRecordset MyRecordset
End With
' Update current record
MyRecordset!Field1 = "Some data"
MyRecordset!Field2 = "Some more data"
MyRecordset.Update
' Move specific fields from current record to worksheet
With Sheets("Sheet1")
Cells(Row, "A") = MyRecordset!Field1
Cells(Row, "B") = MyRecordset!Field2
End With
' Add new record and set field values
MyRecordset.AddNew
MyRecordset!Field1 = "Some data"
MyRecordset.Update
' Delete current record
MyRecordset.Delete
' Close recordset
MyRecordset.Close
' Close database
MyDatabase.Close
Kevin
-> open a database connection
-> use a stored procedure to perform a query
-> execute any SQL command against a database
-> open a recordset using a custom query
-> open a recordset using a table name
-> check for an empty recordset
-> read all records in a recordset
-> add a record to a recordset
-> delete a record from a recordset
-> copy a recordset with headers to a worksheet
-> close a recordset and database
This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.
After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.
Value = MyRecordset!Field1
MyRecordset!Field2 = Value + 1
When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.
Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).
For additional information on the ADODB interface see the MSDN pages at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp
Sample code:
Dim MyDatabase As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim MyRecordset As ADODB.RecordSet
Dim Column As Long
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
' For more information about Open syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Connection)
' Additional help constructing connection strings can be found at http://www.connectionstrings.com/
' Query database using stored procedure (requires command object)
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection
MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
MyCommand.CommandType = adCmdStoredProc
With MyCommand
.Parameters.Refresh
.Parameters.Append .CreateParameter("QueryTex
.Parameters.Append .CreateParameter("QueryLon
.Parameters.Append .CreateParameter("QueryDat
.Parameters.Append .CreateParameter("QueryDat
.Parameters.Append .CreateParameter("BooleanP
' For more information about CreateParameter syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to CreateParameter Method
End With
' Open recordset using command object
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
' For more information about Open method syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Recordset)
' Build a custom query using command object
Set MyCommand = New ADODB.Command
With MyCommand
Set .ActiveConnection = MyDatabase
.CommandType = adCmdText
.CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
End With
MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly
' Execute any SQL statement
MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"
' Open a recordset by specifying specific table (no query)
MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
' Open a recordset by specifying query without using command object
MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic
' Test for no records
If MyRecordset.BOF And MyRecordset.EOF Then
MsgBox "No records in table"
End If
' Determine total records
MsgBox "Total records: " & MyRecordset.RecordCount
' Look at all records in record set
While Not MyRecordset.EOF
MsgBox "Record number: " & MyRecordset.AbsolutePositi
MyRecordset.MoveNext
Wend
' Copy the entire recordset to a worksheet (this technique does not copy field names)
Sheets("Sheet1").[A2].Copy
' Create headers and copy data
With Sheets("Sheet1")
For Column = 0 To MyRecordset.Fields.Count - 1
.Cells(1, Column + 1).Value = MyRecordset.Fields(Column)
Next
.Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count))
.Cells(2, 1).CopyFromRecordset MyRecordset
End With
' Update current record
MyRecordset!Field1 = "Some data"
MyRecordset!Field2 = "Some more data"
MyRecordset.Update
' Move specific fields from current record to worksheet
With Sheets("Sheet1")
Cells(Row, "A") = MyRecordset!Field1
Cells(Row, "B") = MyRecordset!Field2
End With
' Add new record and set field values
MyRecordset.AddNew
MyRecordset!Field1 = "Some data"
MyRecordset.Update
' Delete current record
MyRecordset.Delete
' Close recordset
MyRecordset.Close
' Close database
MyDatabase.Close
Kevin
... or you could use a 'pull' scenario from the SQL server:
- In the Excel sheet, select the section that represents the table.
- From the menu select Insert - Name - Define and enter a name for the table (YourArea for example)
- Close the Excel file
- In SQL server run the following query:
Insert into YourTable
SELECT *
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl s";Extende d Properties=Excel 8.0')...[YourArea]
Hope this helps ...
- In the Excel sheet, select the section that represents the table.
- From the menu select Insert - Name - Define and enter a name for the table (YourArea for example)
- Close the Excel file
- In SQL server run the following query:
Insert into YourTable
SELECT *
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl
Hope this helps ...
... Make sure to configure the option to use opendatasource in advance:
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
Hope this helps ...
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
Hope this helps ...
ASKER
zorvek:
in your comment you provied the code.where do i put this code.please if you dont mind provide me steps to do to export excel data into a table in sql server 2005.
Yveau:
when i run the query you provided i get this error in sql server 2005.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
you also mentioned in your first comment
--From the menu select Insert - Name - Define and enter a name for the table (YourArea for example)
i am using excel 2007 where is it in there.i can see insert but not the other part - Name - Define and enter a name for the table.
ASKER
i also activated the adhoc thingy from the surface area config
If you already activated it from the surface area configuration tool, you don't have to do that again here.
If you want to activate and deactivate it afterwards again, the complete scenario would be:
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
... run query ...
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go
Hope this helps ...
If you want to activate and deactivate it afterwards again, the complete scenario would be:
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
... run query ...
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go
Hope this helps ...
I have no Excel 2007, but you should be able to select an area and define a name for it.
Maybe Excel Genius Zorvek can tell you where that option is hidden in Excel 2007 ?
...
Maybe Excel Genius Zorvek can tell you where that option is hidden in Excel 2007 ?
...
ASKER
zorvek:
can you tell me where i can find the thingy which Yveau: mentioned in his comment.
The defined name option is hidden under toolbar Formula>> Define Name
ASKER
when i run this query in studio.i get error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
SELECT *
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Documents and Settings\Zolf\Desktop\test
ASKER
running this query i get the values from the excel.which is perfect.now how can i insert these values inside a table.please help.
SELECT * FROM OPENDATASOURCE('Microsoft.
'Data Source=C:\Documents and Settings\Zolf\Desktop\test
ASKER
what is this ...Sheet$ which needs to be there in the query without which i get that error
No it should have been the area as I mentioned before:
SELECT *
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl s";Extende d Properties=Excel 8.0')...[YourArea]
Otherwise the query will take a 'smart' look at the sheet mentioned and defines a table by it self. By mentioning the defined area, you are sure of the number of columns etc.
Hope this helps ...
SELECT *
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl
Otherwise the query will take a 'smart' look at the sheet mentioned and defines a table by it self. By mentioning the defined area, you are sure of the number of columns etc.
Hope this helps ...
All you need to do is open the connection and then open a recordset against the table, then you can add the records:
Public Sub AddRecordsToDB()
Dim MyDatabase As ADODB.Connection
Dim MyRecordset As ADODB.RecordSet
Dim Row As Long
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
' For more information about Open syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Connection)
' Additional help constructing connection strings can be found at http://www.connectionstrings.com/
' Open a recordset by specifying specific table (no query)
MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
' Add each row in the worksheet to the table
For Row = 1 To 10
MyRecordset.AddNew
MyRecordset!Field1 = Sheets("Sheet1").Cells(Row , "A").Value
MyRecordset!Field2 = Sheets("Sheet1").Cells(Row , "B").Value
MyRecordset!Field3 = Sheets("Sheet1").Cells(Row , "C").Value
MyRecordset!Field4 = Sheets("Sheet1").Cells(Row , "D").Value
MyRecordset.Update
Next Row
' Close recordset
MyRecordset.Close
' Close database
MyDatabase.Close
End Sub
The above code can be added to any general or worksheet code module. I assumed in the example above that your data is in rows 1 through 10 of Sheet1. I used a connection string for an Access DB. Use the reference sites I cited above to construct a connection string for you DB.
Kevin
Public Sub AddRecordsToDB()
Dim MyDatabase As ADODB.Connection
Dim MyRecordset As ADODB.RecordSet
Dim Row As Long
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
' For more information about Open syntax:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
' and navigate to Open Method (ADO Connection)
' Additional help constructing connection strings can be found at http://www.connectionstrings.com/
' Open a recordset by specifying specific table (no query)
MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
' Add each row in the worksheet to the table
For Row = 1 To 10
MyRecordset.AddNew
MyRecordset!Field1 = Sheets("Sheet1").Cells(Row
MyRecordset!Field2 = Sheets("Sheet1").Cells(Row
MyRecordset!Field3 = Sheets("Sheet1").Cells(Row
MyRecordset!Field4 = Sheets("Sheet1").Cells(Row
MyRecordset.Update
Next Row
' Close recordset
MyRecordset.Close
' Close database
MyDatabase.Close
End Sub
The above code can be added to any general or worksheet code module. I assumed in the example above that your data is in rows 1 through 10 of Sheet1. I used a connection string for an Access DB. Use the reference sites I cited above to construct a connection string for you DB.
Kevin
Here is an example SQL Server connection string from a recent project:
"Provider=sqloledb;Data Source=XXXXX;initial catalog=msdb;user id=xxxx; password=xxxx"
The above code is coded as a macro. To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.
To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.
Kevin
"Provider=sqloledb;Data Source=XXXXX;initial catalog=msdb;user id=xxxx; password=xxxx"
The above code is coded as a macro. To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.
To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.
Kevin
... that connection will work in case of SQL authentication.
When you need Windows authentication, use this format:
"Provider=sqloledb;Data Source=XXXXX;initial catalog=msdb;Integrated Security=SSPI"
Hope this helps ...
When you need Windows authentication, use this format:
"Provider=sqloledb;Data Source=XXXXX;initial catalog=msdb;Integrated Security=SSPI"
Hope this helps ...
ASKER
thanks for your comments guys.
zorvek:
when i try to run the macro.i get an error saying User-defined type not defined.
Yveau:
your idea works but that only gets the values from the excel and displays in a table.how can i insert those values inside a db table.
zorvek idea is better as the HRM dept work with excel.they will just run the macro and the db will be updated with the new values in the table.i have created the same table format like the excel sheet in the db.
i will appreciate your help if you'll can help me achieve this.
the sheet has around 70 columns which i will take care of it.but for the excel to connect to the db you'll mentioned in the DB string which looks like this Data Source='C:\full\path\to\da
where do i find this in sql server 2005.i mean where is the db saved so i can point it to it.
ASKER
ok.i got this code in EE.which works fine.but it does opposite to what i want.i.e it gets the table from the db and puts it in excel.but i need to export the excel data into db table.please help me to edit this code below to do what i need.
the code which zorvek: provided is a little diffecult to connect to a db which is on another server or localhost.
Sub Button1_Click()
'Create connection object
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
'Provide the connection string
Dim strConn As String
'Use the SQL Server OLE DB Provider
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Adventureworks database on the local server
strConn = strConn & "DATA SOURCE = (local);INITIAL CATALOG = pol_employeesalary;"
'Use an integrated login.
strConn = strConn & "INTEGRATED SECURITY=sspi;"
'Now open the connection
cnPubs.Open strConn
'Create a recordset object
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
'Assign the Connection object
.ActiveConnection = cnPubs
'Extract the required records
.Open "Select * from employeesalary"
'Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFro mRecordset rsPubs
'Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
the code which zorvek: provided is a little diffecult to connect to a db which is on another server or localhost.
Sub Button1_Click()
'Create connection object
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
'Provide the connection string
Dim strConn As String
'Use the SQL Server OLE DB Provider
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Adventureworks database on the local server
strConn = strConn & "DATA SOURCE = (local);INITIAL CATALOG = pol_employeesalary;"
'Use an integrated login.
strConn = strConn & "INTEGRATED SECURITY=sspi;"
'Now open the connection
cnPubs.Open strConn
'Create a recordset object
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
'Assign the Connection object
.ActiveConnection = cnPubs
'Extract the required records
.Open "Select * from employeesalary"
'Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFro
'Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
ASKER
now i have managed to edit the above code to my needs.but i get an error on line
rsPubs!Field1 = Sheets("Sheet4").Cells(Row
saying
run time error 3265 "item not found in this collection"
please help
Sub Button1_Click()
'Create connection object
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
'Provide the connection string
Dim strConn As String
'Use the SQL Server OLE DB Provider
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Adventureworks database on the local server
strConn = strConn & "DATA SOURCE = (local);INITIAL CATALOG = pol_employeesalary;"
'Use an integrated login.
strConn = strConn & "INTEGRATED SECURITY=sspi;"
'Now open the connection
cnPubs.Open strConn
'Create a recordset object
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
' Open a recordset by specifying specific table (no query)
rsPubs.Open "employeesalary", cnPubs, adOpenDynamic, adLockPessimistic
' Add each row in the worksheet to the table
For Row = 1 To 10
rsPubs.AddNew
rsPubs!Field1 = Sheets("Sheet4").Cells(Row
rsPubs!Field2 = Sheets("Sheet4").Cells(Row
rsPubs!Field3 = Sheets("Sheet4").Cells(Row
rsPubs!Field4 = Sheets("Sheet4").Cells(Row
rsPubs.Update
Next Row
Insert them by:
insert into YourTable
(col1, col2, col3)
SELECT * -- >> make sure that these columns are the same (and same order) as mentioned in the previous line !!!
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl s";Extende d Properties=Excel 8.0')...[YourArea]
Hope this helps ...
insert into YourTable
(col1, col2, col3)
SELECT * -- >> make sure that these columns are the same (and same order) as mentioned in the previous line !!!
FROM OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="C:\Path\To\File.xl
Hope this helps ...
ASKER
zorvek:
please help to export excel data to sql server 2005 from excel sheet
Yveau:
have you any idea what i need to change in the code above to make it work.from excel it is easier for the hrm dept to export data directly.instead of me everytime writing query in sql server 2005
zolf,
I gave you code that will do what you want. Unfortunately you have not provided enough information to give an actual working solution. For example, all you have said is you have a table in an Excel worksheet and you want to move it to a SQL Server database. We don't know how many columns are in the table. We don't know the table name or the names of any of the fields in the table. We don't know to what fields you want to copy what columns. We don't know how to build a connection string for you since we don't know the server name, the database name, or the login credentials (and please don't post them here ;-)
What we have given you are templates that you can fill in with your specifics. Add my code to any general code module. Follow the instructions in the code to complete the missing elements such as the table and field names. Run the macro and see if it works. Use the debugger to figure out what the bugs are and fix them.
Please keep in mind that we are not a software house. We are not consultants. We answer questions. We only write code if it can be written in a few minutes. If you are unable to do the coding yourself with the starter routines provided above, you should consider hiring someone who can.
You are the project manager of this project. We are providers of very specific and focused answers to questions. Chunk the problem down into pieces appropriate for an Experts Exchange question and ask as many questions as you want. If you manage the project well you might even get us to actually write all the code for you. But you need to put the pieces together and make it work. Also remember that we only know what you tell us.
Check this out: http://www.kittytours.org/calendarproject/ It's the story of one individual who built a rather complex tool with the help of many experts here. She provided the project management and we provided the specific answers to specific questions. Note her first question she asked and the response (or lack of a response ;-)
Kevin
I gave you code that will do what you want. Unfortunately you have not provided enough information to give an actual working solution. For example, all you have said is you have a table in an Excel worksheet and you want to move it to a SQL Server database. We don't know how many columns are in the table. We don't know the table name or the names of any of the fields in the table. We don't know to what fields you want to copy what columns. We don't know how to build a connection string for you since we don't know the server name, the database name, or the login credentials (and please don't post them here ;-)
What we have given you are templates that you can fill in with your specifics. Add my code to any general code module. Follow the instructions in the code to complete the missing elements such as the table and field names. Run the macro and see if it works. Use the debugger to figure out what the bugs are and fix them.
Please keep in mind that we are not a software house. We are not consultants. We answer questions. We only write code if it can be written in a few minutes. If you are unable to do the coding yourself with the starter routines provided above, you should consider hiring someone who can.
You are the project manager of this project. We are providers of very specific and focused answers to questions. Chunk the problem down into pieces appropriate for an Experts Exchange question and ask as many questions as you want. If you manage the project well you might even get us to actually write all the code for you. But you need to put the pieces together and make it work. Also remember that we only know what you tell us.
Check this out: http://www.kittytours.org/calendarproject/ It's the story of one individual who built a rather complex tool with the help of many experts here. She provided the project management and we provided the specific answers to specific questions. Note her first question she asked and the response (or lack of a response ;-)
Kevin
Now for some specific answers...
>when i try to run the macro.i get an error saying User-defined type not defined.
That's probably because you did not include a reference to "Microsoft ActiveX Data Objects x.x Library".
>run time error 3265 "item not found in this collection"
That's because you have note customized the code for your specific situation. Those are actual table field names. Let's say you want to move the value in column to field "Name":
MyRecordset!Name = Sheets("Sheet4").Cells(Row , "A").Value
It's that easy. Try this macro AFTER changing Field1, Field2, etc. to the actual table names in the SQL Server database:
Public Sub AddRecordsToDB()
Dim MyDatabase As ADODB.Connection
Dim MyRecordset As ADODB.RecordSet
Dim Row As Long
' Open database connection and recordset
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "PROVIDER=SQLOLEDB;(local) ;INITIAL CATALOG=pol_employeesalary ;INTEGRATE D SECURITY=sspi;"
MyRecordset.Open "employeesalary", MyDatabase, adOpenDynamic, adLockPessimistic
' Add each row in the worksheet to the table
With Sheets("Sheet4")
For Row = 1 To .UsedRange.Row + .UsedRange.Rows.Count - 1
MyRecordset.AddNew
MyRecordset!Field1 = .Cells(Row, "A").Value
MyRecordset!Field2 = .Cells(Row, "B").Value
MyRecordset!Field3 = .Cells(Row, "C").Value
MyRecordset!Field4 = .Cells(Row, "D").Value
MyRecordset.Update
Next Row
End With
MyRecordset.Close
MyDatabase.Close
End Sub
Place the code in any general code module.
Kevin
>when i try to run the macro.i get an error saying User-defined type not defined.
That's probably because you did not include a reference to "Microsoft ActiveX Data Objects x.x Library".
>run time error 3265 "item not found in this collection"
That's because you have note customized the code for your specific situation. Those are actual table field names. Let's say you want to move the value in column to field "Name":
MyRecordset!Name = Sheets("Sheet4").Cells(Row
It's that easy. Try this macro AFTER changing Field1, Field2, etc. to the actual table names in the SQL Server database:
Public Sub AddRecordsToDB()
Dim MyDatabase As ADODB.Connection
Dim MyRecordset As ADODB.RecordSet
Dim Row As Long
' Open database connection and recordset
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "PROVIDER=SQLOLEDB;(local)
MyRecordset.Open "employeesalary", MyDatabase, adOpenDynamic, adLockPessimistic
' Add each row in the worksheet to the table
With Sheets("Sheet4")
For Row = 1 To .UsedRange.Row + .UsedRange.Rows.Count - 1
MyRecordset.AddNew
MyRecordset!Field1 = .Cells(Row, "A").Value
MyRecordset!Field2 = .Cells(Row, "B").Value
MyRecordset!Field3 = .Cells(Row, "C").Value
MyRecordset!Field4 = .Cells(Row, "D").Value
MyRecordset.Update
Next Row
End With
MyRecordset.Close
MyDatabase.Close
End Sub
Place the code in any general code module.
Kevin
ASKER
zorvek:
i am sorry for not being clear.i have sent a snap shot of my excel sheet which i want to export to sql server 2005.the code which you provided was for access.i changed the code and i managed to connect to my sql server 2005 db.but i had problem with inserting the data.can you please tell me how my code should look
http://community.webshots.com/user/zolfj
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yveau:
thanks mate for your time and help.I will go with your approach i.e SQL approach.I am more familiar with this then excel.
thanks once again.
chhers
zolf
Glad I could be of any help and thanks for the grade !