Connecting to SAS using Excel VBA in MS Office 365

[danewt]

Hi Folks, 

We have a VBA Excel macro that's been in use in our enterprise for at least 5-10 years. The macro makes use of the SASWorkspaceManager 1.1 Type Library to connect to our Workspace Server to extract SAS data into Excel. We have some beta testers of MS Office 365 who are getting errors during the SAS connection step from the same VBA macro. The chief difference is that o365 is running as a 64bit app on Win11(x64) in the beta test environment while previously it runs in 32bit MS Office Excel installed on Win10(x64).

The error message is not very helpful. Something like, "Object not registered." It is raised in the VBA code with the first reference to SASWorkspaceManager during an object declaration. I'm wondering if this a bitwise issue between o365 and the SAS library.

The users are able to connect to the SAS Metadata Server without issue from the SAS Add-in in o365 Excel.

[danewt]

The text of the VBA error is, "Class not registered."

[Sajid01]

Please have a look at this
https://communities.sas.com/t5/Developers/VBA-COM-quot-Class-not-registered-quot/td-p/716112

[ballardw]

I would suggest sharing the VBA that actually connects to SAS that you are using. With actual values for parameters not VB variables if needed. Someone with more experience in the VB world than I have may recognize part of the connection that needs to change to work in the new environment.

32-bit Office that was working might be relying on something that uses a different data engine than your Office 365  has for example. Or may have implemented something in DDE, which was deprecated by Microsoft long ago but still pops up in older code. SAS may attempt to use the DDE but other applications could interfere. So details on how the connection happens may very well be needed to reduce the topics to look at.