Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rename Filename (Access 2002)

    Hi

    I obtained this great piece of code from the forum and wish to modify it slightly, but I am having difficult doing so.

    Public Sub ExportRename(Macrono As Integer, sPath As String, ByVal FileName As String)
    On Error GoTo Err_Handler

    ' strfilename must provide full path of file
    ' Note: Can use Name statement to move file to new folder
    ' Assumes file is named *.txt:

    FileName = "rp_" & FileName & ".txt"
    PathFilename = sPath & FileName


    Select Case Macrono
    Case 1
    DoCmd.TransferText acExportFixed, "Comp_ind Export Specification", "comp_ind", PathFilename
    End Select

    Dim strMsg As String
    Dim strNewfilename As String
    Dim n As Integer

    strNewfilename = left(PathFilename, Len(PathFilename) - 4) & ".lis"

    Name PathFilename As strNewfilename

    Exit_Sub:
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 58 ' File already exists - add number to filename & increment if necessary
    n = n + 1
    strNewfilename = left(PathFilename, Len(PathFilename) - 4) & n & ".lis"

    Resume
    Case 75 ' Path/File access error (file open)
    strMsg = "The file you are trying to rename (" & PathFilename & ") is open. " & _
    "Plz close file and try again."
    MsgBox strMsg, vbExclamation, "OPEN FILE ERROR"
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "rename TEXT FILE ERROR"
    Resume Exit_Sub
    End Select

    End Sub

    This code exports data to a text file and then renames the text file name to include the extension lis (why, cos the programme to which this file is imported into has to have this file extension). If the filename with the lis extension already exists, then it renames the text file to rp_filename1.lis. However, what I want is to rename the existing file (rp_filename.lis) to rp_filename1.lis and to rename the txt file to rp_filename.lis. This is not the difficult bit. What the tricky part is if rp_filename1.lis already exists (along with rp_filename.lis) then it needs to rename rp_filename.lis to rp_filename2.lis and so on as required.

    If this is too difficult my back up option is to delete rp_filename1.lis and rename rp_filename.lis to rp_filename1.lis.

    I hope I have made myself clear on this.

    Thanks & Regards
    WTH

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

    Re: Rename Filename (Access 2002)

    The code you have will rename the exported file to rp_filename.lis; if that exists, to rp_filename1.lis, if that also exists, to rp_filename2.lis etc. This is relatively simple: only the file just created is renamed; the existing files are left unchanged. What you want, would start an ever growing chain of renaming. I don't think that is a good idea.

    You can delete an existing file with the Kill instruction: Kill "C:test.lis"

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Filename (Access 2002)

    Hi Hans

    Thanks for your thoughts on the matter.

    In hindsight I agree it is not the way to go, so I think I will go to my back up plan.

    Regards
    WTH

Posting Permissions

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