Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Macros For Excel

Posted on 2003-11-22
2
Medium Priority
?
660 Views
Last Modified: 2010-04-17
Well I need to to retrieve data from MS SQL Server Database tables and then make an update on the sheet and save it back to the tables in MS SQL Server Database using XML.
Well to make it clear I dont want to import or export data from  MS SQL Server Database.

It must work similar to ClientServer environment.

0
Comment
Question by:amiteshsinha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
Rog D earned 1500 total points
ID: 9803108
This can be done as VBA in Excel.

This will get you a connection to a sql data base.  You will have to change the Connection String to connect to another database.

(www.connectionstrings.com) will help you with other types of connection stirng


Public Sub DataToWorksheet()


'Have to set a reference to Microsoft ActiveX Data Objects

Dim cn As ADODB.Connection
Dim sCon As String
Dim aRs As ADODB.Recordset
Dim ssql As String

sCon = "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Set cn = New ADODB.Connection
cn.Open sCon

ssql = "Select * from Table"

Set rs = New ADODB.Recordset

rs.Open ssql, cn


'Loops through recordset
Do While Not (rs.EOF Or rs.BOF)
   ActiveSheet.Range("A1") = rs("Field1Name")
   rs.MoveNext
Loop

End Sub

Public Sub DataBackIn()

Dim cn As ADODB.Connection
Dim sCon As String
Dim aRs As ADODB.Recordset
Dim ssql As String

sCon = "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Set cn = New ADODB.Connection
cn.Open sCon

'This will update the table
ssql = "Update tableName set Field1 = " & ActiveSheet.Range("A1")
cn.Execute ssql

End Sub


I hope this is what you were looking for....

Rog
0
 

Author Comment

by:amiteshsinha
ID: 9803511
I need to know how we can use an XMl in an excel macro for saving data into MS SQL server database
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question