topic Re: Connecting EXCEL VBA with SAS in SAS Visual Analytics

Connecting EXCEL VBA with SAS

vps — Mon, 02 Nov 2015 11:59:30 GMT

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

Re: Connecting EXCEL VBA with SAS

RW9 — Mon, 02 Nov 2015 12:12:21 GMT

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.

Re: Connecting EXCEL VBA with SAS

vps — Mon, 02 Nov 2015 12:15:12 GMT

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.

Re: Connecting EXCEL VBA with SAS

RW9 — Mon, 02 Nov 2015 12:58:15 GMT

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?

Re: Connecting EXCEL VBA with SAS

vps — Mon, 02 Nov 2015 13:13:23 GMT

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"

Re: Connecting EXCEL VBA with SAS

RW9 — Mon, 02 Nov 2015 13:36:01 GMT

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.

Re: Connecting EXCEL VBA with SAS

antontansengco — Mon, 25 Jan 2021 06:49:04 GMT

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