Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    File rename in VBA (EXCEL XP)

    I have a macro that uses Acrobat v6 to print a worksheet. I have set the defaults on Acrobat to automatically over write any existing pdf file in the target directory. The created adobe file name defaults to the workbook name. For example, lets say the created adobe file is in C:my documentsmyfile.pdf.

    What I would like to do within Excel VBA is rename this file, say to c:my documentsnewmyfile.pdf. I do not need the file name to be dynamic---the filename needs to be the same name month after month as this file is used on the company's intranet site. Then I need to copy it to a network directory. This file will already exist in the network directory [i.e., data from the previous month] so I will need the ability to over write the existing file or if this is not possible, then delete the previous month file and then copy the newmyfile.pdf file to the network directory. Finally, I would like to delete the c:mydocumentsnewmyfile.pdf.

    Can this type of activity be controlled within an excel vba code? I am very new at VBA, so any example code is greatly appreciated. THANKS.

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

    Re: File rename in VBA (EXCEL XP)

    The instruction to rename a file is Name oldname As newname. If the path for the new filename is different from the path of the old filename, the file will be moved. You must delete the existing file first, using Kill filename. For example:

    Sub MoveThatFile()
    Dim strSource As String
    Dim strTarget As String

    strSource = "C:My DocumentsMyFile.pdf"
    strTarget = "F:NetworkfolderNewFile.pdf"

    ' Test if target file exists
    If Not Dir(strTarget) = "" Then
    ' Yes, so delete it
    Kill strTarget
    End If

    ' Move source file
    Name strSource As strTarget
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    Hans,
    Thanks...your code has got me started. I have one final question that I can't seem to understand from the VBA help. Can you have more than one sub/end sub in a Module? If so, can you "call" this module via a short-cut key combination? When I click tools, macros, macros and then options, only the subroutines show up to assign a short-cut key. I can't seem to discern if you can place more than one sub routine in a module or why you would want to if you can't execute all the subs in the module via a short-cut. Could I execute the module from a button on the worksheet or button on the toolbar? The worksheet I am now working on has three modules as I have placed the subroutine codes in a new module. The three modules [sub-routines] need to run in order and, in fact, can run in sequence without user intervention. Now, I click three different short cut keys to execute each sub routine which is in separate module [Module1, Module2, Module3]. It would seem that now that I have the sub routines working, it would be nice to automatically run them sequentially. THANKS.

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

    Re: File rename in VBA (EXCEL XP)

    You can't call a module - a module is just a container for code. A module can contain many procedures (subs) and functions. Procedures and functions can call other procedures and functions, both in the same module and in other modules in the same workbook. There is an exception: if a procedure or function is preceded by the keyword Private, it cannot be called from another module.

    You can create a macro (that is a procedure without arguments) that calls the three macros you now have:

    Sub RunAll()
    Call Macro1
    Call Macro2
    Call Macro3
    End Sub

    (the keyword Call can be omitted, if you prefer). Macro1, Macro2 and Macro3 are the names of the procedures, not the names of the modules.

    You can then assign RunAll to a keyboard shortcut and/or a custom toolbar button.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    Hans,
    As always thank you for the advice...You have given me enough information in this thread, that I'll be able to piece the puzzle together to get it to work. Again, thank you.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    IMHO, the <font color=blue>Call</font color=blue> keyword is something we should always use. It is not necessary and the code will run just fine without it. Using <font color=blue>Call</font color=blue> makes the reading and troubleshooting of code easier, especially when, a year later, you go back to something you developed and you are working thru the code to modify it or to see how you did something.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    Hans,
    I have been digesting this code and converting it to my application these past weeks. I have another Excel workbook application where I would need the code to be worksheet specific--NOT workbook specific.

    For example, I have a workbook with 25 worksheet tabs. I cannot separate the worksheets into separate individual workbooks as they are created via macro allocations, etc. When a worksheet, say "expenses" is printed via Adobe, it defaults to the workbook name. I then, via explorer, re-name this pdf file changing it from the workbook file name to say expense.pdf. I then return to EXCEL and select the next worksheet tab, say "revenue" and print it via Adobe. Again, I minimize EXCEL, select explorer and rename this file from the workbook name to revenues.pdf. If I fail to re-name the pdf file between printing the "expense" worksheet and "revenue" worksheet, all that I am left with is the Adobe pdf workbook file name with the revenue worksheet printed as Adobe over writes the pdf file each time I print [i.e. the workbook pdf file with the expense worksheet is over written by the printing of the revenue worksheet].

    Sorry for the extensive detail, but my question is, can I use the code you provided, but have it specific to each worksheet as I need to control the "rename" process [strTarget] as the adobe pdf rename process is worksheet specific [i.e., like I manually do via windows explorer]? If not, can you think of any work-around? THANKS.

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

    Re: File rename in VBA (EXCEL XP)

    Hans: What is wb in that code? Shouldn't it be wbk?
    Legare Coleman

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

    Re: File rename in VBA (EXCEL XP)

    Air code, and not very good air code at that... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks, I'll correct it.

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

    Re: File rename in VBA (EXCEL XP)

    Edited by HansV to correct typos - thanks, Legare!

    Something like this?

    Dim wbk As Workbook
    Dim wsh as Worksheet
    dim strWorkbook As String
    dim strWorksheet As String
    dim strPath As String

    Set wbk = ActiveWorkbook
    strWorkbook = Replace(wbk.FullName, ".xls", ".pdf")
    strPath = wbk.Path
    If not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If

    For Each wsh In wbk.Worksheets
    ' code to print worksheet to .pdf file
    ...
    strWorksheet = strPath & ws.Name & ".pdf"
    Name strWorkbook As strWorksheet
    Next wsh

    Set wsh = Nothing
    Set wbk = Nothing

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    Hans,
    As always thanks for the help with the code. One issue has arisen that I had not anticipated.

    It takes several seconds for Adobe to release the pdf it has just created. This Excel code is trying to rename the file to the worksheet name while its still under the control of Adobe. I need to build a 10 to 15 second delay after the print command is executed and prior to the rename macro command.

    Is there a "delay" or "pause" function in Excel VBA or should I just put in an arbitrary counter, via a for next/ loop, to count from 1 to 10,000? The purpose of the for/next counter loop is to do nothing more than cause a "delay" in code execution? THANKS.

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

    Re: File rename in VBA (EXCEL XP)

    You can use

    Application.Wait Now + TimeValue('0:00:15")

    to wait for 15 seconds. (A loop is not a very good idea because it takes up a lot of processor resources)

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: File rename in VBA (EXCEL XP)

    Hans,
    Thanks for the answer and thanks also for the advice....I hadn't thought about the resource cycle issue. Again, thanks.

Posting Permissions

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