Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Connecting EXCEL VBA with SAS

Reply
Occasional Contributor vps
Occasional Contributor
Posts: 14

Connecting EXCEL VBA with SAS

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

Esteemed Advisor
Esteemed Advisor
Posts: 6,685

Re: Connecting EXCEL VBA with SAS

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.

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Connecting EXCEL VBA with SAS

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.

 

Esteemed Advisor
Esteemed Advisor
Posts: 6,685

Re: Connecting EXCEL VBA with SAS

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?

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Connecting EXCEL VBA with SAS

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"
Esteemed Advisor
Esteemed Advisor
Posts: 6,685

Re: Connecting EXCEL VBA with SAS

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.

Post a Question
Discussion Stats
  • 5 replies
  • 443 views
  • 0 likes
  • 2 in conversation