Back to Blog
A better finder rename will not quit6/24/2023 Using this method, we can control when and how the function executes. In the example below, the fxRenameFile function only executes if cell A6 equals Y. TOP TIP: If we use the fxRenameFile function inside an IF function, it will only executes when the condition is met. If we run the function a second time, it will show FALSE, as the file has already been renamed.īe aware the function executes each time cells C2 or C4 change, so be careful with the order in which you update the cells. TRUE indicates that the file named in Cell C2 has been successfully renamed to the file named in Cell C4. Look at the screenshot above, our custom function is used in Cell C6: =fxRenameFile(C2,C4) MsgBox fxRenameFile(filePath, newFilePath)Įnd Sub Calling the function from a worksheetĪlternatively, we can call the function just like a normal worksheet function. 'True = File Renamed 'False = Error Occured The macro below calls the function and displays a message box with the following values: Calling the function from another macro.Function fxRenameFile(filePath As String, newFilePath As String)įxRenameFile = True End If 'Turn error checking back on On Error GoTo 0 End Function The VBA function below accepts two string arguments the existing file path and the new file path. MsgBox Prompt:="Unable to rename file", Buttons:=vbOK, _Įnd If 'Turn error checking back on On Error GoTo 0 End Sub Example 5: Reusable functionįinally, let’s create a reusable function for moving and renaming files. 'Display message if error occured If Err.Number 0 Then 'Ignore errors On Error Resume Next 'Rename file Name filePath As newFilePath NewFilePath = "C:\Users\marks\Documents\Example File Renamed.xlsx" 'Create variables to hold file names Dim filePath As String Dim newFilePath As StringįilePath = "C:\Users\marks\Documents\Example File.xlsx" Instead, a better option is to display a message box with an OK button. If there is an error, we really want to avoid going through the Visual Basic error debugging process. The errors are detailed in the section below. Trying to move files that don’t exist, or are locked for editing can trigger errors. Sub VBAMoveFile()Įnd Sub Example 4: Avoiding errors when renaming files For example, the code below moves the file from C:\Users\marks\Documents\ to C:\Users\marks\, but the file name remains the same. Sub VBARenameFileSheetNames()Įnd Sub Example 3: Move a file with the Name commandĭid you notice the Name command requires the file path and file name? Therefore, the Name command doesn’t just rename files but can also move files. We can run the following macro to rename a file using these cell values. The screenshot below shows the current file name in Cell C2 and the new file name in Cell C4. In this example, we rename a file based on cell values. "C:\Users\marks\Documents\Example File Renamed.xlsx"Įnd Sub Example 2: Rename a file based on cell values Name "C:\Users\marks\Documents\Example File.xlsx" As _ This example renames a file from Example File.xlsx to Example File Renamed.xlsx. Name appears in blue because it is a reserved word within VBA. To rename a file with VBA we use the Name command. Example 4: Avoiding errors when renaming files. Example 3: Move a file with the Name command.Example 2: Rename a file based on cell values.
0 Comments
Read More
Leave a Reply. |