Exploring Microsoft Excel and spreadsheets in general. See also Financial Equations, which has notes on Excel and financial equations.

Intro

Before the browser came along, the 2 "killer apps" (i.e. the most useful, popular, and flexible apps) were word processing apps and spreadsheet apps. Dan Bricklin and Bob Frankston co-created VisiCalc in 1979, the first computer spreadsheet. Since then others spreadsheet apps have come along including the following:

Spreadsheet apps have a basic interface of a rectangular grid of cells. Each cell can be referenced by concatenating row (usu. 1,2,3,...) and column (usu. A,B,C,...,AA,AB,...), i.e. the top and left most cell is usually referenced A1. Each cell may have a value or and equation. An equation may use cell references as variables in their equations. EG: Cell A3 may have the equation of =A1+A2.

Miscellany

There are so many little spreadsheet tricks and tips that I've used over the years. Many of them I can redo on the fly but I'm sure there were a number that I wish I had recorded somewhere. Better late than never I guess.

Here are some Excel formulas for generating IDs:

When you want to place a table from MS Excel into your web page in MS Front Page, it creates a lot of HTML, esp. CSS. One work around is to insert the Excel document into your web page with Insert > Insert File. This will create much cleaner HTML that is easier to clean up with the usual search and replace.

If an Excel file has a macro or has had a macro then when it is opened a popup window appears that asks to enable or disable macros. There is a security risk for Excel files with macros that you are not familiar with.

If you have an Excel file that shouldn't have any macros the popup window might appear if the file used to have a macro. To prevent the popup from appearing you could go to Tools > Macro > Security and lower the security (not recommended) or you could right-click on a worksheet, select View Code, and delete all traces of old modules and code.

See also these offsite links:

I haven't verified these macros (because I haven't had the need to do this) but supposedly it will take an Excel file with rows of content, and make a text file for each row.

Solution 1:

Range("A1", Range("A1").End(xlDown)).Select

Dim cell As Object
Dim counter, i As Integer
Dim cellArray() As Variant

'generate cell array

counter = 1

For Each cell In Range(ActiveCell, ActiveCell.End(xlDown))
ReDim Preserve cellArray(counter)
cellArray(counter) = cell.Value
counter = counter + 1
Next cell

'copy array to another worksheet

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Destination"

i = 1

For i = 1 To counter - 1
Worksheets("Destination").Cells(i, 1).Value = cellArray(i)
Next

From there you can add a command for exporting the Destination worksheet to a text file. EG: 

' Creating a Flat Text File from an Excel Spreadsheet
'  Let's not forget that we can create a Flat File (i.e., simple
'	Text File) with Excel just as we could directly from VB6 !
' *************************************************************

Sub FlatFiler()
    Dim Addr, Space, CCC, SCC, CBN, SC, CurrRow, LastRow
    Sheets("Revised").Select
    Space = Chr(32)
    Close #1
    Open "FlatFile" & Application.Text(Now(), "mmdd") & ".TXT" For Output As #1
    LastRow = Application.CountA(ActiveSheet.Range("A:A"))
    For CurrRow = 1 To LastRow
        Addr = Right(Cells(CurrRow, 1), 1)
        CCC = Space & Space & Cells(CurrRow, 3)  'Pad with 2 spaces
        SCC = Space & Space & Cells(CurrRow, 5)      'Pad with 2 spaces
        CBN = Cells(CurrRow, 7)
        SC = Cells(CurrRow, 9)
      Print #1, Addr; Space; CCC; Space; SCC; Space; CBN; Space; SC
    Next
    Close #1
    MsgBox "Done ------------------>"
End Sub

Solution 2:

Filepath = InputBox("Enter a filename, including full path (row number will be appended)", "", "C:\xceldata")
For i = 1 To Selection.Rows.Count
  Filename = Filepath & i & ".txt"
  Open Filename For Output As #1 ' Open file for output.
  Print #1, Selection.Cells(i, 1).Value ' Write data.
  Close #1 ' Close file.
Next i

These are all from http://monkeyfilter.com/link.php/5939.

A column with text can be split into separate columns.

Let's say you have data like this:

FullName
John Smith
Billy Bob Smith
James T. Kirk
Jane Smith-Wesson
Sue Smith Wesson

Simply select the data, go to the menu for Data > Text to Columns, and have it split by the delimiter of space. This will make new columns like this:

John Smith  
Billy Bob Smith
James T. Kirk
Jane Smith-Wesson  
Sue Smith Wesson

That needs a bit of hand tweaking in order to make this:

FirstName LastName
John Smith
Billy Bob Bob Smith
Jane Smith-Wesson
Sue Smith Wesson

or this:

FirstName MiddleName LastName
John   Smith
Billy Bob   Smith
James T. Kirk
Jane   Smith-Wesson
Sue   Smith Wesson

Note that if the original data was a LastFirstName column with data like "Kirk, James T.", then a split by comma into LastName and FirstName would require no manual tweaking. There are of course many tricky name variants such as  "Smith, Reginald, Jr.".

Here is VBScript code for a macro in Excel that takes the selected cells and outputs them the plainest HTML possible. I got this code from http://www.techimo.com/forum/showthread.php?t=106893. Note that I commented out certain parts so I get as simple a table as possible. 

Sub Export2HTML()
    'Select the cells before running this macro
    Dim Printstring As String
    Dim r As Long
    Dim c As Long
    Dim WebpageName As Variant
    Dim WebpageFile As String
    
    WebpageName = Application.GetSaveAsFilename(fileFilter:="Web pages (*.htm), *.htm")
    If WebpageName = False Then Exit Sub
    WebpageFile = Replace(WebpageName, ".html", "")
    WebpageFile = Replace(WebpageFile, ".htm", "")
    WebpageFile = WebpageFile & ".htm"
    Open CStr(WebpageFile) For Output As #1
    'Print #1, "<html>"
    'Print #1, "<body>"
    Print #1, "<table>"
    'Print #1, "<tbody>"
    For r = Selection(1).Row To Selection(1).Row + Selection.Rows.Count - 1
        Print #1, "<tr>"
        
        For c = Selection(1).Column To _
            Selection(1).Column + Selection.Columns.Count - 1
            Print #1, "<td>" & Cells(r, c).Text & "</td>"
        Next
        
        Print #1, "</tr>"
    Next
    'Print #1, "</tbody>"
    Print #1, "</table>"
    'Print #1, "</body>"
    'Print #1, "</html>"
    Close #1
    
    ActiveWorkbook.FollowHyperlink WebpageFile
End Sub

Dates and time in Excel is stored as days. EG: 1:00 = one hour is stored as 1/24 = 0.041666.

There is, of course, a ton of number formatting in Excel. A very common one is something like this: #,###.0;[Red](#,###.0);0.00;"BEGIN TEXT:"@":END TEXT", for positive numbers, negative numbers, zero, and text. However you can also do stuff like this: [<>1]#" item"; [=1]#" items"; General.

Links

Links that lead to off-site pages about MS Excel and spreadsheets.

Page Modified: (Hand noted: 2008-06-16 19:10:06Z) (Auto noted: 2008-06-16 19:10:01Z)