1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2003 macro: log contents of selected cell, clear cell

Discussion in 'Business Applications' started by raydancer, Apr 14, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. raydancer

    raydancer Thread Starter

    Joined:
    Apr 14, 2013
    Messages:
    1
    I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

    The macro I want to write will:

    • check that the selected cell's content is underlined before proceeding
    • copy the content of the currently selected cell into an external plain text .log file
    • .log file lines should be: year/month/day - time - username - cell contents
    • .log file names will probably need to be generated
    • clear the cell's content and formatting (particularly underline and text/background color)
    Here's what I have so far:

    Code:
    Sub Unpost()
    If Selection.Font.Underline = True
        Then Selection.ClearFormats And Selection.Clearcontents
        Else
            If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
                Then Selection.ClearFormats And Selection.Clearcontents
                Else Exit Sub
            End If
    End If
    End Sub
    If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, but I'm not sure how to go about putting it into my code, and have no idea how to generate and name new log files.

    Any help or ideas you could give would be greatly appreciated.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

    http://www.virtualsplat.com/tips/visual-basic-fso.asp

    Rollin
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1096118