how to display the rows and columns values in VBA for select query

Question

My actual code is the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
                Call extract
            End Sub
            Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            End SubSub extract()
            Dim cn As Object
                    Dim uid1, pass1, srvrnm --pass parm
            Set cn = CreateObject("ADODB.Connection")
            Dim rs As Object
            Set rs = CreateObject("ADODB.Recordset")
            Dim cmdSQLData As Object
            Set cmdSQLData = CreateObject("ADODB.Command")
            uid1 = "SSSS"   --user-id
            pass1 = "JJJJ"  --pass words
            srvrnm = "JJJJSDS" --server name
            On Error GoTo err1
            cn.Open "Driver=Teradata; DBCName="& srvrnm& ";uid="& uid1& ";AUTHENTICATION=ldap;pwd="& pass1& "; Trusted_Connection=True"
            Set cmdSQLData.ActiveConnection = cn
            On Error Resume Next
            query1 = "select tablename,databasename as xx from DBC.TABLES sample 2;"
            cmdSQLData.CommandText = query1
            cmdSQLData.CommandTimeout = 0
             Set rs = cmdSQLData.Execute()
            Sheet1.Cells(2, 1) = rs.Fields(0).Value
            Set rs = Nothing
            err1:
            End Sub

My question is how to get database table result in excel rows and column with as many rows as in table and consequent number of columns

Can you please indent the code properly, it is hard to follow what going on there — litelite, May 17 '16 at 18:50
can it possible to select the "multiple SELECT STAMENTS" in above VB. and there result will be stored in the same excel with the respective table name? — Santosh Patil, May 26 '16 at 16:40

Tim · Accepted Answer · 2016-05-18T18:27:23.793

0

You need to loop through your recordset and display the data. So where you have this:

    Set rs = cmdSQLData.Execute()
    Sheet1.Cells(2, 1) = rs.Fields(0).Value
    Set rs = Nothing

You need something like this:

Dim r as Integer 'Row
Dim c as Integer 'Column

For c = 0 To rs.Fields.Count - 1
    'This will start on Row 1, Column A (due to + 1) and place all the field headers
    Sheet1.Cells(1, c + 1).Value = rs.Fields(c).Name
Next c

r = 3 'We'll start on Row 3
Do While Not rs.EOF
    For c = 0 to rs.Fields.Count - 1
        'c + 1 means we're starting on Column 1 (A)
        'If we wanted to start in Column d, we would use c + 4
        Sheet1.Cells(r, c + 1).Value = rs.Fields(c) 
    Next 
    r = r +1
    rs.MoveNext
Loop

Regarding the comments: To add the field headers, you create an initial loop and simply do not move to the next record. See the modified code above.

edited May 18 '16 at 18:27

answered May 17 '16 at 18:55

Tim

2,701
3
26
47

but I need one more help ..how to display first rows(first rows name) – Santosh Patil May 18 '16 at 17:35
Like the column header? that is the `.Name` property of the recordset field. EG: `rs.Fields(c).Name` – Tim May 18 '16 at 18:16
yup I need column header ...where I need to call rs.Fields(c).Name – Santosh Patil May 18 '16 at 18:22
btw, If you add a reference (In the IDE click Tools -> References) to `Microsoft ActiveX Data Objects x.x Library` (your choice as to which one), you can use early binding (eg: `Dim rs as ADODB.Recordset` instead of `Dim rs As Object`) to take advantage of Intellisense and get coding tips and hints for tons of different properties and features. – Tim May 18 '16 at 18:36
can it possible to select the "multiple SELECT STAMENTS" in above VB. and there result will be stored in the same excel with the respective table name? – Santosh Patil May 26 '16 at 16:05
@SantoshPatil I don't understand the question. Can you call SELECT multiple times and store the reulst on the same worksheet? Of course. Just create a new SELECT statement and change where you place the data on the sheet (the r and c variables we use to denote the row and column). – Tim May 26 '16 at 16:47
no Tim..can I select the multiple select staments like:-SELECT * FROM TABLE 1 AND SELECT count(*) FROM TABLE2 AND SELECT * TABLE3 WHERE COLUMN NAME="ABC"....In the Result set Tablel1 result and Table2 count in excel...can we achieve this in my above VB. – Santosh Patil May 26 '16 at 17:04
Yes, you can do this: http://stackoverflow.com/questions/1416003/sql-select-from-multiple-tables – Tim May 26 '16 at 17:16
no Tim..can I select the multiple select staments like:-SELECT * FROM TABLE 1 AND SELECT count(*) FROM TABLE2 AND SELECT * TABLE3 WHERE COLUMN NAME="ABC" in my above VB scripts ....we need to capture select result with table name in excel.... – Santosh Patil May 26 '16 at 17:24
@SantoshPatil Yes...did you read the link I just posted?? It shows you how to SELECT from multiple tables. That SELECT still just returns a recordset. My answer shows you how to display that recordset. You just need to put the two together. – Tim May 26 '16 at 17:55
I went the link which you have given,in my first post which is related VB I am selecting the one table from database in above VB scripts and putting the result in excel ....my question how I can select multiple select statements from database put there results in the excel with there respective table name in above VB scripts .. – Santosh Patil May 26 '16 at 17:59
Then I'm sorry, I still don't understand your question. Since it seems to be completely different from displaying a recordset (the subject of this page), I would recommend starting a new question where you can provide some sample data, some sample code, and a description of what your desired result is. The comments section of this post is not going to be conducive to that conversation. :) – Tim May 26 '16 at 18:04

score 0 · Answer 2 · answered May 18 '16 at 07:32

0

The fastest way to copy the entire recordset to an excel sheet should be:

Sheet1.Cells(2, 1).CopyFromRecordset rs

answered May 18 '16 at 07:32

Jochen

1,254
1
7
9

how to display the rows and columns values in VBA for select query

2 Answers2