Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: create a copy of an opened excel file

    can anyone tell me how to create a copy of an opened excel file...i have tried "filecopy" but this can be used only if the excel file is closed...

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Here is one way.

    This routine
    1 saves the existing file (to save unsaved data)
    2 saves the file with a new name (ie a copy)
    3 opens the origional file
    4 closes the copy

    Code:
    Sub SaveCopy()
        Dim ThisBookName As String
        Dim ThisBookPath As String
        Dim NewBookName As String
        
        'REMEMBER FILE NAME AND PATH
        ThisBookName = ThisWorkbook.Name
        ThisBookPath = ThisWorkbook.Path
        
        'SAVE EXISTING WORKBOOK
        ThisWorkbook.Save
        
        'SET NAME OF COPY FILE
        NewBookName = "Copy of " & ThisBookName
        
        'DELETE ANY COPY OF THE COPY IF IT EXISTS
        If Dir(ThisBookPath & "\" & NewBookName) <> "" Then Kill ThisBookPath & "\" & NewBookName
        
        'SAVE BOOK AS A COPY
        ActiveWorkbook.SaveAs ThisBookPath & "\" & NewBookName
        
        'OPEN ORIGIONAL WORKBOOK
        Workbooks.Open FileName:=ThisBookPath & "\" & ThisBookName
        
        'CLOSE NEW COPY OF WORKBOOK
        Workbooks(NewBookName).Close
    End Sub
    There could well be a better (built in function?) but I havn't found it yet if there is one.

    HTH

    MTB

Posting Permissions

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