Value Metadata
in [Programming]
|
From: Peter T on 24 Feb 2010 04:53 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"> <v>12842.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" <igorm(a)live.com> wrote in message news:%23kZcAeNtKHA.1608(a)TK2MSFTNGP05.phx.gbl... > 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: IgorM on 25 Feb 2010 05:00 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 are:WorksheetName, 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(a)discussions> napisa� w wiadomo�ci grup dyskusyjnych:#QB8mcTtKHA.4568(a)TK2MSFTNGP05.phx.gbl... > 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"> > <v>12842.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" <igorm(a)live.com> wrote in message > news:%23kZcAeNtKHA.1608(a)TK2MSFTNGP05.phx.gbl... >> 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: Peter T on 25 Feb 2010 06:34 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" <igorm(a)live.com> wrote in message news:32A7394D-CF05-415E-A7A0-2DA435CA5321(a)microsoft.com... > 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 > are:WorksheetName, 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(a)discussions> napisa� w wiadomo�ci grup > dyskusyjnych:#QB8mcTtKHA.4568(a)TK2MSFTNGP05.phx.gbl... >> 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"> >> <v>12842.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" <igorm(a)live.com> wrote in message >> news:%23kZcAeNtKHA.1608(a)TK2MSFTNGP05.phx.gbl... >>> 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: IgorM on 25 Feb 2010 09:44 You are right about the xml file. I had to mistakenly take incorrect sheet. You are also right about opening the xls file in Excel 2007, but if you do so it seems that Excel 2007 treats the cell differently and the formula box has no formula in it - the cell seems to be value only. If we open the same file in Excel 2003 for instance, the cell has the same value but in formula it displays the compound formula. Below is an XML structure for one sheet with this kind of cell (A1): <?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"> - <sheetPr> <pageSetUpPr fitToPage="1" /> </sheetPr> <dimension ref="A1" /> - <sheetViews> <sheetView showGridLines="0" tabSelected="1" zoomScale="70" zoomScaleNormal="70" workbookViewId="0" /> </sheetViews> <sheetFormatPr defaultRowHeight="12.75" /> - <cols> <col min="1" max="1" width="17.28515625" customWidth="1" /> </cols> - <sheetData> - <row r="1" spans="1:1" ht="17.100000000000001" customHeight="1"> - <c r="A1" vm="1"> <v>0.56999999999999995</v> </c> </row> </sheetData> <printOptions horizontalCentered="1" /> <pageMargins left="0.19685039370078741" right="0" top="0.19685039370078741" bottom="0.19685039370078741" header="0.31496062992125984" footer="0" /> <pageSetup paperSize="9" fitToHeight="2" orientation="landscape" horizontalDpi="4294967294" r:id="rId1" /> - <headerFooter alignWithMargins="0"> <oddFooter>&L&"Arial CE,Kursywa"&D&C&"Arial CE,Kursywa"&F</oddFooter> </headerFooter> </worksheet> What I also noticed the xl folder for the workbook contains a metadata.xml file of this content: <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> - <metadata xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> - <metadataTypes count="1"> <metadataType name="XLMDX" minSupportedVersion="120000" copy="1" pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1" clearFormats="1" clearComments="1" assign="1" coerce="1" /> </metadataTypes> - <metadataStrings count="5"> <s v="rachunek_zyskow_i_strat" /> <s v="{[Rok finansowy].[Wszystkie].[2009].[ 1],[Rok finansowy].[Wszystkie].[2009].[ 2],[Rok finansowy].[Wszystkie].[2009].[ 3],[Rok finansowy].[Wszystkie].[2009].[ 4],[Rok finansowy].[Wszystkie].[2009].[ 5],[Rok finansowy].[Wszystkie].[2009].[ 6],[Rok finansowy].[Wszystkie].[2009].[ 7],[Rok finansowy].[Wszystkie].[2009].[ 8],[Rok finansowy].[Wszystkie].[2009].[ 9]}" /> <s v="[Measures].[Suma Kwota PLN]" /> <s v="[Rodzaj kosztu].[Wszystkie].[300].[US�UGI / MATERIA�Y WEWN�TRZNE].[304]" /> <s v="[Jednostka odpowiedzal].[Wszystkie].[Produkcja warzyw].[Irrigation]" /> </metadataStrings> - <mdxMetadata count="1"> - <mdx n="0" f="v"> - <t c="4"> <n x="1" s="1" /> <n x="2" /> <n x="3" /> <n x="4" /> </t> </mdx> </mdxMetadata> - <valueMetadata count="1"> - <bk> <rc t="1" v="0" /> </bk> </valueMetadata> </metadata> Hope it helps. U�ytkownik "Peter T" <peter_t(a)discussions> napisa� w wiadomo�ci grup dyskusyjnych:enLJE6gtKHA.3656(a)TK2MSFTNGP06.phx.gbl... > 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" <igorm(a)live.com> wrote in message > news:32A7394D-CF05-415E-A7A0-2DA435CA5321(a)microsoft.com... >> 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 >> are:WorksheetName, 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(a)discussions> napisa� w wiadomo�ci grup >> dyskusyjnych:#QB8mcTtKHA.4568(a)TK2MSFTNGP05.phx.gbl... >>> 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"> >>> <v>12842.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" <igorm(a)live.com> wrote in message >>> news:%23kZcAeNtKHA.1608(a)TK2MSFTNGP05.phx.gbl... >>>> 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: Peter T on 25 Feb 2010 11:57 I added the two xml files but when opened in Excel it was all rejected. If you want to send a file off-line it'd be easier, but please the smallest file possible and with no links. I suppose if you only need to know if the file contains metadata, unzip the xlsx and look for "metadata.xml" in the xl folder. Depending on the tools available to you that might be quite simple. Regards, Peter T PS, my address is in the Reply-to which you'll see in your Live Mail, add the obvious dot and com "IgorM" <igorm(a)live.com> wrote in message news:83753DDD-EF13-428B-95C5-897494B9E6A9(a)microsoft.com... > You are right about the xml file. I had to mistakenly take incorrect > sheet. > You are also right about opening the xls file in Excel 2007, but if you do > so it seems that Excel 2007 treats the cell differently and the formula > box has no formula in it - the cell seems to be value only. If we open the > same file in Excel 2003 for instance, the cell has the same value but in > formula it displays the compound formula. > > Below is an XML structure for one sheet with this kind of cell (A1): > > <?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"> > - <sheetPr> > <pageSetUpPr fitToPage="1" /> > </sheetPr> > <dimension ref="A1" /> > - <sheetViews> > <sheetView showGridLines="0" tabSelected="1" zoomScale="70" > zoomScaleNormal="70" workbookViewId="0" /> > </sheetViews> > <sheetFormatPr defaultRowHeight="12.75" /> > - <cols> > <col min="1" max="1" width="17.28515625" customWidth="1" /> > </cols> > - <sheetData> > - <row r="1" spans="1:1" ht="17.100000000000001" customHeight="1"> > - <c r="A1" vm="1"> > <v>0.56999999999999995</v> > </c> > </row> > </sheetData> > <printOptions horizontalCentered="1" /> > <pageMargins left="0.19685039370078741" right="0" > top="0.19685039370078741" bottom="0.19685039370078741" > header="0.31496062992125984" footer="0" /> > <pageSetup paperSize="9" fitToHeight="2" orientation="landscape" > horizontalDpi="4294967294" r:id="rId1" /> > - <headerFooter alignWithMargins="0"> > <oddFooter>&L&"Arial CE,Kursywa"&D&C&"Arial CE,Kursywa"&F</oddFooter> > </headerFooter> > </worksheet> > > What I also noticed the xl folder for the workbook contains a metadata.xml > file of this content: > > <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> > - <metadata > xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> > - <metadataTypes count="1"> > <metadataType name="XLMDX" minSupportedVersion="120000" copy="1" > pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1" > clearFormats="1" clearComments="1" assign="1" coerce="1" /> > </metadataTypes> > - <metadataStrings count="5"> > <s v="rachunek_zyskow_i_strat" /> > <s v="{[Rok finansowy].[Wszystkie].[2009].[ 1],[Rok > finansowy].[Wszystkie].[2009].[ 2],[Rok finansowy].[Wszystkie].[2009].[ > 3],[Rok finansowy].[Wszystkie].[2009].[ 4],[Rok > finansowy].[Wszystkie].[2009].[ 5],[Rok finansowy].[Wszystkie].[2009].[ > 6],[Rok finansowy].[Wszystkie].[2009].[ 7],[Rok > finansowy].[Wszystkie].[2009].[ 8],[Rok finansowy].[Wszystkie].[2009].[ > 9]}" /> > <s v="[Measures].[Suma Kwota PLN]" /> > <s v="[Rodzaj kosztu].[Wszystkie].[300].[US�UGI / MATERIA�Y > WEWN�TRZNE].[304]" /> > <s v="[Jednostka odpowiedzal].[Wszystkie].[Produkcja > warzyw].[Irrigation]" /> > </metadataStrings> > - <mdxMetadata count="1"> > - <mdx n="0" f="v"> > - <t c="4"> > <n x="1" s="1" /> > <n x="2" /> > <n x="3" /> > <n x="4" /> > </t> > </mdx> > </mdxMetadata> > - <valueMetadata count="1"> > - <bk> > <rc t="1" v="0" /> > </bk> > </valueMetadata> > </metadata> > > Hope it helps. > > > U�ytkownik "Peter T" <peter_t(a)discussions> napisa� w wiadomo�ci grup > dyskusyjnych:enLJE6gtKHA.3656(a)TK2MSFTNGP06.phx.gbl... >> 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" <igorm(a)live.com> wrote in message >> news:32A7394D-CF05-415E-A7A0-2DA435CA5321(a)microsoft.com... >>> 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 >>> are:WorksheetName, 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(a)discussions> napisa� w wiadomo�ci grup >>> dyskusyjnych:#QB8mcTtKHA.4568(a)TK2MSFTNGP05.phx.gbl... >>>> 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"> >>>> <v>12842.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" <igorm(a)live.com> wrote in message >>>> news:%23kZcAeNtKHA.1608(a)TK2MSFTNGP05.phx.gbl... >>>>> 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 >>>>>> >>>>>> >>>> >>>> >> >>
First
|
Prev
|
Pages: 1 2 Prev: Excel 2003 VBA program kills itself in Japan Next: macro to update last character |