GetObject() shows an error 432

[vbaResearch]

I use office 2016 Pro
I firstly did this by using

Private Sub ComboBox1_Change()
   Dim MyWB As Workbook
   Set MyWB = Workbooks.Open(ThisWorkbook.Path & ".\readings\" & Year(Date) & " - Table.xlsx")
   If MyWB.Sheets(1).Cells(ComboBox1.Value + 1, 2).Value = 0 Then
            TextBox1.Text = 0
            TextBox1.BackColor = RGB(200, 255, 200)
   End If
   MyWB.Close
End Sub

and it worked, but I needed it to work with less signs of opening and recently I found a thread where johnywhy (here: Link to thread) writes that GetObject works better if I dont want anything to appear.
So I want to try this. But I can not make it work because excel gives an error 432 on the line:

Private Sub ComboBox1_Change()
   Dim MyWB As Workbook
   Set MyWB = GetObject(ThisWorkbook.Path & ".\readings\" & Year(Date) & " - Table.xlsx")
   If MyWB.Sheets(1).Cells(ComboBox1.Value + 1, 2).Value = 0 Then
            TextBox1.Text = 0
            TextBox1.BackColor = RGB(200, 255, 200)
   End If
   MyWB.Close
   Set MyWB = Nothing
End Sub

Next I found that I have to check references. So I checked the Microsoft Scripting Runtime. It uses "C:\Windows\System32\scrrun.dll". And it still does not work.

But maybe the reason is that y Windows and office are 64 bit, so maybe it has to use "\Windows\SysWOW64\scrrun.dll" . Next I found that I have to register scrrun.dll from SysWOW64 folder in command line. So I opened PowerShell with administrator rights and ran these lines:

It said that it succeeded. Then I restarted Excel. and found that nothing has changed in Microsoft Scripting Runtime path, it still uses "C:\Windows\System32\scrrun.dll"

again nothing changed
I tried regsvr32 with closed Excel and even restarted the PC, but everything is the same and same error 432 appears when I execute my code.

again

Please tell what am I missing or why microsoft scripting runtime path hadn't changed after registering of it from folder SysWOW64?

 

[vbaResearch]

I can confirm for you that the syntax for GetObject is correct, and that it does return a reference to the specified workbook, provided that the path and filename are correct. If the path and/or filename are not correct, you'll get that runtime error 432.

Try running it again, but this time when you get the error, enter the following line in the Immediate Window (Ctrl+G), and press ENTER . . .

? ThisWorkbook.Path & "\readings\" & Year(Date) & " - Table.xlsx"

Does it return the correct path and filename? If so, check whether the file itself is spelled correctly and that it doesn't contain any extra spaces. Also, if the path and filename are correct, the following line in the Immediate Window should return the filename?

? Dir(ThisWorkbook.Path & "\readings\" & Year(Date) & " - Table.xlsx", vbNormal)

Does it return the filename?

I made the name simplier for this example

Here is what I get with first query

And with second

It is a correct path, but I still get the same error 432.

Private Sub CommandButton1_Click()
   Dim MyWB As Workbook
   Set MyWB = GetObject(ThisWorkbook.Path & "\readings\Book1.xlsx")
   MyWB.Close
   Set MyWB = Nothing
End Sub

 

[vbaResearch]

Just a moment ago I had an access to another computer and tried the same codes and it worked! but I do not understand why it does not work on my PC now, it is so crazy.

 

[Domenic]

The only other thing that I can suggest is to try repairing Microsoft Office. If that doesn't help, try uninstalling and re-installing Microsoft Office. You can do both by going to Control Panel >> All Control Panel Items >> Programs and Features.

 

[vbaResearch]

The only other thing that I can suggest is to try repairing Microsoft Office. If that doesn't help, try uninstalling and re-installing Microsoft Office. You can do both by going to Control Panel >> All Control Panel Items >> Programs and Features.

Thank you Domenic for reply, I tried repairing and it did not help, then I tried Uninstalling and reinstalling by Microsoft tool, which operated probably more than half an hour, restarted and went to Microsoft services page, and made an online installation, executed the Excel, and ... the same error occured, it had not gone. I do not know where to look else, reinstalling the Windows is too much work. How do you think, does this (click on the word "this") thread on stackoverflow look similar? because that is why I firstly thought about scrrun.dll.

 

[RoryA]

No. That thread is about using Getobject to create objects that are part of the scripting library. You are not doing that.

 

[Domenic]

I see that @RoryA has already answered your question, so that's great.

Unfortunately, I don't have any other suggestions. If all else fails, though, you could always try contacting Microsoft Support.

 

[ZVI]

Hi,
Try running Excel as administrator.
Then if GetObject works, set Excel.exe to be run always as administrator

 

[vbaResearch]

Hi,
Try running Excel as administrator.
Then if GetObject works, set Excel.exe to be run always as administrator

O M G, what? I mean I never ran it as administrator, it is the first time I ever had to try to run Excel as administrator ?
(and on other two PCs that I could gain access to - it was fine without running it as admin, and also I have only administrator account in Windows, ough, sorry, it is a topic for another theme)

But, I just tried running Excel as administrator and it worked! Thank You ZVI, ?, I could try to reinstall Windows otherwise finally
Thank You Rory and Domenic for helping, and I am very sorry for being such a scrrundller ?

So, totally running as admin solved this problem, Thank You

 

[ZVI]

Glad it has helped! ?