GetObject referencing worksheet question

[romanemul]

Hello forum guys.

I want to know what is the way of referencing worksheet within the GetObject function.
I know there are other ways of refering.

On the MSDN documentation there is example of referencing the class and object with the syntax like this.

[COLOR=#0101FD][FONT=Consolas]Set[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] LayerObject = GetObject([/FONT][/COLOR][COLOR=#A31515][FONT=Consolas]"C:\CAD\SCHEMA.CAD[B]!Layer3[/B]"[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR]

so i supposed it to use it like this

Dim otherwb As Object


Set otherwb = New Excel.Application
Set otherwb = GetObject("C:\myfile.xlsm[B]!Sheet1[/B]")

Workbook opens up but there is an error message saying automation error. Invalid syntax.

Can anyone help me with proper syntax to reference worksheet,objects etc ?

I cant find proper syntax for it.

Thanks in advance

 

[Domenic]

Try...

    Dim ws As Object    

    Set ws = GetObject("C:\Users\Domenic\Documents\sample.xlsm", "Excel.Sheet").Worksheets("Sheet1")

Hope this helps!

 

[romanemul]

Yep that works.

But how did you find about "Excel.Sheet" class parameter ? I tried "Excel.Worksheet" and few other class parameter variations and that doesn't worked.

 

[Domenic]

It was mentioned in a book by John Walkenbach called Microsoft Office Excel 2007 Power Programming with VBA. I believe the book has been updated, though. I haven't come across any other reference.

 

[romanemul]

Just answering to make this thread complete. All avaliable class names are located in a windows registry under a path

HKEY_CLASSES_ROOT

As you navigate through the long list of classes you can find many names used as a "class" parameter in a CreateObject and GetObject methods.

Some of them doesnt seems to work untill you dont add a reference to it.

 

[Domenic]

Thanks for the information!

Cheers!