> Thanks for the idea? I just tried looking at an xls file, and it looks
> pretty binary-ish. I don't think I want the person handling this to have
> to deal with appending stuff to the file. A simple copy and paste out of
> email SEEMS simplest, but maybe not.
> 
> Harold


If the data were in an attached spreadsheet (or text file), then the 'Get
External Data' stuff would work.  You can set that up so it imports any new
data.

However, Excel already does what you want, it's called 'Text To Columns'.
By default, if you paste tab-delimited data Excel will break it up into
cells, eg if you copy a Word table or Access data.  You can manually set it
up to handle pipe chars.

A VBA macro is easy enough though, and it looks like this:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    
    With Target
        If .Cells.Count = 1 Then
            If .Column = 1 Then
                If InStr(.Value, "|") Then
                    .TextToColumns Destination:=Range(.Address), _
                                   DataType:=xlDelimited, _
                                   TextQualifier:=xlDoubleQuote, _
                                   ConsecutiveDelimiter:=False, _
                                   Tab:=False, _
                                   Semicolon:=False, _
                                   Comma:=False, _
                                   Space:=False, _
                                   Other:=True, _
                                   OtherChar:="|"
                    Call ResetTextToColumns
                End If
            End If
        End If
    End With

End Sub

Private Sub ResetTextToColumns()

    Dim rng As Excel.Range
     
    On Error Resume Next
     
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
    
    With rng
        .Value = "Dummy"
        .TextToColumns Destination:=rng, _
                       DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, _
                       ConsecutiveDelimiter:=False, _
                       Tab:=True, _
                       Semicolon:=False, _
                       Comma:=False, _
                       Space:=False, _
                       Other:=False, _
                       FieldInfo:=Array(1, 1), _
                       TrailingMinusNumbers:=True
        .Clear
    End With
    
End Sub


Right-click on the tab for the spreadsheet, click 'view code', and paste it
in.  This macro will only fire in response to change on that sheet.

It's really only one line, with a bit of error handling.  It checks you've
only pasted in one cell, and in the left-most column (A), and the data
contains pipes (|).  If that's ok, then it splits it up.

Excel remembers the last settings you used for 'Text To Columns', so if you
did the pipes manually (Data/TextToColumns/dialog options) then the next
time you pasted in pipe-delimited data, it would automagically break it up.
That's either really neat or a real pain in the arse depending on the phase
of the moon.  Note that it's now forgotten how to do tabs...

And that's what the ResetTextToColumns bit does, it sets the parameters back
to Tabs.  If you took ResetTextToColumns out, and pasted in pipe-delimited
data, the macro would fire.  However the second time you did it, the macro
WON'T fire, as Excel would do it for you since the TextToColumns parameters
are now set to pipe.

If you used Outlook then you could put a macro there, treating the Excel
spreadsheet as a database.  Other mail programs may be able to do that as
well.

Tony

-- 
http://www.piclist.com PIC/SX FAQ & list archive
View/change your membership options at
http://mailman.mit.edu/mailman/listinfo/piclist