Return to Archive

SAS Tip of the Month
April 2004

I had a company come to me a few months ago and ask me to import some data in to a SAS dataset from an Excel spreadsheet. Quite simple you may think but this request had one small wrinkle - the spreadsheet had cell comments that also had to be imported in to a comment field. After a little bit of time and research on Excel and VBA I would like to share with you the VBA code that was used to bring the cell comments to a specified cell in Excel:

   Function GetCellCommentText(rCellComment As Range)
      On Error Resume Next
      GetCellCommentText = WorksheetFunction.Clean(rCellComment.Comment.Text)
      On Error GoTo 0
   End Function

To enter the VBA code into Excel select

   Tools > Macro > Visual Basic Editor

then

   Insert > Module

Then paste the code above and then press

   File > Close and Return to Microsoft Excel

The function is now ready for use within Excel and can be accessed from the User Defined function list. Now you may be asking how did I run this new function from SAS and import the data into a SAS dataset? For that answer you will have to wait for later in the year.