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
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)
Try this command at an admin command prompt where SAS is installed:
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?
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. : )
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).
You just have to search properly. Look for SASWorkspaceManager and VBA. Toss in IOM if you need to.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.