Visual Basic for Applications (VBA) is a subset of Visual Basic that is used to tailor Microsoft Office programs to suit your needs. When writing to a file in VBA, it's good practice to make sure that particular file exists before you attempt to write to it. If you want to find if a file exists in Excel, build a VBA module that does the trick. In order to use this function, you should have some prior experience with VB programming.
- Skill level:
Other People Are Reading
Things you need
- Excel 2007 or Excel 2010
Copy the following code:
Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file ' or folder exists, false if not. 'PathName : Supports Windows mapped drives or UNC ' : Supports Macintosh paths 'File usage : Provide full file path and extension 'Folder usage : Provide full folder path ' Accepts with/without trailing "\" (Windows) ' Accepts with/without trailing ":" (Macintosh) Dim iTemp As Integer 'Ignore errors to allow for error evaluation On Error Resume Next iTemp = GetAttr(PathName) 'Check if error exists and set response appropriately Select Case Err.Number Case Is = 0 FileOrDirExists = True Case Else FileOrDirExists = False End Select 'Resume error checking On Error Goto 0
'Macro Purpose: To test the FileOrDirExists function with Windows 'Only included to demonstrate the function. NOT required for normal use! Dim sPath As String 'Change your directory here sPath = "C:\Test.xls" 'Test if directory or file exists If FileOrDirExists(sPath) Then MsgBox sPath & " exists!" Else MsgBox sPath & " does not exist." End If
'Macro Purpose: To test the FileOrDirExists function with a Macintosh 'Only included to demonstrate the function. NOT required for normal use! Dim sPath As String 'Change your directory here sPath = "HardDriveName:Documents:Test.doc" 'Test if directory or file exists If FileOrDirExists(sPath) Then MsgBox sPath & " exists!" Else MsgBox sPath & " does not exist." End If
Open Excel and press "Alt-F11" to enter the Visual Basic Editor.
Click "Insert" and then click "Module."
Paste the code into the right-hand pane by pressing "Ctrl-"V."
Change "text.xls" to the file name you are searching for.
Press "F5" to run the procedure. The procedure will return a pop-up window telling you whether the file exists.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for