Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automatic file naming

    I

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic file naming

    This is a rough adapted version of what I use:

    Sub AFN()
    '
    Workbooks.Add
    On Error GoTo nosave
    f = "Drive:Folder"
    ChDir f
    n = Val(Range("D8"))
    IX = f + "Index.txt"
    Open IX For Input As #1 ' Open file for input.
    Input #1, NextFile
    Close #1
    NextFile = NextFile + 1
    Open IX For Output As #1
    Print #1, NextFile
    Close #1
    Range("D8") = NextFile
    Name = f + "" + Mid$(Str$(NextFile), 2) + ".xls"
    ActiveSheet.SaveAs Filename:=Name, AddToMru:=True
    Application.RecentFiles.Add Name:=Name
    ActiveWorkbook.Saved = True
    Exit Sub
    nosave:
    MsgBox "File Not Saved", , "Please Note:"
    End Sub

    Notes:
    Replace the line F = "DriveFolder" with "c:MyDocuments" or whatever.
    Create a txt file with a number in and save as index.txt.

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

    Re: automatic file naming

    This may or may not be difficult to do depending on the answers to the questions below:

    1- Is this template going to be shared by more than one user, and, if so, how is this sequential number to be handled between users?

    2- Have you thought about where you want to keep track of the sequential number? In the Registry (under what key)? In a file somewhere. Do you just want to look at all the files in the directory where the file is to be stored and determine the highest number (might be slow if there are a lot of files)?

    3- If you keep the number in the Registry or a file, and go to save a new workbook but there is already one there by the next sequential name, what do you want to do?

    4- Have you thought of useing a name consisting of the date plus a sequential number (if more than one can be created per day)? This can be a little easier to do.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic file naming

    Hi and thanks

    I think it will be better to save the number in the registry under a key like HKEY_CURRENT_USERSoftwareMORDYSOS2.
    A new file will be opened by A user, but many users will open many files on the Network. There will not be a need to share files.
    the savining must be done as the file is opened so it get its number ASAP.
    I like the nuber system, the date one has spaces and I think is more complicated. why do think its easy?

    Thanks

    Mordy

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

    Re: automatic file naming

    Using the date with a sequential number on the end does not have to have spaces in it. It is easier because it only takes a few lines of code to implement. The following VBA code will save the active workbook with names like 2001-03-06-001.xls. No spaces and the files will sort in order in an explorer window that is sorted by name.

    <pre>Public Sub DoFileSaveAs()
    Dim strUserPath As String, strFileName As String, strWk1 As String
    Dim I As Integer
    strUserPath = "C:Work"
    I = 0
    Do
    I = I + 1
    If I = 1000 Then
    MsgBox "No available File Names."
    Exit Sub
    End If
    strFileName = Format(Date, "yyyy-mm-dd") & "-" & Format(I, "000") & ".XLS"
    strWk1 = Dir(strUserPath & strFileName, vbNormal)
    Loop While strWk1 <> ""
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs strUserPath & strFileName
    Application.DisplayAlerts = True
    End Sub
    </pre>


    Using sequential number and keeping them in the Registry will take much more code that the above, particularly if you properly take care of errors that you can get reading and writing to the registry. I can put together something (without complete error handling) if you really want to see it.
    Legare Coleman

Posting Permissions

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