I post this with fingers crossed, as I saw someone else posted about this a long time ago - got no responses at all.
I'm trying to use the information on this page: Programming with Visual Basic (sas.com)
...in order to use VBA to access some of the SAS capabilities in Excel.
I run this code and get "CLASS NOT REGISTERED" - as if I don't even have sas on my system. But I do! I can open Enterprise Guide, etc. I also have checked the two reference libraries that Programming with Visual Basic (sas.com) suggests to do.
error on bolded line
Code:
Sub Test()
Dim obWSMgr As New SASWorkspaceManager.WorkspaceManager
Dim obWS As SAS.Workspace
Dim strWorkspaceErrors As String
Set obWS = obWSMgr.Workspaces.CreateWorkspaceByServer("My workspace", VisibilityNone, Nothing, "", "", strWorkspaceErrors)Debug.Print obWS.Utilities.HostSystem.DNSName
obWS.CloseEnd Sub
Try this command at an admin command prompt where SAS is installed:
sas /regserver
Also, consider not using VBA and switch to C#. It is easier and way more powerful than VB*(anything). VB has no future, per Microsoft.Works but best to switch now.
Thanks for the tip on sas /regserver
I'd like to follow up on that recommendation. I am on a corporate-provided laptop at a BIIIIIIIG company. Our stuff is locked down in every imaginable way. I am unable to open the command prompt as administrator.
However, I would like to at least try to follow your advice before I involve Desktop Support at my company to run the command.
When you say "...where SAS is installed", do you mean that I have to open the command prompt...and change the current directory to the directory/folder where SAS is installed? Or can you be more specific on that? Or did you just mean, do this on the machine where SAS is installed?
Because did try running it and got 'sas' is not recognized as an internal or external command...etc.
I agree with you - this seems to be a matter of something, perhaps a DLL, not being fully registered. Can I directly register the DLL itself using command prompt?
I agree with you and know that c# is the future, but right now in my current context, I have to try to use VBA in an Excel workbook. I will try to learn c#.
I am not sure about sas.exe. What I have on my laptop installed, is enterprise guide. so when I open enterprise guide, then open task manager, then right click on EG process and go to file location, it's: C:\Program Files (x86)\SAS94\x86\SASEnterpriseGuide\7.1
is that what you ask?
This is SAS-related, not VB or VBA (different things. There is also VB.NET).
It has to do with the COM libraries used with SAS/Integration Technologies. When you are using SAS/EG, you are most likely connecting to a SAS server somewhere. Hence, you are not using a local version of SAS. SAS is executing elsewhere. SAS has 3 providers (Local, Share, and IOM). You need to find examples using IOM, not local. You have to execute your SAS code on a remote instance and then get the data back.
Look for articles by Chris Hemendinger on IOM and VBA. You can also glean some knowledge from his articles on VBScript (another variant), PowerShell, C#, etc. Share may also work but IOM is probably your best bet.
EG does not have to use a SAS Server but based upon your comments, it probably does.
Any chance you can get a local instance of SAS?
can't seem to find Chris's book on amazon or anything about chris on the internet ?
google 'no results'
I assume we're both using the same Google 🙂
If you try googling his name with iom and vba, you'll see there are zero results except instances where you told people to search for him
And if you search Amazon.com, there are no results at all - just a similarly named person who wrote books on Tennis Shoes.
So thanks for the link - very helpful, Amazon's search feature sucks I guess. : )
Thanks. So far I have only found books he has written on - the closest I can get is .NET and IOM, but not VBA and IOM.
😞
I would choose the latter. VB6/VBA have nothing in common with .NET
I know … that's why I got excited earlier when you indicated Chris had written on the subject, but I am not finding that to be the case. If you actually do find anything he's written on VBA with SAS, let me know. Thanks.
Between MS butchering their HTML links in current releases and SAS stripping it out, the links are wrong (mainly blame MS).
Look here:
Creating a workspace session using SAS IOM & VBA - Stack Overflow
You just have to search properly. Look for SASWorkspaceManager and VBA. Toss in IOM if you need to.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.