EXCEL VBA easy question, who's available ?!?!?

  Heefie 23:10 11 Feb 2005
Locked

I recently found how to find if a file exists and to branch accordingly. I now need to do the same for a FOLDER, but it seems it's not so easy ...

I've therefore gone for the (supposedly) easy route of ignoring the errors, as follows ...

On Error Resume Next

For MyKA = 1 To 99

MkDir (MyPath & "Season" & MyKA)

Next MyKA

So, when the first non-existant FOLDER is found & therefore created ... how do I exit the loop ?!?!

This must be easy as pie (as usual !) but I have a lot of distractions (8 kids here tonight ...) !!

Thanks in advance for any help ...

  VoG II 23:28 11 Feb 2005

The following function tests for the existence of a directory. It returns True if the directory exists, and False if it does not.

Function dirExists(dirAndPath) As Boolean

The following function tests for the existence of a directory. It returns True if the directory exists, and False if it does not.

Function dirExists(dirAndPath) As Boolean

'this function checks for the existence of a directory. Its initial value is false
'It is set to true only if the directory exists

Dim tempVar
'remove any blanks that may be surrounding the directory and path
dirAndPath = Trim(dirAndPath)

'remove any \ that may be on end of the path
If Right(dirAndPath, 1) = "\" Then _
dirAndPath = Left(dirAndPath, Len(dirAndPath) - 1)

'set error trap and check for directory existence
On Error Resume Next
tempVar = Dir(dirAndPath & "\*.*", vbDirectory)
If Err = 0 And tempVar <> "" Then

dirExists = True
End If
'turn off error trap
On Error GoTo 0
End Function

The following macro illustrates using the above function:

Sub DemoUse()

If dirExists("C:\TEST") Then

MsgBox "The directory exists"

Else

MsgBox "The directory does not exist."

End If

End Sub

Dim tempVar

'remove any blanks that may be surrounding the directory and path

dirAndPath = Trim(dirAndPath)


'remove any \ that may be on end of the path
If Right(dirAndPath, 1) = "\" Then dirAndPath = Left(dirAndPath, Len(dirAndPath) - 1)

'set error trap and check for directory existence

On Error Resume Next

tempVar = Dir(dirAndPath & "\*.*", vbDirectory)

If Err = 0 And tempVar <> "" Then


dirExists = True

End If

'turn off error trap

On Error GoTo 0

End Function


Sub DemoUse()

If dirExists("C:\TEST") Then

MsgBox "The directory exists"

Else

MsgBox "The directory does not exist."

Exit Sub


End If
End Sub

  VoG II 23:31 11 Feb 2005

hopefully understandable - formatting to pot.

  Heefie 23:44 11 Feb 2005

Do you KNOW all this stuff, or are you better than me at searching it out ?!?!

I GOOGLE like a lunatic on this stuff, and normally find something that helps on the Internet ... somewhere ... & I try my hardest to adapt it, manipulate it, make it "mine", if you like ... but sometimes I hit the brick wall & I think you've helped me out every time that's happened !!!

Thanks ... again !!!

Mind, you, I haven't tried it yet, it might not work <g> !!!!!

  VoG II 23:58 11 Feb 2005

Post back if it doesn't work!

Google is a great search engine but if you need help with Excel use the search box at mrexcel.com or post here.

  Heefie 20:59 04 Apr 2005

... sorry !!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Nokia 8 vs Galaxy S8

Best new design books 2017: From vintage infographics to the the psychology of type

iMac 21.5-inch (2017) review

Nokia 8 : design, caractéristiques techniques, date de sortie