Date:         Thu, 21 Dec 2006 13:05:11 -0800
Reply-To:     "William W. Viergever" <william@VIERGEVER.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "William W. Viergever" <william@VIERGEVER.NET>
Subject:      Re: Reading "mixed" Excel dates
In-Reply-To:  <42051F34E465B44984373376532AFE1B03E0B1@incomx04.lgmt.trdo>
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 12:37 PM 12/21/2006, Barz, Ken wrote:
>I have to access data from an Excel spreadsheet in which the owner's
>have a column of dates.  In some cases the dates are their proper
>selves, and in others they have preceded the date with a single quote.
>What I ultimately get to read into SAS is the output of an Excel macro
>that outputs a .txt file.  So, the single quote preceded values come out
>as dates and I can read them in with an informat.  Is there anything I
>can do using (base) SAS with those that come out as unformatted Excel
>dates such as 38922?


ken:

here's some vba code i threw together b/c i was so pi__ed off once
when copying a bunch of tested/de-bugged "formulas" from the
development workbook to other final-product/client ones b/c all the
new stuff still was pointing back to the original workbook (i.e., it
had created "links")

anyway, what i did was insert a leading apostrophe on *all*
cells/formulas on a given sheet, which made it text and not formulas

then did my copy/paste to the new/other workbook

then removed the leading apostrophes

and voila! - all was well

anyway, maybe it'll help you

simply pre-select a range, then invoke the appropriate macro

later


'Removes hidden apostrophes that are first characters.
'Works on cells with formulas, text, or values.
Public Sub ApostroRemove()
     Dim currentcell As Range
     For Each currentcell In Selection
         If currentcell.HasFormula = False Then
             'Verifies that procedure does not change the
             'cell with the active formula so that it contains
             'only the value.
             currentcell.Formula = currentcell.Value
         End If
     Next
End Sub

'Appends hidden apostrophe as first character.
'Works on cells with formulas, text, or values.
'Excellent for displaying formulas when printing.
Public Sub ApostroPut()
     Dim currentcell As Range
     For Each currentcell In Selection
         'Prevents inserting apostrophes in blank cells.
         If currentcell.Formula <> "" Then
             currentcell.Formula = "'" & currentcell.Formula
         End If
     Next
End Sub



---------------------------------------------------
William W. Viergever
Viergever & Associates
Health Data Analysis / Systems Design & Development
Sacramento, CA 95825
william@viergever.net
(916) 483-8398
---------------------------------------------------