New Ss Tag For Posting Spreadsheet Data

  • Here's a recent post from Oz:
    --------------------


    Below is copy of spreadsheet. I want to know how many shirts were ordered that fit the following criteria; column B = "MWF AM", column C = "Butterfly", column D = "Women", and column E = "S".


    LNAME CLASS PIC TYPE Size QTY Unit Price Total
    Doe MWF AM Butterfly Women S 2 11.00 22.00
    Poe MWF PM Butterfly Women M 3 11.00 33.00
    Soe TU/TH Butterfly Men's XXL 5 11.00 55.00
    Kato MWF AM Tiger Kid's L 8 11.00 88.00
    Jimbo MWF AM Butterfly Women S 2 12.00 24.00


    -------------------


    As usual, the spaces get compressed and makes the tabular formatting disappear.


    Oz has a new tag that allows you to post data to look like this:
    [ss=
    LNAME CLASS PIC TYPE Size QTY Unit Price Total
    Doe MWF AM Butterfly Women S 2 11.00 22.00
    Poe MWF PM Butterfly Women M 3 11.00 33.00
    Soe TU/TH Butterfly Men's XXL 5 11.00 55.00
    Kato MWF AM Tiger Kid's L 8 11.00 88.00
    Jimbo MWF AM Butterfly Women S 2 12.00 24.00]*[/ss]
    Better, eh?


    Here's how it was entered:


    [noparse][ss=
    LNAME CLASS PIC TYPE Size QTY Unit Price Total
    Doe MWF AM Butterfly Women S 2 11.00 22.00
    Poe MWF PM Butterfly Women M 3 11.00 33.00
    Soe TU/TH Butterfly Men's XXL 5 11.00 55.00
    Kato MWF AM Tiger Kid's L 8 11.00 88.00
    Jimbo MWF AM Butterfly Women S 2 12.00 24.00]*[/ss][/noparse]


    The ss tag preserves internal spaces, so the column alignments are maintained. Note that the text is inside the first tag, after the = sign, and followed by the closing brace, asterisk, and closing tag.


    When you paste from Excel into the message window, tabs are inserted where the column breaks were, so you're somewhat at the mercy of HTML for whether the resulting tabulation exactly fits your columns. Even so, it's a welcome addition when uploading a spreadsheet is overkill.


    For a faster alternative, use the routine below. Select the data you want to paste from Excel, run the sub (which formats the data, adds the tag, and copies the result to the clipbard), and just paste (Cntl-V) into the Oz message window. It maintains column alignment, and shows row and column headers, like this:


    [ss=
    ---A---- ---B---- ----C---- ----D---- ----E---- -F-- ----G----- --H---
    1 LNAME CLASS PIC TYPE Size QTY Unit Price Total
    2 Doe MWF AM Butterfly Women S 2 11.00 22.00
    3 Poe MWF PM Butterfly Women M 3 11.00 33.00
    4 Soe TU/TH Butterfly Men's XXL 5 11.00 55.00
    5 Kato MWF AM Tiger Kid's L 8 11.00 88.00
    6 Jimbo MWF AM Butterfly Women S 2 12.00 24.00
    ]*[/ss]


    Comments and suggestions for improvement are invited and welcome.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: New Ss Tag For Posting Spreadsheet Data


    shg,


    Your code works cool for me as is.


    Thanks for your hard work


    Bill[hr]*[/hr] Auto Merged Post;[dl]*[/dl]shg,


    Since your code outputs to the clip board the output can be posted to any other MS application such as MSWord or PowerPoint. How about an option to include or exclude the tags so you can copy an Excel table to MSWord without the tags. This would be helpfull in documenting custom applications.


    Bill

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: New Ss Tag For Posting Spreadsheet Data


    Reafidy, the quickest way to include references for Dataobject is to add a userform.


    Nice work shg.
    I assume the line space is a feature of the ss tag.

  • Re: New Ss Tag For Posting Spreadsheet Data


    Quote from Andy

    Reafidy, the quickest way to include references for Dataobject is to add a userform.


    Thank you, Andy, I meant to point that out, particularly since I couldn't find the Microsoft Forms 2.0 among the available references. I searched Oz to find out exactly what you told Reafidy. And after you add the userform, you can delete it and the reference will persist.

    Quote from Andy

    I assume the line space is a feature of the ss tag.


    Reckon so. That's a question for Dave.

    Quote from Bill R

    How about an option to include or exclude the tags so you can copy an Excel table to MSWord without the tags.


    Good suggestion, Bill, see update below.



    I found it interesting that to copy into Notepad with line feeds, you need not vbCr, not vbLf, but vbCrLf. Should that be obvious?


    Also, here's a funny: I had to use noparse tags when posting the code to stop the ss tags from formatting ...


    Here's the update. It includes options to do values or formulas, with or without tags.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: New Ss Tag For Posting Spreadsheet Data


    Great job!


    How does it look;


    [ss=
    -A- -----------B------------ -C-
    1 Jan =COUNTIF($C$1:$C$10,C1) mon
    2 Feb =COUNTIF($C$1:$C$10,C2) tue
    3 Mar =COUNTIF($C$1:$C$10,C3) wed
    4 Apr =COUNTIF($C$1:$C$10,C4) thu
    5 May =COUNTIF($C$1:$C$10,C5) fri
    6 Jun =COUNTIF($C$1:$C$10,C6) sat
    7 Jul =COUNTIF($C$1:$C$10,C7) sun
    8 Aug =COUNTIF($C$1:$C$10,C8) mon
    9 Sep =COUNTIF($C$1:$C$10,C9) tue
    10 Oct =COUNTIF($C$1:$C$10,C10) wed
    ]*[/ss][hr]*[/hr] Auto Merged Post;[dl]*[/dl]What do you think about making this into an free add-in for Excel, shg?

  • Re: New Ss Tag For Posting Spreadsheet Data


    FREE ADD-IN FROM MEMBER SHG TO MAINTAIN SPACING WHEN PASTING A RANGE FROM EXCEL INTO A POST ON THE OZGRID FORUM.


    This add-in simplifies copying data from a spreadsheet to a bulletin board like Oz, or other applications.


    HTML suppresses display of multiple spaces, so the column alignment of data copied from a spreadsheet is lost when you post. The add-in buffers columns to to a uniform width, so that when displayed in a fixed-pitch font, columns are aligned.


    The add-in offers three choices for enclosing data between tags: Code tags, for some bulletin boards; an "ss" tag created by Dave for Oz for exactly this purpose, so that code tags can be reserved solely for code; and no tags, for pasting to NotePad or another text editor.


    The add-in installs a menu item, Copy Rng. Click that, and follow your nose from there. Data is copied to the clipboard, from which you can paste into any application.


    Here's a sample output using ss tags:


    [ss=
    ---A---- ---B---- ----C---- ----D---- ----E---- -F-- ----G----- --H---
    1 LNAME CLASS PIC TYPE Size QTY Unit Price Total
    2 Doe MWF AM Butterfly Women S 2 11.00 22.00
    3 Poe MWF PM Butterfly Women M 3 11.00 33.00
    4 Soe TU/TH Butterfly Men's XXL 5 11.00 55.00
    5 Kato MWF AM Tiger Kid's L 8 11.00 88.00
    6 Jimbo MWF AM Butterfly Women S 2 12.00 24.00
    ]*[/ss]


    To install:
    1. Unzip and move the attachment to your add-ins directory (or any location, but remember it for step 2)


    In XP: C:\Documents and Settings\[username]\Application Data\Microsoft\AddIns\


    In Vista: C:\Users\[username]\AppData\Roaming\Microsoft\Addins.)


    2. Open Excel, do Tools > Add-Ins > Browse, select Range to Clipboard, Open.


    Alt+c is a shortcut to bring up the menu.


    To use:
    1. Select the spreadsheet range to copy
    2. Do Alt+c or click Copy Rng to bring up the form, and press the Copy button on the form (no, not Cntl+c)
    3. Paste (Cntl+v) into the target application


    The form is not modal, so you're free to move around the spreadsheet to select ranges when the form is up. Also, options will persist if you close and reopen the form.


    I hope you find it convenient.

  • Re: New Ss Tag For Posting Spreadsheet Data


    Code
    1. Looks like the code cleaners have been in .....

    Yep, that was me -- not sure how, but it was me.


    Dave added a menu item to invoke the userform. I liked that a lot, and wanted to add some other internal changes for Excel 2007, so I took Dave's version, cut in the changes, and then reposted it -- apparently fat-fingering it in the process.


    Then I had a problem with the AddInInstall event that added the control -- it froze the Excel user interface (for me, Dave had no problem), which could only be restored by setting ScreenUpdating in the Immediate window. Couldn't solve that mystery, but changing it to the Open event did the same thing with no freeze.


    Anyway, here's the current version, and thank you for your interest.


    FYI, it doesn't like numbers in accounting format (presumably the "* " component) -- the text property for such cells returns widely varying string lengths, with zero ("$ - ") returning a much longer string than non-zero values. Any thoughts welcome.

  • Re: New Ss Tag For Posting Spreadsheet Data


    shg, I have uploaded the latest .zip file from your post above to the intruction post as that is where the header link points.


    Great job and thanks!

  • Re: New Ss Tag For Posting Spreadsheet Data


    Hi shg,


    In your instuction to install your addin you refer to RangeToClipboard1


    Quote

    2. Open Excel, do Tools > Add-Ins > Browse, select RangeToClipboard1, Open.


    When I downloaded the addin the other day and unzipied it a got RangeToClipboard.xla


    Waht is your latest version?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Shg,
    Using your add.in when I copy the attached xl sample with tags and post to ozgrid it works fine. Bur when I copy without tags and post to MSWord the column alighnment goes screwy.
    Any suggestions? Should the columns be lined up if copying to MSWord?


    [ss=
    ----------A----------- B --C-- ---D--- ---E--- ---F--- ---G---
    4 Investments Phase 2007 2008 2009 2010
    5 Percent in Market 1.0% 30% 29% 28% 27%
    6 Percent in Fixed 70% 71% 72% 73%
    7 Beginning Investments Rate 50,000 53,075 56,310 59,711
    8 Interest Earned Market 10.0% 1,500 1,539 1,577 1,612
    9 Interest Earned Fixed 4.5% 1,575 1,696 1,824 1,962
    10 Ending Invenstment 53,075 56,310 59,711 63,285
    ]*[/ss]

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: New Ss Tag For Posting Spreadsheet Data


    If you have the version with the GUI and the menu bar, you're good to go.


    Quote

    Should the columns be lined up if copying to MSWord?

    Yo, Bill! Gotta use a fixed-pitch font -- Courier, Lucida Console, Consolas ...

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: New Ss Tag For Posting Spreadsheet Data


    You're welcome, Bill. Please feel free to suggest changes or improvements.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: New Ss Tag For Posting Spreadsheet Data


    Dave,


    Quote

    who is alienscript?


    Do not know what you are revering to?

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: New Ss Tag For Posting Spreadsheet Data


    So, I'm copying a text in the formula field so that it appears in a single cell in excel (keeping its original format). But when I use this macro, I get an error and the macro stops. Is there a way for me to copy text into a single cell, keeping its format, that I can then use this macro to post in a BB?


    Thanks in advance.