Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get data set from Oracle SQL to Excel via VBA

2972c2ed-aca1-433f-90e2-eeb1d2851a2dMar 1 2017 — edited Mar 1 2017

Hi guys,

I have an SQL statement that works fine in Oracle SQL Developer

select
     *
from articles
where
     artnr in (123, 234, 345)

I want to call this statement from VBA which works if I do it like this:

Sub ConnectToOracle

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim arr As Variant

connstr
= "Provider=msdaora;Data Source=###;User Id=###;Password=###;"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn
.Open connstr

rs
.CursorType = adOpenForwardOnly
rs
.Open ("select * from articles where artnr in (123, 234, 345)"), cn

arr
= rs.GetRows
arr
= transposeArray(arr)

Dim x As Long
Dim y As Long
x
= UBound(arr, 1) - LBound(arr, 1) + 1
y
= UBound(arr, 2) - LBound(arr, 2) + 1

Worksheets(1).Activate
ActiveSheet.Range(Cells(1, 1), Cells(x, y)) = arr

'Close connections
Set rs = Nothing
Set cn = Nothing

End Sub

However, my actual SQL-code is much longer and I want to use a stored procedure to store the select-statement on the server and then call it from VBA.

How would I do this? I can't find any online documentation on how to call regular SQL statements from VBA and get a record set. Most of them only feature single variables.

Thanks!

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 29 2017
Added on Mar 1 2017
7 comments
8,730 views