get a result of an sql into a field

  • Thread starter Thread starter Ralf Meuser
  • Start date Start date
R

Ralf Meuser

Hi there

I would like to get a result of an sql execution (ms sql server) into aq
filed.

example i A1 I have a ID number
in A2 I would like to get the result of something like this 'select name
from address where id=A1'

Does this exist in Excel ?

Thanks in advance


Ralf
 
Here is the sub i have written for loading an Sql Query into th
worksheet.

Parameters:
Server Name
DataBase Name
SQL Command
Target Sheet name
Column to begin from
Row to begin from

ex: CALL LoadData("MyServer","MyDataBase","Select UserName fro
TblNames",
"QueryData",4,2)

**You will need to add a reference to MS ActiveX Data objects from th
VBA Editor.

Sub LoadData(strServerName As String, strDbName As String
strSQLCommand As String, strSheetName As String, intBeginColumn a
Integer, intBeginRow As Integer)
Application.ScreenUpdating = False
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Integrate
Security=SSPI;Persist Security Info=True;Initial Catalog=" & strDbNam
& ";Data Source=" & strServerName & ";Use Procedure for Prepare=1;Aut
Translate=True;Packet Size=4096"
objMyConn.Open
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = strSQLCommand
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
ctr = 0
ThisWorkbook.Sheets(strSheetName).Select
For Each fld In objMyRecordset.Fields
ctr = ctr + 1
ThisWorkbook.Sheets(strSheetName).Cells(intBeginRow, intBeginColumn
ctr).Value = fld.Name
Next fld
ThisWorkbook.Sheets(strSheetName).Range("A" & intBeginRow
1).CopyFromRecordset (objMyRecordset)
ThisWorkbook.Sheets(strSheetName).Cells.Select
Selection.Columns.AutoFit
ThisWorkbook.Sheets(strSheetName).Cells(intBeginRow
intBeginColumn).Select
End Su
 
Back
Top