• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 901
  • Last Modified:

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
0
zolf
Asked:
zolf
  • 12
  • 9
  • 5
  • +2
1 Solution
 
frankyteeCommented:
you can create an ADO connection to your SQL server and add the records into whatever SQL table.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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("QueryTextParam", adVarChar, adParamInput, 10, "Value")
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", 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.AbsolutePosition
      MyRecordset.MoveNext
   Wend

   ' Copy the entire recordset to a worksheet (this technique does not copy field names)
   Sheets("Sheet1").[A2].CopyFromRecordset 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
0
 
YveauCommented:
... 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.xls";Extended Properties=Excel 8.0')...[YourArea]

Hope this helps ...

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
YveauCommented:
... Make sure to configure the option to use opendatasource in advance:

sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

Hope this helps ...
0
 
zolfAuthor Commented:

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.
0
 
zolfAuthor Commented:

i also activated the adhoc thingy from the surface area config
0
 
YveauCommented:
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 ...
0
 
YveauCommented:
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 ?

...
0
 
zolfAuthor Commented:


zorvek:
can you tell me where i can find the thingy which Yveau: mentioned in his comment.
0
 
Jeroen RosinkCommented:
The defined name option is hidden under toolbar Formula>> Define Name
0
 
zolfAuthor Commented:


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\test4.xls";Extended Properties=Excel 8.0')
0
 
zolfAuthor Commented:

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.Jet.OLEDB.4.0',
   'Data Source=C:\Documents and Settings\Zolf\Desktop\test4.xls;Extended Properties=Excel 8.0')...Sheet1$
0
 
zolfAuthor Commented:


what is this ...Sheet$ which needs to be there in the query without which i get that error
0
 
YveauCommented:
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.xls";Extended 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 ...
0
 
zorvek (Kevin Jones)ConsultantCommented:
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
YveauCommented:
... 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 ...
0
 
zolfAuthor Commented:

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\database.mdb'.
where do i find this in sql server 2005.i mean where is the db saved so i can point it to it.



0
 
zolfAuthor Commented:
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").CopyFromRecordset rsPubs

'Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
0
 
zolfAuthor Commented:

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, "A").Value

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, "A").Value
      rsPubs!Field2 = Sheets("Sheet4").Cells(Row, "B").Value
      rsPubs!Field3 = Sheets("Sheet4").Cells(Row, "C").Value
      rsPubs!Field4 = Sheets("Sheet4").Cells(Row, "D").Value
      rsPubs.Update
   Next Row
0
 
YveauCommented:
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.xls";Extended Properties=Excel 8.0')...[YourArea]

Hope this helps ...
0
 
zolfAuthor Commented:

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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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;INTEGRATED 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
0
 
zolfAuthor Commented:


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
0
 
YveauCommented:
You shouldn't write the query every time.
Just create a stored procedure and run that from the SQL Server when the new file is in place. Something like:

create stored procedure ImportFromExcel
as
begin
    insert into YourTable
    (col1, col2, col3)
    SELECT  *
    FROM    OPENDATASOURCE
    (       'Microsoft.Jet.OLEDB.4.0'
    ,       'Data Source="C:\Path\To\File.xls";Extended Properties=Excel 8.0')...[YourArea]
end
go

Then when needed, run:
execute ImportFormExcel

... that shouldn't be to hard, right ?

Are you going for the Excel approach or the SQL approach ?

0
 
zolfAuthor Commented:

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
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 12
  • 9
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now