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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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):

Tools | Options | View tab | - checkbox under "Window Options"

and then print the s/sheet with row and column headings (and gridlines) turned on

File | Page Setup | Sheet tab | - two check boxes under "Print"

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:

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

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.

Reply | Quote

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:
[indent]

---

I question the usefulness of establishing copyright on a s/sheet, since you cannot copyright the underlying ideas, only the particular expressions of them.

---

[/indent]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.
[indent]

---

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.

---

[/indent]That is exactly why you might want to seek the protection of the law

Reply | Quote

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…

Reply | Quote

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.

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

Hope that helps

Andrew C

Reply | Quote