Prev: Excel 2003 VBA program kills itself in Japan
Next: macro to update last character
From: IgorM on 23 Feb 2010 15:02
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described here: http://msdn.microsoft.com/en-us/library/dd953161.aspx).
I ask because I have an xlsx file with data that when opened in Excel 2007
shows values only. But when I save the same file in xls format and open it
in Excel 2003 it displays value in the cell but in formula box Excel shows
_xlfn.COMPOUNDVALUE(20) for instance.

Thanks
IgorM

From: Peter T on 23 Feb 2010 16:34
Curious!

"2.2.4 Metadata
Metadata is additional data associated with a particular cell or its
content. Metadata is recorded in BIFF8 for future extensibility purpose
only."

I haven't come across this but I assume the way to check in VBA is by
examining the formula you can see in the input bar. I can't test but try
this -

Function HasMetadata(rCell As Range) As Boolean
With rCell
If .HasFormula Then
' case sensitive
HasMetadata = Left$(.Formula, 20) = "=_xlfn.COMPOUNDVALUE"
End If
End With
End Function

I'd be interested to see the relevant 2007 XML

Regards,
Peter T


"IgorM" <igorm(a)live.com> wrote in message
news:3ABE45A1-75C9-4AFD-B125-52CEAFCD9434(a)microsoft.com...
> Hi
>
> How can I check (using VBA) if a cell has some metadata associated (as
> described here: http://msdn.microsoft.com/en-us/library/dd953161.aspx).
> I ask because I have an xlsx file with data that when opened in Excel 2007
> shows values only. But when I save the same file in xls format and open it
> in Excel 2003 it displays value in the cell but in formula box Excel shows
> _xlfn.COMPOUNDVALUE(20) for instance.
>
> Thanks
> IgorM


From: IgorM on 23 Feb 2010 17:28
The issue with the given code is that in Excel 2007 (where I want to examine
the cell for metadata) there is no cell formula - just value.
The formula is only visible under Excel prior to 2007. I thought there is
some extra cell parameter that can be checked if the cell has some metadata.

The xml for the file for cells with the metadata is:

</c>
- <c r="F16" s="190" vm="20">
<v>12842.655189999999</v>
</c>

So there is an extra vm parameter for these cells.

Kind regards
IgorM

"Peter T" <peter_t(a)discussions> wrote in message
news:O0tF8$MtKHA.5612(a)TK2MSFTNGP05.phx.gbl...
> Curious!
>
> "2.2.4 Metadata
> Metadata is additional data associated with a particular cell or its
> content. Metadata is recorded in BIFF8 for future extensibility purpose
> only."
>
> I haven't come across this but I assume the way to check in VBA is by
> examining the formula you can see in the input bar. I can't test but try
> this -
>
> Function HasMetadata(rCell As Range) As Boolean
> With rCell
> If .HasFormula Then
> ' case sensitive
> HasMetadata = Left$(.Formula, 20) = "=_xlfn.COMPOUNDVALUE"
> End If
> End With
> End Function
>
> I'd be interested to see the relevant 2007 XML
>
> Regards,
> Peter T
>
>
> "IgorM" <igorm(a)live.com> wrote in message
> news:3ABE45A1-75C9-4AFD-B125-52CEAFCD9434(a)microsoft.com...
>> Hi
>>
>> How can I check (using VBA) if a cell has some metadata associated (as
>> described here: http://msdn.microsoft.com/en-us/library/dd953161.aspx).
>> I ask because I have an xlsx file with data that when opened in Excel
>> 2007 shows values only. But when I save the same file in xls format and
>> open it in Excel 2003 it displays value in the cell but in formula box
>> Excel shows _xlfn.COMPOUNDVALUE(20) for instance.
>>
>> Thanks
>> IgorM
>
>
From: joel on 23 Feb 2010 17:44

I don't know if you can! It appears that 2007 is using features that
2003 doesn't recognize. 2007 should remove these features when saving
as a 2003 workbook but isn't removing these features. some metadata
2003 will recognize and some it won't. I would make sure I have the
latest updtes to 2007 since this may be a bug that has been fixed.

I just checked on teh web and found this site

http://office.microsoft.com/en-us/excel/HA100778231033.aspx

It says there is a compatibility checker built into 2007.

the is a VBA instruction to automatically turn on the compatibility
checker. Maybe you have it turned off

BookVar.CheckCompatibility = False


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181927

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: IgorM on 24 Feb 2010 01:48
Thanks for response.
I'm aware of the compatibility checker. The problem is that these cell have
none of the properties that could cause problem in Excel versions prior 2007
according to the compatibility checker.
These values were copied and pastes as values only from a different
workbook. Some of the source cells had cube values but some other were plain
sums of cells (including cells that use cube formulas). Despite copying and
pasting as values only majority of the cells were copied just fine but some
(still can't figure out any differences between those copied correctly and
incorrectly) were copied, as what seems in Excel 2007, values only but there
must be some metadata in these cells that is not recognizable in Excel
versions prior 2007. Compatibility checker doesn't see any issues in these
cells.


U�ytkownik "joel" <joel.46uznn(a)thecodecage.com> napisa� w wiadomo�ci grup
dyskusyjnych:joel.46uznn(a)thecodecage.com...
>
> I don't know if you can! It appears that 2007 is using features that
> 2003 doesn't recognize. 2007 should remove these features when saving
> as a 2003 workbook but isn't removing these features. some metadata
> 2003 will recognize and some it won't. I would make sure I have the
> latest updtes to 2007 since this may be a bug that has been fixed.
>
> I just checked on teh web and found this site
>
> http://office.microsoft.com/en-us/excel/HA100778231033.aspx
>
> It says there is a compatibility checker built into 2007.
>
> the is a VBA instruction to automatically turn on the compatibility
> checker. Maybe you have it turned off
>
> BookVar.CheckCompatibility = False
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:
> http://www.thecodecage.com/forumz/showthread.php?t=181927
>
> [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]
>
 |  Next  |  Last
Pages: 1 2
Prev: Excel 2003 VBA program kills itself in Japan
Next: macro to update last character