Creating Folders from Excel Using OLE Automation

B

Bob C.

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?
 
R

Rob Bovey

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 *
 
B

Bob C.

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.
 
C

Chip Pearson

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
 
D

Dave Peterson

Or you could just ignore the error:

on error resume next
MkDir "C:\MyFolder"
 
D

Dave Peterson

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
 
Top