Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autoname Workbook (97)

    I have a couple of things I need to do:

    I have a CSV file with approximately 100 rows of data. I need to rename the workbook to an xls, but name it like the following:

    103102_workbookname.xls

    The first seven characters must be the date followed by an underscore.

    I also need to rename the sheet, but it looks like that topic has been discussed...I'll look there for help.

    I can set it up where a cell would populate with that info, but is there a way to get the workbook to rename itself using a macro?

    And if so, can I specify where it is saved? Preferably a network folder.

    Thanks in advance for the help!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoname Workbook (97)

    Something like this:

    <pre>Sub test()
    Dim sFilename As String
    Dim spath As String
    spath = "c:windwostemp"
    sFilename = Format(Month(Now), "00") & _
    Format(Day(Now), "00") & Right(Year(Now), 2) & "_"
    sFilename = sFilename & "WorkbookName.xls"
    ActiveWorkbook.SaveAs Filename:=spath & sFilename, FileFormat:=xlWorkbookNormal
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoname Workbook (97)

    Try this as a start.... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Change the path as appropriate.

    Public Sub DatedFileName()
    Mo = Left(Date, 2)
    Dy = Mid(Date, 4, 2)
    Yr = Right(Date, 2)
    NewFileName = Mo + Dy + Yr + "_" + ActiveWorkbook.Name
    ActiveWorkbook.SaveAs FileName:= _
    "C:WINDOWSDesktop" + NewFileName + ".xls", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    End Sub


    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoname Workbook (97)

    Does the following not work in XL97? It works fine in XL2K and seems a bit simpler.

    <pre> sFileName = Format(Now(), "mmddyy") & "_Workbookname.xls"
    </pre>

    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoname Workbook (97)

    I guess it should.

    But I like the more complicated one <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoname Workbook (97)

    pieterse ,

    Thanks a bunch. Yours worked fine.

    I imagine the other one would too.

    Thanks to all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •