Creating a directory from within Excel VBA

  • Thread starter Thread starter Jo Gjessing
  • Start date Start date
J

Jo Gjessing

Hi all,

I'm creating a little VBA script within Ms Excel, wanting the script to
change directory before writing a file, using the ChangeFileOpenDirectory
method. It works fine. But what if the directory does not exist? Then I want
the script to create it. Can you please tell me how I make it do so?

Thank you very much in advance.

Jo
 
One way

Sub stantial()
On Error GoTo ErrNotExist
ChDir ("C:\Mydir")
Exit Sub
ErrNotExist:
MkDir "c:\Mydir"
End Sub

Mike
 
You could also try this

Sub selectfolder()
Dim objShell As Object, objFolder As Object

Set objShell = CreateObject("Shell.Application")
On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
If Not objFolder Is Nothing Then
Set oFolderItem = objFolder.Items.Item
MyPath = oFolderItem.Path
ChDir (MyPath)

End If

End Sub
 
Back
Top