Error message "ActiveX component can't create object" appears for all objects

0
votes

I'm unable to create any objects in an Excel macro I am trying to write. Initially I was trying to work with MSXML2.DOMDocument60, however, I realized that i'm not able to get ActiveX to create any objects. For example, I get the same error (Run-time 429 'ActiveX component can't create object') for this line of code:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

Obviously since i'm in excel, the program is installed correctly and it should have access to it. I've checked several other Stackoverflow pages for this. It doesn't appear to be an issue with a reference and I can't imagine the simple code above not working because of missing dlls since I am again already in Excel.

Is there possibly a security feature on my computer blocking this action?

Again, my issue is not with the above code. This was just a simple way to show that Create object is not working.

My main goal is to get the following to work: Dim objXML As MSXML2.DOMDocument60 Set objXML = CreateObject("MSXML2.DOMDocument60")

I've already verified that I have the correct reference and I re-registered the Dlls.

2
excelvba
Excel worksheet objects require you to "attach" to an existing worksheet, whether it's one already present in the workbook or a worksheet you've newly created. So use something like Dim thisWS as Worksheet; Set thisWS = ThisWorkbook.Sheets("Sheet1") - PeterT
Hi PeterT. Thank you for your response. Unfortunately, that doesn't address my issue. The code above was just intended as an example. The real issue is that i appear to be blocked anytime I use "CreateObject" and I'm not sure why. I run into the same issue trying to create an object for a word document: Dim oWordApp As Word.Application Set oWordApp = CreateObject("Word.Application") - Robert
Are you using a Mac? - Tim Williams
Hi Tim. No. I am on a Windows 10 machine with a 64- bit Operating system. The version of excel i'm using is Microsoft 2016 and it is 32 bit. - Robert
Why are you late-binding ExcelSheet if you're in Excel? The type library is already referenced, have you tried seenig what happens when you do Set ExcelSheet = New Excel.Worksheet? - Mathieu Guindon

2 Answers

1
votes

I found the answer I was looking for. My company has the C drive and another network drive locked down, which prevents CreateObject from working when an excel sheet is saved to either of these locations. By saving the excel sheet to my desktop instead I am able to get it to work.

0
votes

There's no class with an Excel.Sheet progID in the Excel object model. You probably meant Excel.Worksheet, but that won't work either, and it's not because of CreateObject or any kind of obscure security feature.

Simply put, not all classes/types are creatable: In the Excel type library Application is, and that's about it: everything else needs to be created within the object model, using the factory methods provided by that API.

In other words the only way you can create an Excel.Worksheet, is if Excel creates it for you.

Set excelSheet = Excel.Application.ActiveWorkbook.Worksheets.Add

Same with an Excel.Workbook:

Set excelBook = Excel.Application.Workbooks.Add

There are other ways (e.g. copying an existing Worksheet without specifying a Target argument will create a new Workbook that contains a copy of that source Worksheet), but rule of thumb if you can't New up a class in a referenced type library, there's little chance CreateObject can do any better (unless the type registration explicitly prevents early-binding usage).

The question was edited, but this answer stands: don't use CreateObject to create instances of classes that are already resolved and readily available.

Set objXml = New MSXML2.DOMDocument60 'works fine

If you really want to use CreateObject, then you need to use progID strings that exist in your registry.

Set objXml = CreateObject("MSXML2.DOMDocument60") ' blows up on my machine as well
Set objXml = CreateObject("MSXML2.DOMDocument") ' works fine

But using CreateObject to create an instance of a class the compiler already knows about, is a very, very roundabout way to do this.