Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change an existing Filename to the Active Filename (Excel XP/2002)

    Hi -

    I'm relatively new to VBA in Excel and I have a question. Is there a way to change / update / replace a Template filename in a Module to reflect the current active document's filename. For example replace "Test.xlt" to "Test1.xls"?? Any help would be greatly appreciated.

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

    Re: Change an existing Filename to the Active Filename (Excel XP/2002)

    You can use the instruction

    Name oldname As newname

    to rename a file, but why would you want to rename a template to a workbook?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Change an existing Filename to the Active Filename (Excel XP/2002)

    When an Excel Template is correctly installed as a Template in one of the default Templates Folders, it is automatically renamed when the user opens it, exactly as you ask, "Test.xlt" is opened as "Test1.xls".
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Change an existing Filename to the Active File

    I'm not very conversant with Excel VBA, but it probably works much like Word. When you want to refer to the document that the user had active at the time the procedure was invoked, you can use ActiveDocument. Since Excel's objects are workbooks and sheets, there probably is a handy active object there, too. (If this doesn't address your question, then never mind.)

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Change an existing Filename to the Active File

    Yep, ActiveWorkbook.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change an existing Filename to the Active Filename (Excel XP/2002)

    Hi and thanks for your comments. Perhaps I should have more adequately explained my project. I have created an input form as a template that others will be using. When they open the copy of the template, sure enough, the file will change to an XLS file type. However, the data that these different folks will be using must be captured in a central "database" file. The way that I went about sending the input form data to the central database was to copy and paste, which I recorded as a macro, so I'm copying and pasting between two files. (I tried the template wizard with data tracking, but I exceeded the 100 field limit) NB - Access is not an option at this point in time.
    I created the macro in the template so that it could be accessed by each user each time the template copy (xls) was opened. When the macro runs from a copy of the template, the macro stops because the file is no longer "test.xlt" but is now "test1.xls".
    I've pasted some of the code below. Thanks for any help you can give me.

    Windows("Test.xlt").Activate
    Range("C164").Select
    Selection.Copy
    Windows("Activity Report Form DatabaseV7.xls").Activate
    ActiveSheet.Paste
    Windows("Test.xlt").Activate
    Range("B12").Select

    So here, I need the Test.xlt to actually change to reflect the template copy's filename

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

    Re: Change an existing Filename to the Active Filename (Excel XP/2002)

    The best way is not to use the name of the workbook. When the user creates a new workbook from the template, its name might be Test1, or Test2 etc. Store the workbooks and worksheets you are using in variables.

    Dim wbkSource As Workbook
    Dim wstSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wstTarget As Worksheet

    ' Do this while you're sure the new workbook is active
    Set wbkSource = ActiveWorkbook
    Set wstSource = wbkSource.Worksheets("Sheet1")
    ...
    Set wbkTarget = Workbooks.Open("Activity Report Form DatabaseV7.xls")
    Set wstTarget = wbkTarget.Worksheets("Total")

    wstSource.Range("C164").Copy Destination:=wstTarget.Range("A3")
    ...

    ' Clean up at the end
    Set wstTarget = Nothing
    Set wbkTarget = Nothing
    Set wstSource = Nothing
    Set wbkSource = Nothing

  8. #8
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change an existing Filename to the Active Filename (Excel XP/2002)

    Thanks Hans - That worked perfectly; just what the Module ordered! Thanks for your quick and responsive help.

Posting Permissions

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