Results 1 to 7 of 7
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    How to print all cells as a list (2000/any)

    Hello, Excel experts. Is there an easy way to print the contents of all the cells in a spreadsheet as a list? (This would be useful in registering the copyright in the spreadsheet, for those who might wonder.) Back when I used SuperCalc

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to print all cells as a list (2000/any)

    If you want to do it interactively via the print menu, you can select the cells in question and then on the Print Dialog pick Selection from the Print What options.

    Is that any good to you ?

    AndrweC

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to print all cells as a list (2000/any)

    Can you define what you mean by "contents of all cells" and "as a list?" Do you want to print the value of the cells or the formulas? By list, do you mean each cell on a separate line down the page? If so, do you want the cells identified in some way? What do you want to do with empty cells?
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to print all cells as a list (2000/any)

    If you are trying to document the contents of a s/sheet, I would suggest changing to the 'formula view' (MS may have another name for this view):
    <pre>Tools | Options | View tab | - checkbox under "Window Options"
    </pre>

    and then print the s/sheet with row and column headings (and gridlines) turned on
    <pre>File | Page Setup | Sheet tab | - two check boxes under "Print"
    </pre>


    I question the usefulness of establishing copyright on a s/sheet, since you cannot copyright the underlying ideas, only the particular expressions of them. Reverse-engineering any s/sheet is trivial (more or less) for anyone who has seen the copyrighted sheet and is familar with the subject matter. In any event, that's your call.

    If you absolutely HAVE to have a "list" of cell contents, I would do something like the following pseudo code:
    <pre>for each worksheet
    create a new sheet called concatenate(originalname," LIST")
    CellCount = counta(entire sheet)
    for Counter = 1 to CellCount
    choose each non-blank cell in original sheet in sequence
    on "xxx LIST" sheet Col A, Row Counter = _
    relevant address of non-blank cell on "xxx"
    on "xxx LIST" sheet Col B, Row Counter = _
    value or formula for non-blank cell on "xxx"
    Next Counter
    Next worksheet
    </pre>


    For cells in the orignal worksheet that have formulas in them you could concatenate the formula with a string character to force them to display and print as a formula, rather than a value. This will probably also make values clearer, since the display will show either the value or an equals sign and the value, preceeded by whatever string character you have concatenated to the value...

    You will have to have a way of handling worksheets with > 65K non-blank cells (if there are any) since you will run out of rows: I would suggest moving over to columns D and E.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How to print all cells as a list (2000/any)

    Thank you all for your responses. I guess I was ambiguous. Yes, I want basically a "source code" listing showing the cell address and cell contents (when this is a formula, as a formula), as a very long list. Given the various options, I think I'm going to dump it into a Word document, since wrapping long formulas is easier (at least for me) in Word, and I'm infinitely more familiar with VBA in Word.

    As an aside on the legal issue:
    <hr>I question the usefulness of establishing copyright on a s/sheet, since you cannot copyright the underlying ideas, only the particular expressions of them. <hr>
    Well, I'm not talking about summing a list here. Any moderately complicated spreadsheet is way more than an idea. Even though Excel automates many of the hardest parts (like figuring out dependencies), there is enough original material in many spreadsheet projects to qualify for copyright protection. What someone could legally extract because it is merely an idea or is not original, will depend on the circumstances.
    <hr>Reverse-engineering any s/sheet is trivial (more or less) for anyone who has seen the copyrighted sheet and is familar with the subject matter.<hr>
    That is exactly why you might want to seek the protection of the law

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How to print all cells as a list (2000/any)

    Here is a Word VBA module that you can import into a new Word template that will read in the contents of cells from an Excel Workbook. Maybe it will help someone. If you think of any improvements, please post them back.

    Note: until you close the new document, a "hidden" instance of Excel appears in the task list (Ctrl-Alt-Del). I'm not sure how to close that in VBA, since I am not expressly opening it...
    Attached Files Attached Files

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to print all cells as a list (2000/any)

    I did not read your initial post properly yesterday, so hopefully the following will be of more assistance.

    You could try just printing the sheet to show formulas. If you go to Tools, Options, View and select Formulas under Window Options. Then in Page Setup, select the Sheet Tab and check Row and Column Headings. When you then print the sheet you should have a grid of the cells showing the formula in each. As a reading aid you might either print gridlines or place borders around each cell. You can set the columns to auto width to accommodate any lengthy formulae.

    The following code will loop through all the used cells in a sheet and output the address and the contents. It will print the cells in row order, i.e. all used cells in Row A come first, then B etc. Note that Cell.Formula will return a value if there is no formula as such. The code outputs to the immediate window, but you change it to print to an open file, or place the output as a String into another worksheet and then print that. <pre>Sub ListCellContents()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If Not IsEmpty(oCell.Value) Then
    Debug.Print oCell.Address & " " & oCell.Formula
    End If
    Next
    End Sub</pre>


    Hope that helps

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •