Display all information embedded into an object

[RodP]

Hi All,

I have an excel file which has a couple of objects. These objects actually contain data for use with an addin program (this addin program uses the information to generate data pulls basically). Does anyone know if it's possible to display 'all' the data behind the object in question?

I'm able to view some of the information using the design tool and properties box but there is additional data not shown here. I'm wanting to do this as some of the data needs to be replaced; a simple find/replace actually.

I'm trying to investigate this as I have about 1000 of these objects which all require the same change and the addin program does not provide this find/replace feature.

This is the informaiton I get from the properties box for one of the objects. The extra data I'm looking for is similar to an SQL query (or at least is uses the word 'where' at the beginning!)

(Name) NITRORange12
AltHTML
ApplyCalcOrder False
AutoArrangeDims False
AutoLoad False
CalcOrder 0
ColumnsInRange 28
Database SNACKSSUM
DimCount 4
DimDescription
DimGetString
DimIndex 0
DimName
DimsinColumns 2
DimsInHeadings 0
DimsInRows 2
DisplayStyle 0 - rngTableDefault
DisplayType 1 - ShortDescription
Drillable True
Enabled True
GetDatabaseFrom
GetFormatFrom
GetLayoutFrom
GetTitleFrom
Height 12
Left 0.75
Locked True
MaxSelections 0
MinSelection 0
NAasZero True
Placement 2
PostUpdateMacro
PreUpdateMacro
PrintObject False
Prompt False
PromptForBatabase False
PromptText
RangeDescription NITRO Range 13
RowsInRange 6
Shadow False
Top 166.5
UpdateIndex 16
UseDictionaryFormats False
Visible True
Width 12

Many thanks in advance

RodP

 

[PHV]

If the add-in is referenced in your VBA project you may find some info with the Object Browser (F2 key when in the VBE).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Are these objects in a sheet?

Trouble is, there is no Objects collection for the sheet. There are OLEObjects, Shapes, ChartObjects, Ranges, QueryTables, PivotTables...

In the Object Model it has...
[tt]
Multiple Objects
|
+-Worksheet
|
+-Multiple Objects
[/tt]
There does not seem to be a common collector.

What kind of object? OLE?

I tried an example using QueryTable object...

    Dim ob As Object, ws As Worksheet
    For Each ws In Worksheets
        For Each ob In ws.QueryTables
            With ob
                MsgBox .Name
                With .ResultRange
                    MsgBox .Address
                    MsgBox .Top
                End With
            End With
        Next
    Next

It demonstrates that in some cases, you have to drill deeper to get "data"

Not sure how you'ld use a QUERY to get at this data.

Skip,

_[red][/red]

 

[stevexff]

Will setting a watch on it help? Stop it in the debugger with a breakpoint, then expand the object in the watch window.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[SkipVought]

Watch Window with "...about 1000 of these objects..."

????

Skip,

_[red][/red]

 

[HughLerwill]

http://www.dailydoseofexcel.com/archives/2007/05/14/listing-an-objects-properties-and-methods/

may be of interest.

 

[RodP]

Hi Everyone,

All excellent replies! Many thanks.

Thanks HughLerwill, the program you pointed me to works fab and I can really drill down into the object's properties now. This program has confirmed each object in quesiton is an OLEObject. A quick look through and I've found some of the info I'm looking for. What I've confirmed now is that each object has something called a GETString. For example:

NITRORange1's getstring is:

OLEObject.Object.DimGetString =

[higlight]GET MKT 'TOTCOVGB' PROD WHERE (("TOTAL MARKET"="CSL - UB TOTAL BAGGED SNACKS") AND ("LEVEL"="TOTAL MARKET"))WHERE (("CSA - UB SNACK SUB CATEGORY"="CRISPS") AND ("LEVEL"="TOTAL MARKET!CSA - UB SNACK SUB CATEGORY")) FCT 'V' PER 'TWOYR52' 'PREV52' 'LAT52' TOTALSBELOW
[/highlight]

This refers to an external INFACT database SQL type engine (if any of you have come across it!) and will provide sales value data for 52week periods; 2-years, 1-year and this-year for all items in the database that are classed as 'crisps' (yum yum!)

My aim for the 1000 objects or so is to change (for exmaple) "CSA - UB SNACK SUB CATEGORY"="CRISPS" to "CSA - UB SNACK SUB CATEGORY"="SNACKS".

HOWEVER....

I know for a fact that this is not the actual code.string I need to alter. This GETString is created from another property or set of properties, for whenever you edit this line manually but then go in through the formal tool/engine (it's called NITRO) and click 'create report' it rewrites the GETString to what it was originally and you loose you're edit! Doh!

This is the level I need to get to and so using this property listing program, I'm hoping I'll be able to find some more info.

If any of you have had similar experiences and could perhaps guide me in specifc areas to look at in the drill down listings, it would be much appreciated. I'd be happy to send anyone an example of one of these OLEObjects but will await for replies and we could arrange that privately.

Thanks again everyone for your advice so far

RodP

 

[RodP]

Hi Everyone,

Is there anyone out there with any other suggestions?

Many thanks in advance

RodP

 

[PHV]

Have you tried the Object Browser ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RodP]

Hi PHV,

Yes I've used object browser and my previous (long) post was a result of using it.

Any ideas?

A little confused around the Watch facility too. I'm not too sure what I need to actually be watching.

Many thanks

RodP

 

[SkipVought]

Shows all the properties of an object and drilldown

Skip,

_[red][/red]