Returning data from sql server to excel using vba

[AnnRed]

Hi,
Can anyone help me? I need to connect to a sql server database and return information into a column. I'm getting an error with this code - Object variable or with block variable not set

Here is what I've come up with so far with the help of a guy in another forum:

Sub Return() 
    Dim vRange As Range 
    Dim Acell As Range 
    Dim ws As Worksheet 
    Dim vSelect As String 
    Dim vVmsApp As String 
    Dim SLottsRdInventoryPostAD As Workbook 
    Set ws = SLottsRdInventoryPostAD.Worksheets("ITSec") ' change to your sheet
    vVmsApp = Range("ITSec!G1").Value 
    Set vRange = ws.Range("A2:A6") 
    For Each Acell In vRange 
        If Not IsEmpty(Acell) Then 
            vSelect = "Select [Timestamp] from dbo.vVms where Username = '" & Acell.Value & "' and Application = '" & vVmsApp & "';" 
            objconn.Open vConn 
            Set objData = objconn.Execute(vSelect) 
            Acell.Value = objData(0) 
            Debug.Print objData(0) 
        End If 
    Next Acell 
End Sub

 

[Andrew Poulsom]

Where are you assigning a workbook to SLottsRdInventoryPostAD, eg?

Set SLottsRdInventoryPostAD = Workbooks("SLottsRdInventoryPostAD.xls")

 

[AnnRed]

Querying Sql from excel - a Result!

Hi,

I thought I'd share this with you all as no-one seemed to know how to do this. I managed to get it working!!!

Dim objconn As ADODB.Connection

Sub main()
Dim vConn As String
Dim objdata As ADODB.Recordset
Set objconn = New ADODB.Connection
Set objdata = New ADODB.Recordset
vConn = "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=S;User ID=X;Password=Y"
objconn.Open vConn
For Each cell In Range("a2:a3000")
    cell.Activate
    If IsEmpty(cell.Value) Then
        Exit For
    End If
    
    Debug.Print cell.Value, ActiveCell.Row
    
    If Not IsEmpty(cell.Value) Then
        Worksheets("Sheet1").Cells(ActiveCell.Row, 3).Value = DataReturn(cell.Value, Worksheets("Sheet1").Cells(1, 3))
        Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value = DataReturn(cell.Value, Worksheets("Sheet1").Cells(1, 4))
        Worksheets("Sheet1").Cells(ActiveCell.Row, 5).Value = DataReturn(cell.Value, Worksheets("Sheet1").Cells(1, 5))
    End If
Next
Worksheets("Sheet1").Cells(1, 1).Activate
objconn.Close
End Sub


Function DataReturn(vUser, vApp)
Dim vselect As String
vselect = "Select [Timestamp] from dbo.vVMS where username = '" & vUser & "' and application = '" & vApp & "';"
Set objdata = objconn.Execute(vselect)
If objdata.EOF And objdata.BOF Then
    DataReturn = "Not a User"
Else
    DataReturn = objdata(0)
End If
End Function