3

I want to directly write the result of my sql query in specific cells I am using ADO Excel VBA.

        SetConn     ' SET THE CONNECTION TO THE DATABASE.

        sQuery = "SELECT * FROM [referenceStandard$] WHERE [Line] = 'L1' AND [Variants] = 'Choco Wafer (L1)' AND [ID] = 'CW (L1)' "

        If rs.State = adStateOpen Then
            rs.Close
        End If

        rs.CursorLocation = adUseClient

        rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            MsgBox "Result is > 1"
            Do While Not rs.EOF

            'Codes that will copy the result of query and paste in worksheet cells

            Loop
        End If
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Arvin John Salandanan
  • 49
  • 6
  • Depending on how many rows you have there, you might want load an Object array first and then write the entire range to your sheet at once. [Check an answer here](https://stackoverflow.com/questions/680199/how-to-export-datagridview-to-excel-using-vb-net/37381927#37381927) to see an example of writing an object array to Excel in VB.net. Hopefully it gives you some ideas. – Michael Z. Apr 30 '19 at 04:24
  • Hello @MichaelZ. I want the result of the query to insert in an array and get rid off the blank/empty result. Is it possible? – Arvin John Salandanan Apr 30 '19 at 04:44
  • If you loop through every row to create the object array then you have complete control over it and could skip rows and change cell values as needed. It's fast because it's all in memory. – Michael Z. Apr 30 '19 at 04:45
  • Really you should probably handle all of that in the SQL though – Michael Z. Apr 30 '19 at 04:47

1 Answers1

4

You can use CopyFromRecordset:

rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
    ActiveSheet.Range("a1").CopyFromRecordset rs
End If

If you want something more specific/selective than that then might be useful to update your question with a few more details.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Hi it works! Thank you very much! :) But I forgot to add a question in my post that the result of the query will go to an array and get rid off empty/blank results. – Arvin John Salandanan Apr 30 '19 at 04:42
  • Seems like you need to add in some more details then. What *exactly* do you want to do? – Tim Williams Apr 30 '19 at 04:48
  • I have columns which serves as category. And rows for their specific values. Ex: Columns ID, Product, Quantity, Color, Weight. And specific value of row is 1, Wafer, 200,(emptyvalue), 2kg. I want a result of 1,Wafer,200,2kg. *Empty value automatically remove once I run the query. – Arvin John Salandanan Apr 30 '19 at 04:55
  • Add onto your SQL query "...and color is not null" That will remove records with no color specified. – Tim Williams Apr 30 '19 at 04:57
  • But I still need the whole value of row. So I'm planning to insert the result in array first. Then I can filter the result if the array has blank/empty value. Adding IsNotNull function in my query only filtered the result of my query but not the specific row I need. – Arvin John Salandanan Apr 30 '19 at 05:04
  • You can use `rs.GetRows` to create an array with your recordset values. – Tim Williams Apr 30 '19 at 05:11
  • Thanks Tim Williams. I will search about it. Have a nice day! :) – Arvin John Salandanan Apr 30 '19 at 05:15