Passing a Variable to Create New Directory and Name New File

• • Reply

  13 years ago

  by DaveSC430

  DaveD

  , United States

  Joined 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

  Dave

  Report abuse
• • Reply

  13 years ago

  by sync_or_swim

  Rob Bickel

  S. Wales, United Kingdom

  Joined 15 years ago

  Dave,
  
  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
  ErrorHandler:
  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"
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  13 years ago

  by Akhtar Hussain

  Akhtar

  Mumbai, India

  Joined 15 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

  Akhtar

  Report abuse
• • Reply

  13 years ago

  by DaveSC430

  DaveD

  , United States

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

  Report abuse