File rename in VBA (EXCEL XP)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

IMHO, the Call keyword is something we should always use. It is not necessary and the code will run just fine without it. Using Call 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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hans: What is wb in that code? Shouldn’t it be wbk?

Reply | Quote

Air code, and not very good air code at that…

Thanks, I’ll correct it.

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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

Reply | Quote