[RESOLVED] [Excel 2010] - Create folder or open folder if it is already created

Hi there,

I am new to coding of all kinds and have run into problems when trying to write a script to do the following in Excel 2010. I have been googling for the last couple of days and have not been able to find an answer, so would appreciate your help!

I’m making a macro that will check to see if a folder exists and do one of two things, depending on the result:
1: Create the folder if it doesn’t exist, and/or
2: Show the folder if it does exist

This is my code so far:

Code:

---

Dim sPath As String

sPath = "H:\My Documents\Prospects\" + Cells(ActiveCell.Row, 39).Value + "\" + Cells(ActiveCell.Row, 1).Value

‘Create the folder if it doesn’t exist
If Len(Dir(sPath)) = 0 Then
  MkDir sPath
End If

‘Show the folder if it exists
If Len(Dir(sPath)) = True Then
retVal = Shell("explorer.exe " & sPath, vbNormalFocus)
End If

---

Both parts of the code run fine if separated into two macros, by removing the If Then statements. However, I can’t get it to run as one macro. I get run-time error 75: Path/file access error message.

Any ideas on where I am going wrong? The code works fine if the folder doesn’t yet exist. I only get the error message if I am trying to show an existing folder. If I insert On Error Resume Next it doesn't do anything, but doesn't flash the run-time error.

Thanks in advance for any help you can give me.

Here is something similiar: http://www.vbforums.com/showthread.p...-list-in-excel
Check out the API in the last post. If the path exists, the function does nothing, if it doen't exist, it creates it. No need to check for existance beforehand

Hi Zvoni,

The MakeSureDirectoryPathExists function does seem useful. However, it only solves one half of the problem. I guess to complete the puzzle I need to know how to show a directory. I'll have a play mashing the MakeSureDirectoryPathExists with the second half of the code above and see how I get on.

Had a play around and can't get the function to work. I have created a test macro and just copy pasted the code from the link you gave me to your old post. It returns an error of "Only comments are available after Functions".

Did I mention I was new to this? :p

OK, figured it out. I could have sworn I tried this method but I guess not *shrug*

Code:

---

Sub OpenFolder()
Dim sPath As String

 'Define the directory that you are going to create and/or open 
sPath = "H:\My Documents\Prospects\" + Cells(ActiveCell.Row, 39).Value + "\" + Cells(ActiveCell.Row, 1).Value

'create the directory
On Error Resume Next
    MkDir sPath
    On Error GoTo 0

'Show the directory
retVal = Shell("explorer.exe " & sPath, vbNormalFocus)

End Sub

---