Solved

Macros For Excel

Posted on 2003-11-22
2
658 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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?

751 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