Connecting EXCEL VBA with SAS

[vps]

Hi - 

I am trying to connect SAS from VBA and fetch the data to excel spread sheet. Below is the code what I am trying.

Public Sub GetData()

Dim obConnection As ADODB.Connection
Dim obRecordset As ADODB.Recordset
Dim i As Integer

Set obConnection = New ADODB.Connection
obConnection.Provider = "sas.LocalProvider.1"
obConnection.Properties("Data Source") = "sas.abc.abc.com:1234"
obConnection.ConnectionString = "Provider=SAS.IOMProvider; Data Source=iom-bridge://sas.abc.abc.com:1234; User ID=abcd;Password=1234"
Set obRecordset = New ADODB.Recordset
obRecordset.Open "proc sql; select * from aa.getdata_dim; QUIt;", obConnection, adOpenStatic, adLockPessimistic, 
End Sub

When my code try to open the recordset. Code fails and I am prompted with run time error as below.

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '3709':

Application-defined or object-defined error
---------------------------
OK Help
---------------------------

Has any one achieved this, and help me out ?

Cheers

VS

[RW9]

Yes, I don't think you can do it like that.  Datasets are not a database.  Also, you seem to be trying to use SAS commmands to select??

What is wrong with just saving the data to CSV, then you can open the CSV file via VBA.

[vps]

My intension is to build a connection string with SAS and get the data in the spreadsheet. Later on I am using those data to make some formation. I am sure, we can do this. but i am facing some difficulties. So some one must have for sure tried this out.

[RW9]

Actually yes, a quick google search results in:

http://www.lexjansen.com/phuse/2005/as/as11.pdf

http://stackoverflow.com/questions/25447567/using-vba-to-get-data-from-sas-and-limit-the-data-set

However I would still question why you want to do this.  Just export the data from SAS to CSV, SAS is not a database, hence you wouldn't want to be querying it at regular intervals like you do with a database.  When you want to use the data, run an export.  Reasons this would be a better approach - formats would not be pushed out properly and you may want to process the data before export.  

If you want access to data in the form of a database, use a database?

[vps]

Thanks for the LINK. I have already gone thru this reference and it didnt help me out.
Answering to your question. I am trying to automate something., which cannot be achieve by SAS to CSV.

Do you have any idea what is diff between
obConnection.Properties("Data Source") = "C:\path\"

and

obConnection.Properties("Data Source") = "sas.abc.abc.com:1234"

[RW9]

Well, I would still use one or the other.

As for that code, it looks like the first instance is referencing connection information as stored in a file on your local machine, whereas the second appears to be contacting a web address, however it looks like the web address you have given is just a dummy one to give an example, i.e. you would change to the URL where the connection is, and apparently a post number after the web address.  

https://msdn.microsoft.com/en-us/library/office/ff837414.aspx?f=255&MSPPError=-2147217396

You may be better off checking a ticket with the helpdesk, I don't know of anyone who tries to pull data out of a dataset with VBA, its always create an export file then import.  What is it you need to automate, there may be better options.

[antontansengco]

I think you need to add the Microsoft ActiveX Data Objects 2.8 Library to the References in VBA Project..