Creating Folders from Excel Using OLE Automation
 

I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?

Creating Folders from Excel Using OLE Automation
 

Hi Bob,

There are a couple of ways to do this, but the simplest one is to use
the MkDir statement:

MkDir "C:\MyFolder"

Note that you can't create multiple folder levels al at once using this
method (e.g. C:\MyFolder1\MyFolder2). For that you need to use the Windows
API or FileSystemObject.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob C." wrote in message
...
I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?

Creating Folders from Excel Using OLE Automation
 

Rob;

I feel so silly. It never occurred to me to use the old
DOS command for creating a folder. It works fine. That
creates another problem, however. My tool saves multiple
documents to the same folder, if it exists. If I encode
the MkDir command, it may try to create the folder again
later after it has already been created. Is there any way
to determine if a folder exists before trying to create
it? Something that I could use in an If...Then statement
to check for the existence of the folder before creating
it.

Bob C.

-----Original Message-----
Hi Bob,

There are a couple of ways to do this, but the
simplest one is to use
the MkDir statement:

MkDir "C:\MyFolder"

Note that you can't create multiple folder levels al at
once using this
method (e.g. C:\MyFolder1\MyFolder2). For that you need
to use the Windows
API or FileSystemObject.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob C." wrote in
message
...
I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel
Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my
excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?


.

Creating Folders from Excel Using OLE Automation
 

Bob,

You can determine whether a directory exists with the following
code:

If Dir("C:\Temp2", vbDirectory) < "" Then
Debug.Print "Directory exists"
Else
Debug.Print "Directory does not exist"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob C." wrote in message
...
Rob;

I feel so silly. It never occurred to me to use the old
DOS command for creating a folder. It works fine. That
creates another problem, however. My tool saves multiple
documents to the same folder, if it exists. If I encode
the MkDir command, it may try to create the folder again
later after it has already been created. Is there any way
to determine if a folder exists before trying to create
it? Something that I could use in an If...Then statement
to check for the existence of the folder before creating
it.

Bob C.

-----Original Message-----
Hi Bob,

There are a couple of ways to do this, but the
simplest one is to use
the MkDir statement:

MkDir "C:\MyFolder"

Note that you can't create multiple folder levels al at
once using this
method (e.g. C:\MyFolder1\MyFolder2). For that you need
to use the Windows
API or FileSystemObject.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob C." wrote in
message
...
I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel
Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my
excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?


.

Creating Folders from Excel Using OLE Automation
 

Or you could just ignore the error:

on error resume next
MkDir "C:\MyFolder"
on error goto 0



"Bob C." wrote:

Rob;

I feel so silly. It never occurred to me to use the old
DOS command for creating a folder. It works fine. That
creates another problem, however. My tool saves multiple
documents to the same folder, if it exists. If I encode
the MkDir command, it may try to create the folder again
later after it has already been created. Is there any way
to determine if a folder exists before trying to create
it? Something that I could use in an If...Then statement
to check for the existence of the folder before creating
it.

Bob C.

-----Original Message-----
Hi Bob,

There are a couple of ways to do this, but the
simplest one is to use
the MkDir statement:

MkDir "C:\MyFolder"

Note that you can't create multiple folder levels al at
once using this
method (e.g. C:\MyFolder1\MyFolder2). For that you need
to use the Windows
API or FileSystemObject.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob C." wrote in
message
...
I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel
Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my
excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?


.


--

Dave Peterson

Creating Folders from Excel Using OLE Automation
 

And both of these examples would fail if you had a file with the name myFolder.

You could modify Chip's code to check:

Option Explicit
Sub testme()
If Dir("C:\Temp2", vbDirectory) < "" Then
If GetAttr("c:\temp2") And vbDirectory Then
Debug.Print "Directory exists"
Else
Debug.Print "File with that name exists"
End If
Else
Debug.Print "Directory does not exist"
End If
End Sub



Dave Peterson wrote:

Or you could just ignore the error:

on error resume next
MkDir "C:\MyFolder"
on error goto 0

"Bob C." wrote:

Rob;

I feel so silly. It never occurred to me to use the old
DOS command for creating a folder. It works fine. That
creates another problem, however. My tool saves multiple
documents to the same folder, if it exists. If I encode
the MkDir command, it may try to create the folder again
later after it has already been created. Is there any way
to determine if a folder exists before trying to create
it? Something that I could use in an If...Then statement
to check for the existence of the folder before creating
it.

Bob C.

-----Original Message-----
Hi Bob,

There are a couple of ways to do this, but the
simplest one is to use
the MkDir statement:

MkDir "C:\MyFolder"

Note that you can't create multiple folder levels al at
once using this
method (e.g. C:\MyFolder1\MyFolder2). For that you need
to use the Windows
API or FileSystemObject.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob C." wrote in
message
...
I have created a software tool that operates from a
Microsoft Excel platform using Visual Basic programming.
This tool creates reports from data in an Excel
Workbook.
Once the report is generated, I want to create a unique
folder on my server and save the document to the new
folder. Saving the document is no problem but how do I
create a new folder on the server to save the document
to? I want to encode this using visual basic in my
excel
module using unique data from the workbook to create the
name of the folder. Can anyone help solve this problem?


.


--

Dave Peterson


--

Dave Peterson