Test for existence of folder from Excel in VBA
Test for existence of folder from Excel in VBA
(OP)
How do I test for the existence of a folder from a VBA module in Excel? GetAttr only works if the folder exists. There appear to be a whole heap of ways for testing for the existence of a file, but I can't figure out how to do it for a folder. The help on my MSDN disk I got with VB6 says to use "FileSystemObject.FolderExists(folderspec)" but that does not appear to be available to me in Excel '97.
Anybody got any suggests (apart from inelegant "on error ..." approaches!)
Anybody got any suggests (apart from inelegant "on error ..." approaches!)
Bung
Life is non-linear...





RE: Test for existence of folder from Excel in VBA
Dim strFolderName As String
Dim objFso As New FileSystemObject
blah
strFolderName = "C:\whatever"
blah
If Not objFso.FolderExists(strFolderName) Then
MkDir (strFolderName)
End If
etc
But why it isn't available almost as a default thing beats me!
Bung
Life is non-linear...
RE: Test for existence of folder from Excel in VBA
Sub listfiles()
Dim myrow As Integer
Dim myfile As String
myrow = 25
' retrieve a filename using Dir function
myfile = Dir("*.xls")
Do Until myfile = ""
Cells(myrow, 1) = myfile
myrow = myrow + 1
myfile = Dir
Loop
End Sub
RE: Test for existence of folder from Excel in VBA
Function file_exists(whatfile) As Boolean
Dim myfile, whatfile2 As String
file_exists = False
' retrieve a filename using Dir function
myfile = Dir("*.xls")
Do Until myfile = ""
If myfile = whatfile Then file_exists = True
myfile = Dir
Loop
End Function
RE: Test for existence of folder from Excel in VBA
Bung
Life is non-linear...
RE: Test for existence of folder from Excel in VBA
Actually, it does exist.
if len(dir(Folder2Find,vbdirectory))>0 then folder exists
Quick, easy and mostly painless.
--VBArrrgh