Creating Folders from Excel Using OLE Automation

  • Thread starter Thread starter Bob C.
  • Start date Start date
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?
 
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 *
 
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.
 
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
 
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
 
Back
Top