Select data from mssql using excel vba and where statement

Question

I need select data from database, using Excel. I want to select only one column from database and I need use where statement (to select only data which have the same unique value as data in Excel sheet)

I tried this

    Sub DB_RTVresult()
Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String

    Dim myRes As ADODB.Recordset
    Server_Name = "123" ' Enter your server name here
    Database_Name = "DBName" ' Enter your database name here
    User_ID = "UserName" ' enter your user ID here
    Password = "Pass" ' Enter your password here

    Set myCon = New ADODB.Connection

Worksheets(2).Select
LastRow = GetRowCnt
For bl = LastRow To 5 Step -1
myCon.ConnectionString = "Driver={SQL Server};Server=" & Server_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";Database=" & Database_Name & ";"
myCon.Open
     SQLStr = "SELECT [RTV_RESULT]" & _
  "FROM [WSCZWMS].[WSCZWMS].[OMEGA_E2E_REPORT] WHERE [SME_TRACK_NO] ='" & Cells(bl, "CC").Value & "'"
    Set myRes = myCon.Execute(SQLStr)

   Worksheets("HelpTables").Range("E2" & Rows.Count).End(xlUp).Offset (1)
    StrQuery = "OMEGA_SPEQ_REPORT"

myCon.Close

Set myRes = Nothing
Set myCon = Nothing
Next

End Sub

But when I run it, it writes Application-define or object-define error. and colored this line

Worksheets("HelpTables").Range("E2" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset myRes

This line dave the select data into first empty row in column E, (start on 3rd row)

Do you have any idea how to solve this?

miwin · Accepted Answer · 2019-12-16 15:26:30Z

0

The reason for your issue is, that you are already referring to the Row in your .Range("E2" & Rows.Count) .

If you modify the mentioned line to the following, it should work properly:

Worksheets("HelpTables").Cells(Rows.Count, 5).End(xlUp).Offset(1).CopyFromRecordset myRes

The 5 in .Cells(Rows.Count, 5) is for the fifth column ('E').

answered Dec 16, 2019 at 15:26

miwin

1,21511 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pierre_J44000 · Accepted Answer · 2019-12-16 15:35:04Z

0

I am not sure, but it seems that your excel objects Cells dans Rows should be affected and your sheets selected.

I would have writen

    For b1=...
        Worksheets(2).select
        ...
        ... Worksheets(2).cells(b1,"CC").value
        ...
        Worksheets("HelpTables").select
        Worksheets("HelpTables").Range("E" & _
           Worksheets("HelpTables").Range("E:E").Rows.Count).End(xlUp).Offset(1) ...
        ...

NB :

"CC" is the name of a Excel value ?

Do you want Range("E2" & rows.count) to go to E2100 if there are 100 rows ? That's why I wrote "E" and not "E2" in my proposition. But may be you wanted (... rows.count+2) ?

Hope it helps. Pierre.

answered Dec 16, 2019 at 15:35

Pierre_J44000

111 bronze badge

Collectives™ on Stack Overflow

Select data from mssql using excel vba and where statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related