Formatting Export to Excel

[Deactivated User]

I tried to search for the previous posts on formatting Export to Excel / Template for Export to Excel but did not find the solution only several ideas. I think there was a post which suggested a template and another which suggested a macro (must have been  Dave_3.0 )

Can anybody help me to get a feasible work around.

What I am looking is for a way to remove the "value cloumn" from the export when an image is displayed and there is display mapping. Eg. for status reports. Hiding on shrinking it to zero width would do that as well.

I'd like to remove the Value columns (eg Minor Variance and On Track etc.)

[David Morton]

"Writing" excel macros is easy.

 

Press record macro in Excel ; highlight column I, select delete column, highlight column L (because column M will have shifted to L), delete column. Stop recording macro.

 

Thats it ; now you have a macro.

[Andrew M]

Not aware of a "supported" way to stop this and keep the icon in the portlet, can as Dave suggests delete the value columns after the export.

Would be nice if this just exported an icon/colour as you see in the portlet

Loads of ideas regarding export to excel, definitely due a re-think.

 

If you just need to export this data to excel, then i'd advise to go down the report route as opposed to a portlet.

You'd have much better control over formatting

[David Morton]

One suspects someone could write a "generic" tidy-up-Clarity-output macro that you could run against *any* Export-to-Excel output - the macro would have to be clever enough to interrogate column & row settings and then perform whatever tidy-up logic was needed to that type of column...

 

Be nice if anyone has built such a thing to upload it here wouldn't it?

 

(I don't have such a thing, have a few bespoke case-by-case ones only)

[Andrew M]

or we could "fix" the xml being generated

[David Morton]

^ thats the template option isn't it ; could such a thing be inserted between the application and the user's Excel session auto-magically?

 

I was imagining the "magic VBA macro" solution being something you installed into Excel locally that then either automatically ran when a Clarity-XML spreadsheet file was opened or the user could manually run via a toolbar-click.

 

(pipe-dream really, I have no intention of building anything like that (unless heavily incentivised ))

[Deactivated User]

Well I guess that would something like the XLT transformation fpena  does in XOG query bridge when stripping obsolete parts of the xml. But again that would require XOG bridge and the user to do the transformation, unless Federico has again new tricks in his sleeve. After all there is the save step where the user takes action and directs the output to be saved.

 

The thing is that the end users are expecting a simple push button or set once solution and no manual action every time.

A general macro would work only when it can recognized the obsolete columns every time without failure. That is not going to happen if it is based column number.

[L.Elias]

Are there any scenarios where the columns are something other than Value/Color? Seems like the macro/vba would basically just have to search Row 2 for instances of "Value", then for each check if the next cell over is "Color" and if so, delete whichever you want

 

Personally I wouldn't mind if the default output just did both in the same column (display the value text AND set the cell color)

[Deactivated User]

Thank you guys for your input.

Writing the macro is not a problem and it can do it. However, that will require all users to do that or have the macro distributed in some manner. The ideal solution would not require any user action.

 

The way I see it though the instances are always Value and Color the top cell above them is merged. So deleting the column only does that while the file is open. When it is saved and reopened both are gone. To avoid that the top cells have to be unmerged first.

The second challenge is that when I select a column it it selected by the ID even if I use search to get there. So the macro only works for predefined locations of the columns. That is not problem if the export is always done from the same portlet.

 

  ActiveWindow.ScrollColumn = 8     Range("K1:T1").Select     Selection.UnMerge     Range("K1:T2").Select     Selection.Cut Destination:=Range("L1:U2")     Columns("K:K").Select     Selection.EntireColumn.Hidden = True     Columns("M:M").Select     Selection.EntireColumn.Hidden = True

 

"fixing" the xml might be possible after some thinking

 

       <Cell ss:StyleID="headerCenterWrap" ss:MergeAcross="1">
          <Data ss:Type="String">Budget RAG</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader"/>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Value</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Color</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Value</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Color</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Value</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Color</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Value</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Color</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Value</Data>
        </Cell>
        <Cell ss:StyleID="subHeader">
          <Data ss:Type="String">Color</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="string">
          <Data ss:Type="String">PRJ00455</Data>
        </Cell>
        <Cell ss:StyleID="url"