Showing posts with label MsgBox. Show all posts
Showing posts with label MsgBox. Show all posts

Delete Files Using VBA

May 26, 2019 Add Comment
VBA Delete Files Macro helps to Delete Files using VBA from a folder. 

Generally while dealing with the file operations we create or delete files using VBA in the process. 

For example when we are automating a specific task, we create lot of intermediate files and finally we produce one final output. At end we clean or delete all the intermediate files using VBA.

Sometimes we may regularly monitor outlook and when we receive a new mail with required input files. We will save these files in a specific folder to process these files and produce the final output.

And we will check before saving the input files in the folder and delete if there are any existing files are already available in the directory.


Solution(s):

We can use DeleteFile method of FileSystemObject to delete files using VBA. Following are the examples to delete a specific file or files from a location.

VBA Delete Files Macro – Example Cases:

Delete a specific file from a folder

Following is the example to delete a specific file from a folder or a directory.You can use DeleteFile method of FileSystemObject to Delete a specific file from a folder.

First it will check whether the file exists in the sourse location or not. If it exists it will delete from the directory ot it will display message.

Code:'In this Example I am deleting a File 'Sample.xls' which exists in the same location of the macro file 

Sub sbDeletetingAFile() 
Dim FSO 
Dim sFile As String 'Source File Location 

sFile = "D:\Job\" & "Sample.xls" 'You can change this Loaction 

'Set Object 
Set FSO = CreateObject("Scripting.FileSystemObject") 

'Check File Exists or Not 
If FSO.FileExists(sFile) Then 
'If file exists, It will delete the file from source location 
FSO.DeleteFile sFile, True  
MsgBox "Deleted The File Successfully", vbInformation, "Done!" 
Else 
'If file does not exists, It will display following message 
MsgBox "Specified File Not Found", vbInformation, "Not Found!" 
End If 
End Sub 

Output:

Case 1: 
If file does not exists in the source location to delete, It will display following message.



Case 2: 
If file is exists in the source location, It will delete the file from source location. And it will display following message.




The MsgBox Function

February 24, 2017 Add Comment



You’re probably already familiar with the VBA MsgBox function — I use it quite a bit in the examples throughout this book. 

The MsgBox function, which accepts the arguments shown in Table 15-1, is handy for displaying information and getting simple user input. It’s able to get user input because it’s a function. 

A function, as you recall, returns a value. In the case of the Msgbox function, it uses a dialog box to get the value that it returns. Keep reading to see exactly how it works.


Here’s a simplified version of the syntax for the MsgBox function:
MsgBox(prompt[, buttons][, title])
You can use the MsgBox function in two ways:
  1. To simply show a message to the user. In this case, you don’t care about the result returned by the function.
  2. To get a response from the user. In this case, you do care about the result returned by the function. The result depends on the button that the user clicks.
If you use the MsgBox function by itself, don’t include parentheses around the arguments. The following example simply displays a message and does not return a result. When the message is displayed, the code stops until the user clicks OK.
Sub MsgBoxDemo()
MsgBox “Click OK to begin printing.”
Sheets(“Results”).PrintOut
End Sub

Figure 15-1 shows how this message box looks.

You can also use the MsgBox function result without using a variable, as the following example demonstrates:
Sub GetAnswer2()
If MsgBox(“Continue?”, vbYesNo) = vbYes Then
         ‘ ...[code if Yes is clicked]...
Else
          ‘ ...[code if Yes is not clicked]...
End If

End Sub