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