We need to store the user’s response, so we will create a variable to hold the response. If the Folder variable contains an empty string, we will prompt the user to ask if they wish to create the folder. Folder = Dir(Path,vbDirectory)Īs we did earlier, we will check to see if the response returns an empty string. Because this is a folder, we will use the optional constant vbDirectory in the DIR function. We will set the Folder variable to hold the folder location stored by the Path variable. We will set the Path variable to point to a folder that does not exist: Path = “C:\Users\LG\Desktop\VBA\S12” Path – Hold the full folder\filename informationįolder – Hold only the folder name Dim Path as String If the folder does not exist, we will prompt the user and ask if they would like to create the folder. In this task, we will check to see if a folder exists. This confirms that the file exists in the defined folder. If FileName = ThenĮxecute the code by pressing F5 and observe the response. The completed code should look like the following: Sub FileExists()įileName = ("C:\Users\LG\Desktop\VBA\S2_recordMacros_start.xlsx") If the file does exist, this first version will simply show the filename in a message box. If the file does not exist, we will display a message stating such. We will test for the empty string response with an IF statement. If the file does not exist, the DIR function will return an empty string. In our example we will use the following code: FileName = (“C:\Users\LG\Desktop\VBA\S2_recordMacros_start.xlsx”) We will store the response in the FileName variable we created in the previous step.įileName = (“your folder name\your file name”) The next step is to query a folder for a file and return the filename if it exists, or an empty string if the file does not exist. The DIR function returns a string, so we need to declare a variable named FileName to hold the returned value. Question Mark (?) – This wildcards character will allow for any character in a single character positionĮxample: ?cel (The first and second characters can be anything, but the third through fifth characters must be “cel”)Įx ?el (The first and second characters must be “Ex”, the fourth and fifth characters must be “el”, but the third character can be anything)įirst, open the Visual Basic Editor ( ALT-F11) and create an empty module (i.e. There are two wildcard characters:Īsterisk (*) – This wildcard character will allow for any character(s) in any quantity.Įxample: Exc * (any text starting with “Exc”)Įxc*el (any text starting with “Exc”, ending with “el”, and any character in between) Wildcards represent “any characters” and are useful when you want to capture multiple items in a search based on a pattern of characters. You can filter for files with a specific attribute by using the constants listed above.Īn interesting thing you can use with the DIR function are wildcards. The default is vbNormal, which are files with no specific attributes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |