Autoname Workbook (97)

Topic

New Reply

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!

Reply | Quote

Replies

Something like this:

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

Reply | Quote

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

    sFileName = Format(Now(), "mmddyy") & "_Workbookname.xls"

Reply | Quote

I guess it should.

But I like the more complicated one

Reply | Quote

pieterse ,

Thanks a bunch. Yours worked fine.

I imagine the other one would too.

Thanks to all.

Reply | Quote

Try this as a start….

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

Reply | Quote