Returning data from sql server to excel using vba

AnnRed

New Member
Joined
Sep 13, 2006
Messages
3
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 :confused:

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


Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Where are you assigning a workbook to SLottsRdInventoryPostAD, eg?

Set SLottsRdInventoryPostAD = Workbooks("SLottsRdInventoryPostAD.xls")
 

AnnRed

New Member
Joined
Sep 13, 2006
Messages
3
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!!!

Code:
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
 

Forum statistics

Threads
1,136,266
Messages
5,674,727
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top