Vlookup against SQL database

[savindrasingh]

Hello Experts:

I am using MS Excel 2007 on Win Xp. I have an excel sheet which have list of servers in column A starting from cell address A2.

I have a database instance from which I need to lookup for the server contact person and application contact person email address for each server listed there.

Below are the details of database and table from which I have to get the information:

Servername: CRBPRODKESP\SISP03<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Authentication: Windows Authentication<o></o>
Database name: Runbook database
Table names: dbo_QRB_AppSupport (this is for Application contact person email address) and dbo_QRB_Customer (this is for Server contact person email address)<o></o>

Can any one please provide me with VBA code to extract this information.

Currently I have imported the tables to MS Access, created one input table and using below query to get this information and then copy and paste it to Excel.

SELECT InputServers.ServerName, dbo_QRB_Customer.EmailAddr, dbo_QRB_AppSupport.EmailAddr
FROM (InputServers INNER JOIN dbo_QRB_Customer ON InputServers.ServerName = dbo_QRB_Customer.ServerName) INNER JOIN dbo_QRB_AppSupport ON dbo_QRB_Customer.ServerName = dbo_QRB_AppSupport.ServerName;

I am after a code which I can run in Excel to find the Application and Server contact information from given Database/tables and fill the same next to the server name on the same sheet.

 

[xenou]

The best site I know of for information on querying databases from Excel is here:
http://www.erlandsendata.no/english/index.php?t=envbadac

Also valuable if using ADO as the means to connect to the database is the w3schools page on ADO:
http://www.w3schools.com/ado/default.asp

Using ADO it does not matter (more or less) whether the database is Access, SQL Server, or another database - the connection string is different depending on the database, but once the connection is open then the rest is very similar no matter what kind of database. Although if you have the information in Access already it may be best to work in Access (if possible).

Good luck - this isn't very complicated but then again its all a bit confusing when you first try.

 

[savindrasingh]

Hello All,

I got the clue from above links and had managed to write the required code. I am posting this code so that someone who need this will get benefited by this:

Sub Import()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
 
    Set TargetRange = Range("A2")
    Set Conn = New ADODB.Connection
 
    Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Runbook;Data Source=[COLOR=black]CRBPRODKESP\SISP03[/COLOR]"
    Set RecSet = New Recordset
 
    RecSet.Open "SELECT EmailAddr FROM Runbook.dbo.QRB_AppSupport WHERE ServerName='" & Range("A2").Value & "'", Conn, , , adCmdText
    TargetRange.Offset(0, 3).CopyFromRecordset RecSet
 
    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
    Set Conn = Nothing
End Sub