Solved

Macros For Excel

Posted on 2003-11-22
2
657 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
2 Comments
 
LVL 8

Accepted Solution

by:
Rog earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?

749 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