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

"Jo Gjessing" wrote:

> 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

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

"Mike H" wrote:

> One way
>
> Sub stantial()
> On Error GoTo ErrNotExist
> ChDir ("C:\Mydir")
> Exit Sub
> ErrNotExist:
> MkDir "c:\Mydir"
> End Sub
>
> Mike
>
> "Jo Gjessing" wrote:
>
> > 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