Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he 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

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" wrote in message
...
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he 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

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"
<v12842.655189999999</v
</c

So there is an extra vm parameter for these cells.

Kind regards
IgorM

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he 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

I misunderstood, I thought you were looking for metadata in Excel 2003.

I tried to create a file with metadata in a cell as per your sample. After
editing the xml in Notepad and recreating the xlsx it wouldn't open in 2007,
at least not until after allowing Excel to repair it, then the edited
changes were removed.

Maybe the xml needs a bit more. Could you post the entire xml of a sheet
with just one cell (say F16), eg like this but with the metadata

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?
- <worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
<dimension ref="F16" /
- <sheetViews
- <sheetView tabSelected="1" workbookViewId="0"
<selection activeCell="F16" sqref="F16" /
</sheetView
</sheetViews
<sheetFormatPr defaultRowHeight="15" /
- <sheetData
- <row r="16" spans="6:6"
- <c r="F16"
<v12842.655189999899</v
</c
</row
</sheetData
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3"
footer="0.3" /
</worksheet

(this is pasted after opening in IE, don't worry about the extraneous
dashes, I'll remove those)
First I changed
<c r="F16"
to
<c r="F16" s="190" vm="20"
then to
<c r="F16" vm="20"

both got 'repaired'

The other question of course is how did that metadata get in there in the
first place!

Regards,
Peter T


"IgorM" wrote in message
...
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"
<v12842.655189999999</v
</c

So there is an extra vm parameter for these cells.

Kind regards
IgorM

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he 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

Just to make it clear. All I want to do is to check in Excel 2007 if a
certain cells contains metadata - in xlsx file.
I do not normally have access to older version of Excel and it is only in
versions prior 2007 that some cells show '_xln.compoundvalue' in formula
instead of value.

Cells that show this function are (headings of the table below
aWorksheetName, WorksheetCodeName, Cell.Address, Cell.Formula,
Cell.Value):
Irrigation Arkusz40 $S$53 =_xlfn.COMPOUNDVALUE(94) 0,57
Irrigation Arkusz40 $S$61 =_xlfn.COMPOUNDVALUE(95) 19,6873111111111
Irrigation Arkusz40 $S$62 =_xlfn.COMPOUNDVALUE(96) 61,8690111111111
Irrigation Arkusz40 $S$70 =_xlfn.COMPOUNDVALUE(97) 30,9298888888889
Irrigation Arkusz40 $S$72 =_xlfn.COMPOUNDVALUE(98) 0,2944
Irrigation Arkusz40 $S$82 =_xlfn.COMPOUNDVALUE(99) 14,9157
Irrigation Arkusz40 $S$84 =_xlfn.COMPOUNDVALUE(99) 2,833983
Irrigation Arkusz40 $S$107 =_xlfn.COMPOUNDVALUE(100) 164,52998
Irrigation Arkusz40 $S$108 =_xlfn.COMPOUNDVALUE(100) 164,52998

I will split the XML into two posts.

U¿ytkownik "Peter T" <peter_t@discussions napisa³ w wiadomo¶ci grup
...
I misunderstood, I thought you were looking for metadata in Excel 2003.

I tried to create a file with metadata in a cell as per your sample. After
editing the xml in Notepad and recreating the xlsx it wouldn't open in
2007, at least not until after allowing Excel to repair it, then the
edited changes were removed.

Maybe the xml needs a bit more. Could you post the entire xml of a sheet
with just one cell (say F16), eg like this but with the metadata

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?
- <worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
<dimension ref="F16" /
- <sheetViews
- <sheetView tabSelected="1" workbookViewId="0"
<selection activeCell="F16" sqref="F16" /
</sheetView
</sheetViews
<sheetFormatPr defaultRowHeight="15" /
- <sheetData
- <row r="16" spans="6:6"
- <c r="F16"
<v12842.655189999899</v
</c
</row
</sheetData
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3"
footer="0.3" /
</worksheet

(this is pasted after opening in IE, don't worry about the extraneous
dashes, I'll remove those)
First I changed
<c r="F16"
to
<c r="F16" s="190" vm="20"
then to
<c r="F16" vm="20"

both got 'repaired'

The other question of course is how did that metadata get in there in the
first place!

Regards,
Peter T


"IgorM" wrote in message
...
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"
<v12842.655189999999</v
</c

So there is an extra vm parameter for these cells.

Kind regards
IgorM

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he 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

Even if you do not have access to 2003 you can still open a 2003 xls file in
2007.

I was expecting you to post the xml for a sheet with only a single cell
containing your metadata. In other words with so I could reproduce it. I did
manage to create a sheet with your posted large xml. Had some problems
though, first Excel complained that | is an illegal character, so I replaced
all those with #..

Finally a large sheet opened, mainly with links to other workbooks, sheets
and named ranges. So mainly I got a sheet full of #REF! errors.

I converted the file to Excel 2003 format, but I couldn't find any of the
metadata examples you describe below. BUT - the cell information described
below does not relate to the large xml you posted. Eg, cell S53 in the xml
has the value 159.1, not 0.57 as you say below.

If you want to post a single cell example of metadata I will look into ways
of identifying it.

Regards,
Peter T


"IgorM" wrote in message
...
Just to make it clear. All I want to do is to check in Excel 2007 if a
certain cells contains metadata - in xlsx file.
I do not normally have access to older version of Excel and it is only in
versions prior 2007 that some cells show '_xln.compoundvalue' in formula
instead of value.

Cells that show this function are (headings of the table below
aWorksheetName, WorksheetCodeName, Cell.Address, Cell.Formula,
Cell.Value):
Irrigation Arkusz40 $S$53 =_xlfn.COMPOUNDVALUE(94) 0,57
Irrigation Arkusz40 $S$61 =_xlfn.COMPOUNDVALUE(95) 19,6873111111111
Irrigation Arkusz40 $S$62 =_xlfn.COMPOUNDVALUE(96) 61,53111111111
Irrigation Arkusz40 $S$70 =_xlfn.COMPOUNDVALUE(97) 30,9298888888889
Irrigation Arkusz40 $S$72 =_xlfn.COMPOUNDVALUE(98) 0,2944
Irrigation Arkusz40 $S$82 =_xlfn.COMPOUNDVALUE(99) 14,9157
Irrigation Arkusz40 $S$84 =_xlfn.COMPOUNDVALUE(99) 2,833983
Irrigation Arkusz40 $S$107 =_xlfn.COMPOUNDVALUE(100) 164,52998
Irrigation Arkusz40 $S$108 =_xlfn.COMPOUNDVALUE(100) 164,52998

I will split the XML into two posts.

U¿ytkownik "Peter T" <peter_t@discussions napisa³ w wiadomo¶ci grup
...
I misunderstood, I thought you were looking for metadata in Excel 2003.

I tried to create a file with metadata in a cell as per your sample.
After editing the xml in Notepad and recreating the xlsx it wouldn't open
in 2007, at least not until after allowing Excel to repair it, then the
edited changes were removed.

Maybe the xml needs a bit more. Could you post the entire xml of a sheet
with just one cell (say F16), eg like this but with the metadata

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?
- <worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
<dimension ref="F16" /
- <sheetViews
- <sheetView tabSelected="1" workbookViewId="0"
<selection activeCell="F16" sqref="F16" /
</sheetView
</sheetViews
<sheetFormatPr defaultRowHeight="15" /
- <sheetData
- <row r="16" spans="6:6"
- <c r="F16"
<v12842.655189999899</v
</c
</row
</sheetData
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75"
header="0.3" footer="0.3" /
</worksheet

(this is pasted after opening in IE, don't worry about the extraneous
dashes, I'll remove those)
First I changed
<c r="F16"
to
<c r="F16" s="190" vm="20"
then to
<c r="F16" vm="20"

both got 'repaired'

The other question of course is how did that metadata get in there in the
first place!

Regards,
Peter T


"IgorM" wrote in message
...
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"
<v12842.655189999999</v
</c

So there is an extra vm parameter for these cells.

Kind regards
IgorM

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
Hi

How can I check (using VBA) if a cell has some metadata associated (as
described he
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

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/ex...778231033.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/sh...d.php?t=181927

Microsoft Office Help

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" napisa³ w wiadomo¶ci grup
...

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/ex...778231033.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/sh...d.php?t=181927

Microsoft Office Help