Creating a folder on SaveAs
 

Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward file,
however when the user saves a new file (eg. a new store has opened) I need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

Hi,

I think your saying that

D:\Store Returns\Contacts

already exists so this will create the directory you want

MkDir "D:\Store Returns\Contacts\StoreNo"

Create it and then do your save.

Mike

"leerem" wrote:

Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward file,
however when the user saves a new file (eg. a new store has opened) I need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

Hi

I would always check for the existence of the folder first with a call to a
function as below.
...
...
foldername = "D:\Store Returns\Contacts\StoreNo\"
If Not DirExist(FolderName) Then
MkDir FolderName
End If
...
...

Function DirExist(FolderName) As Boolean
On Error Resume Next
ChDir (FolderName)
If Err < 0 Then
DirExist = False
Else
DirExist = True
End If
On Error GoTo 0
End Function
--
Regards
Roger Govier

"leerem" wrote in message
...
Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward
file,
however when the user saves a new file (eg. a new store has opened) I
need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and
FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

Hi Mike, that seems so simple MkDir..

How could I adjust this so that it only performs this task if the file
doesn't exist or does it not matter. i wouldn't want it to keep creating
several file with the same name.

"Mike H" wrote:

Hi,

I think your saying that

D:\Store Returns\Contacts

already exists so this will create the directory you want

MkDir "D:\Store Returns\Contacts\StoreNo"

Create it and then do your save.

Mike

"leerem" wrote:

Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward file,
however when the user saves a new file (eg. a new store has opened) I need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

Like this

If Len(Dir("C:\Store Returns\Contacts\StoreNo", vbDirectory)) 0 Then
MsgBox "Exists"
Else
MsgBox "Not There"
'do things
End If

Mike

"leerem" wrote:

Hi Mike, that seems so simple MkDir..

How could I adjust this so that it only performs this task if the file
doesn't exist or does it not matter. i wouldn't want it to keep creating
several file with the same name.

"Mike H" wrote:

Hi,

I think your saying that

D:\Store Returns\Contacts

already exists so this will create the directory you want

MkDir "D:\Store Returns\Contacts\StoreNo"

Create it and then do your save.

Mike

"leerem" wrote:

Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward file,
however when the user saves a new file (eg. a new store has opened) I need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

many thanks Mike

Regards

Lee

"Roger Govier" wrote:

Hi

I would always check for the existence of the folder first with a call to a
function as below.
...
...
foldername = "D:\Store Returns\Contacts\StoreNo\"
If Not DirExist(FolderName) Then
MkDir FolderName
End If
...
...

Function DirExist(FolderName) As Boolean
On Error Resume Next
ChDir (FolderName)
If Err < 0 Then
DirExist = False
Else
DirExist = True
End If
On Error GoTo 0
End Function
--
Regards
Roger Govier

"leerem" wrote in message
...
Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward
file,
however when the user saves a new file (eg. a new store has opened) I
need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and
FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

Creating a folder on SaveAs
 

One way is to ignore any error when you create the folder:

on error resume next
mkdir "D:\store returns"
mkdir "D:\Store Returns\Contacts"
mkdir "D:\Store Returns\Contacts\StoreNo"
on error goto 0

Here's something that Jim Rech Posted:

Option Explicit
Declare Function MakePath Lib "imagehlp.dll" Alias _
"MakeSureDirectoryPathExists" (ByVal lpPath As String) As Long

Sub Test()
MakeDir "c:\aaa\bbb"
End Sub

Sub MakeDir(DirPath As String)
If Right(DirPath, 1) < "\" Then DirPath = DirPath & "\"
MakePath DirPath
End Sub

leerem wrote:

Hi All, Your assistance is required.

How would I progmatically save an excel file to a folder that doesn't yet
exist. I'm writing an attachment to a code that saves a straightforward file,
however when the user saves a new file (eg. a new store has opened) I need
to identify that store by their Store No. with the related files stored
within.

So I need to save the file as for example:

ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Contacts\StoreNo\" &
FileNameSave, FileFormat:=xlNormal

With StoreNo being the New Folder that needs to be created and FileNameSave
as the File Name.

In short how do you create a folder with code.

Any assistance would be much appreciated.

Regards
Lee

--

Dave Peterson