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?

 

[RoryA]

Why is there a dot before the \readings part of the path? Are you trying to navigate back up one folder?

BTW, the Scripting library has nothing to do with this.

 

[vbaResearch]

Why is there a dot before the \readings part of the path? Are you trying to navigate back up one folder?

BTW, the Scripting library has nothing to do with this.

to be true I made a lot of tries and I do not remember why I have decided on using the dot before readings, it works with "Workbooks.Open" as I want it to work. But when I use "GetObject", then none of paths work, even if I make a full path. That is why I came to decision that it is not the reason why GetObject does not work in my case. Further I found that in other similar topics people register the scrrun.dll file for references using the command prompt commands
cd \Windows\SysWOW64
regsvr32 scrrun.dll
and they said they were successful but it did not work for me

Yes, in my folder there are:
1) a main program file
2) and folder "readings"
(I mean program file and folder are in same folder). The goal at all is to open a file that is located in the readings folder, read data from it and change the color of textbox on a UserForm1 depending on the value.
it works without errors when using "Workbooks.Open" but with GetObject not.

And what do you mean it has nothing to do with this? User "johnywhy" here says it works with his codes, so there should be a way to make it work.

 

[RoryA]

I mean that the scripting library has nothing to do with GetObject and is therefore not relevant to this issue.

If the readings folder is in the same folder as the workbook with the code in it, then you need:

Set MyWB = GetObject(ThisWorkbook.Path & "\readings\" & Year(Date) & " - Table.xlsx")

 

[vbaResearch]

I mean that the scripting library has nothing to do with GetObject and is therefore not relevant to this issue.

If the readings folder is in the same folder as the workbook with the code in it, then you need:

Set MyWB = GetObject(ThisWorkbook.Path & "\readings\" & Year(Date) & " - Table.xlsx")

Ok, thank you. You are right it works fine with "Workbooks.Open" and without dot.
but GetObject unfortunately still is angry at me with its error 432

 

[RoryA]

That sounds like a problem with your file associations perhaps. Can you double-click xlsx files in explorer to open them without any problems?

 

[vbaResearch]

That sounds like a problem with your file associations perhaps. Can you double-click xlsx files in explorer to open them without any problems?

Yes everything opens as should without any inconvenience, green excel icon, and opens in excel, in other forums people say it worked in older versions of excel and then they had to register scrrun.dll file and it worked after. but in my case it still does not I dont know why

 

[RoryA]

As I said earlier, scrrun has nothing whatsoever to do with that code.
The only other reason for getting that error that I know of is if the path is wrong but I assume it’s exactly the same path as you were using with workbooks.open successfully. What kind of path is it? Network drive, or local?

 

[vbaResearch]

As I said earlier, scrrun has nothing whatsoever to do with that code.
The only other reason for getting that error that I know of is if the path is wrong but I assume it’s exactly the same path as you were using with workbooks.open successfully. What kind of path is it? Network drive, or local?

It is a small project on local drive which is in Main folder I have tried full paths and relative paths the reason seams to be in my GetObject() function, like if something is broken in its references or could you please try opening any workbook the same way on your system

 

[Domenic]

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?