VBA/COM "Class not registered"

[ipisors]

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.Close

End Sub

[AlanC]

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.

[ipisors]

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#.

[AlanC]

Where is sas.exe being executed?

[ipisors]

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?

[AlanC]

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?

[ipisors]

can't seem to find Chris's book on amazon or anything about chris on the internet  ?

google 'no results'

[AlanC]

Well, he runs this site 😉 Here are some links:

Chris Hemedinger, Author at SAS Blogs

https://www.amazon.com/Chris-Hemedinger/e/B003579A9M?ref_=dbs_p_pbk_r00_abau_000000

Also look for the Integration Technologies Cookbooks. Here is an example (it is 9.3 but still valid):

Connecting to a Remote SAS Workspace Server Using SAS Objects - 9.3

[ipisors]

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.  : )

[AlanC]

Yeah, I don’t use Google for search. I am a Bing guy (SAS/Microsoft consultant)

…plus, I hate Google 😉

[ipisors]

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. 

😞

[AlanC]

He wrote a lot (I believe) where his name is not attributed.



You need to try and convert the .NET to VBA. There is no easy way. You can
check here for a possible scenario:



VB6 Migration Tool for .NET
<>ize.net%2Fproducts%2Fapp-migrations%2Fvbuc&data=04%7C01%7Ccommunity.admin%40
sas.com%7C7060699a9c084c43e2d908d8cc7756b0%7Cb1c14d5c362545b3a4309552373a0c2
f%7C0%7C0%7C637484160959504973%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiL
CJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=Ts2xmJDcbnOkjOppp
3aH6oQeg3b5AJiAEqtv3mxvMC8%3D&reserved=0> & C# Converter - VBUC |
Mobilize.Net



..or, 1) switch to C#.



I would choose the latter. VB6/VBA have nothing in common with .NET. You can
call the dlls but almost all examples for working with SAS are in C#,
including mine.



Thanks,

Alan

[ipisors]

---

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.

---

[AlanC]

Well, he has but his name is probably not on it. It was a long time ago.
Look at LexJansen's site which has the SUGI/SGF archives. For example,

WUSS02.tif (lexjansen.com)
<>

The technique you need is old but the papers are out there. Find some
keywords and go searching. For example, VBA/SAS
Cookbook/LanguageService/SASWorkspaceManager

I have some code I am happy to share on IOM but it is in C#. You need to
translate that to VBA. You are swimming uphill, however, since what you need
to do can be done outside of VBA and there are code examples. The choice is
simple: learn some basic C# or try and find nuggets of VBA code.

You could also ignore the direct integration and submit SAS at the command
line on the system where SAS is installed and capture data using stdout.

[AlanC]

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

WUSS02.tif (lexjansen.com)

You just have to search properly. Look for SASWorkspaceManager and VBA. Toss in IOM if you need to.