Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    SAVEAS with DATE (Excel 2000)

    Hi all again

    I'm trying to save a file with a name that I tell it ,and a date format, it's not working

    Also I want to shorten my Msgbox, by using _ return, but everytime I try to do that, the checker thinks I'm going to the next line.


    Private Sub CommandButton1_Click()
    If UserForm1.ListBox1.ListIndex = 1 Then ' DOTFilm
    Sheets("Film_MAG").Visible = True
    Sheets("film_YELLOW").Visible = True
    Sheets("film_Black").Visible = True
    Sheets("film_CYAN").Visible = True
    Sheets("Sheet1").Visible = False
    Sheets("film_Black").Select
    Range("A7").Select
    MsgBox ("PLEASE ENSURE THAT X-RITE CABLES ARE CONNECTED TO COMPUTER BEFORE READING BLACK FILM 91%."), , "FILM DOT GAIN"
    MsgBox ("PLEASE ENSURE THE CENTURFAX PROGRAM IS NOT ACTIVE IN THE WINDOWS TASKBAR TRAY, AND THAT PROGRAM IS ""XKEY"" IS YOUR ACTIVE PROGRAM."), , "FILM DOT GAIN"
    ThisWorkbook.SaveAs Filename("DOTFILM" & Format("dd:mm:yy"), (XLS))

    Thanks
    Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SAVEAS with DATE (Excel 2000)

    1. You don't specify the argument correctly. You omit the date from the Format function. Moreover, you cannot use either of the characters / : * ? " < > | in a file name, because they have special meanings, for example the : in C: indicates a disk drive. You also handle the extension incorrectly. You could use

    ThisWorkbook.SaveAs Filename:="DotFilm" & Format(Date, "ddmmyy") & ".xls"

    or

    ThisWorkbook.SaveAs Filename:="DotFilm" & Format(Date, "dd-mm-yy") & ".xls"

    2. If you want to display a multiline message box, concatenate strings with vbCrLf

    MsgBox "Please ensure the CenturFax program is not active in the Windows taskbar tray," & vbCrLf & _
    "and that ""XKey"" is your active program.", "Film Dot Gain"

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SAVEAS with DATE (Excel 2000)

    Thank you Hans.

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

    Re: SAVEAS with DATE (Excel 2000)

    One addition to what Hans said. If you want the files to sort into the correct chronlogical order in Windows Explorer, then I would suggest using this format for the filename:

    <pre> ThisWorkbook.SaveAs Filename:="DotFilm" & Format(Date, "yyyy-mm-dd") & ".xls"
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SAVEAS with DATE (Excel 2000)

    You're still using film?

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SAVEAS with DATE (Excel 2000)

    Thanks Legare.

Posting Permissions

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