VBA for picking only one value from an SQL base

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Hi,

I want to pick only one value from an SQL base. I know how to bring a whole tabel to Excel. I use:

Code:
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=192.168.0.10\SQL;UID=sa1;PWD=password;APP=System operacyjny Microsoft® Windows®;WSID=User1;DATABASE=Wyposaz" _
        ), Array("enie")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT S_operatorzy.ID, S_operatorzy.Login, S_operatorzy.Phraser, S_operatorzy.Upr_sprzet_inf, S_operatorzy.Upr_sprzet_mod, S_operatorzy.Upr_odziez_inf, S_operatorzy.Upr_odziez_mod, S_operatorzy.Upr_p" _
        , _
        "ersonel_inf, S_operatorzy.Upr_personel_mod, S_operatorzy.Upr_alerty" & Chr(13) & "" & Chr(10) & "FROM Wyposazenie.dbo.S_operatorzy S_operatorzy" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_Kwerenda_z_sql22"
        .Refresh BackgroundQuery:=False
    End With
End Sub

As you can see, I can get all those columns into a sheet with headers. What I want to do is bring only one value without a header that I can assign to A1 cell or a variable.

I tried something like:
Code:
Set cnn = CreateObject("ADODB.Connection")

    cnn.Open "Driver={SQL Server};Server=SARA;UID=sa1;Password=password;Database=Wyposazenie"
    nSQL = "SELECT S_operatorzy (S_operatorzy.Login" & vbNewLine & "WHERE S_operatorzy.Login 'mylogin')"
    cnn.Execute nSQL
Range("A1").Value = cnn

However, it does not work. Can you please help me?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

anvg

Active Member
Joined
Feb 14, 2012
Messages
485
Hi
You have some mistakes in your code. Try with
Code:
Set cnn = CreateObject("ADODB.Connection")

    cnn.Open "Driver={SQL Server};Server=SARA;UID=sa1;Password=password;Database=Wyposazenie"
    nSQL = "SELECT S_operatorzy FROM Wyposazenie.dbo.S_operatorzy WHERE S_operatorzy.Login = 'mylogin'"
    Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value
    cnn.Close
[COLOR=#222222][FONT=Verdana]
Regards,[/FONT][/COLOR]
 

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Thank you very much. I will check this code on Monday and I will write you if it works.

What does this part of code mean?
Code:
Options:=2048)(0)

You have some mistakes in your code
Oh really? I did not expect that :P

Regards!
 

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Ok, so unfortunately I get the run-tume errro 3001. The VBE highlights the line:
Code:
Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value

Any ideas?
 

Damian86

New Member
Joined
Jun 30, 2014
Messages
47

ADVERTISEMENT

Ok, so doesn't the error occur when I try to insert
Code:
cnn = CreateObject("ADODB.Connection")
into
Code:
Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value
?

I mean that a cell cannot hold an object as a value. Am I right? Maybe cnn.Execute.Value does not give a value?
 
Last edited:

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
The answer is:

Code:
Sub FetchRecord()

Dim Conn As Object
Dim ConnStr As String
Dim RecSet As Object
Dim SqlStr As String

Set Conn = CreateObject("ADODB.connection")
ConnStr = "Driver={SQL Server};Server=SARA;UID=sa1;Password=password;Database=Wyposazenie"
Set RecSet = CreateObject("ADODB.Recordset")
SqlStr = "SELECT S_operatorzy.Login FROM Wyposazenie.dbo.S_operatorzy S_operatorzy WHERE S_operatorzy.Login='mylogin'"

Conn.Open ConnStr

RecSet.Open SqlStr, Conn
Range("A1").Value = RecSet(0)

Conn.Close
Set Conn = Nothing
End Sub

I've changed the names of the variables. Regards.
 

Forum statistics

Threads
1,141,480
Messages
5,706,638
Members
421,460
Latest member
Taamrak

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