Passing a Variable to Create New Directory and Name New File

  • 13 years ago

    While working with VBA in Access, does anyone know how to use a variable (example:  Me!VariableName) to create a new Directory by adding it to the Application.CurrentProject.Path or literally naming it if necessary (like F:\CompanyData\VariableName), and then naming the file with an extension (like VariableName1.Doc) as a Word file???

    I need it to end up like F:\CompanyData\VariableName\VariableName1.Doc

    Much thanks for any help


  • 13 years ago


    Welcome to Developer Fusion!!  To create a directory you can use the following function to check if the directory already exists, if the directory doesn't exist an exception is thrown and the function returns false.

    Function DirExists(DirName As String) As Boolean
        On Error GoTo ErrorHandler
        DirExists = GetAttr(DirName) And vbDirectory
    End Function

    Heres an example of creating a new directory based upon the contents of a textbox:

    Private Sub Command1_Click()
    Dim strDir As String
    strDir = App.Path & "\" & Text1.Text
    If DirExists(Trim(strDir)) = False Then
        MkDir Trim(strDir)
    End If
    End Sub

    Once you have created your directory you can save your document as:
    strDir & "\" & VariableName1 & ".Doc"

  • 13 years ago

    Hi Dave

    you can also use "Microsoft Scripting Runtime" Reference then add two commad button on your form and change its caption "Create Folder", "Create File"

    Here is the test code

    Private Sub Command1_Click()
    'To create folder
    Dim fso As New FileSystemObject
    If Not fso.FolderExists("" & App.Path & "\Me!Variable") Then
        fso.CreateFolder (App.Path & "\Me!Variable")
    End If
    End Sub

    Private Sub Command2_Click()
    'To create file
    Open "C:\VariableName1.Doc" For Output As #1
        Print #1, "First Line"
        Print #1, "Second Line"
        Print #1, "Third Line"
    Close #1
    End Sub

    Remember first you have to add a reference to Microsoft Scripting Runtime

    hoping this may help you


  • 13 years ago

    That is fantastic - THANKS!  It worked like a charm. 

    But now I am faced with overwriting existing files.  I fought with LookIn Property and cannot seem to find a way simply to list the files in the Variable Directory or at least warn if the Variable.doc file exists in the directory.

    One more hand would be greatly appreciated.

    Thanks in advance.

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.”