strictlydata

I have this macro which queries ms sql and returns results to excel.
I also have a macro which updates sql from excel.

example

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Sub get_data()

cn.Open "Driver={SQL Server};" & "Server=127.0.0.1;" & "Database=demo;" & "Uid=User_Name;" & "Pwd=Password;"

Sql = "Select distinct product, long_description " _
& "from stock"

rs.Open Sql, cn

r = 2
Do Until rs.EOF
Range("A" & r).Value = rs(0)
Range("B" & r).Value = rs(1)

r = r + 1
rs.movenext

Loop

Range("A1").Select

rs.Close
cn.Close

End Sub

Has anyone got an idea on how this can be changed to a .net com addin

Thanks



Re: Visual Studio Tools for Office convert vba to excel addin

Ji Zhou – MSFT

Hi,

They are almost the same. Just connect to your data base and execute command line to retrieve the data which will be used to fill Excel file. You can find a lot of references on MSDN about how to use the SqlDataReader(http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(VS.71).aspx ). To use the data fill the cells of Excel, you can use this statement:

Code Snippet
this.Application.get_Range("A1" , missing).Value2 = sdr[0];

The whole sample codes I write are as follows: (This example retrieves the ContactName in Northwind’s Customers Table, and fill the column A in the worksheet)

Code Snippet

private void ThisAddIn_Startup(object sender, System.EventArgs e)

{

SqlConnection con = new SqlConnection(

"Data Source=SHA-JZHO-XP;Initial Catalog=Northwind;Integrated Security=True");

con.Open();

SqlCommand com = new SqlCommand("Select ContactName from Customers");

com.Connection = con;

SqlDataReader sdr = com.ExecuteReader();

int i = 1;

while (sdr.Read())

{

this.Application.get_Range("A" + i.ToString(), missing).Value2 = sdr[0];

i++;

}

}

Hope it helps!

Thanks

Ji






Re: Visual Studio Tools for Office convert vba to excel addin

strictlydata

Hi Ji

Thanks for your help it works well now