SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Connecting to SAS from Excel using VBA and ActiveX Data Objects

Reply
New Contributor
Posts: 2

Connecting to SAS from Excel using VBA and ActiveX Data Objects

I'm trying to set up an Excel model that has a number of user inputs. Based on these inputs I would use VBA to create some SQL code that would then get passed to SAS and the resulting output pulled back to Excel using ActiveX Data Objects.

 

I'm asking if anyone has some example code that would establish the connection assuming I am working at an office with SAS and data stored in servers external to my local workspace. I know this is as general as it gets but I've done it before I just can't recall the exact VBA code to use. I know I need the servername and the port but beyond that I'm not sure. Any help would be appreciated.

 

I'm on SAS 9.4 and Excel 2016. I'm trying to avoid any Excel Add-Ins that would require user input.

Super User
Super User
Posts: 7,668

Re: Connecting to SAS from Excel using VBA and ActiveX Data Objects

Errm, so your using Excel as your datase, then via VBA are generating some SQL, which you pass through to SAS to create something, which then passes back to Excel?  That sounds a bit over complex.  Can you not agree on one technology, for example:

Use SAS to do things then export a final report to Excel.

Import all data to a database, then either export or pull from to Excel.

 

Whilst it may be possible, your reliant on various different technologies and interdependancies with that method.

New Contributor
Posts: 2

Re: Connecting to SAS from Excel using VBA and ActiveX Data Objects

The issue is that my business users are not SAS proficient and don't have time to learn it. Generating the SQL to create the dataset using VBA is simple. And your solutions do not rely on one technology either. My solution only requires a user to open the excel file, select some drop-downs and click a button. I've done it before it's just a matter of setting up the connection.

 

I appreciate the response.

Super Contributor
Posts: 384

Re: Connecting to SAS from Excel using VBA and ActiveX Data Objects

Take a look at my paper from SAS Global Forum 2014 where I do essentially what I think you want http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf

 

Chris

Ask a Question
Discussion stats
  • 3 replies
  • 241 views
  • 0 likes
  • 3 in conversation