directory creating based on form values, filecopy

[brichard]

Hello All

This is my first post on this forum.
I have found some very useful information here to complete this task, but being a noob to VBA, can't put the pieces together.
(Sorry for the wording, English is only my third language.)

Your help is much appreciated.

_What I would like to do_

Based on values of a form create a directory and copy renamed files there, then open a folder and open a file.

*form*
value1
value2
value3

*create directory*
x:\directories\value1\value2\value3

If it exists, stop and give error message.

*create subdirectories*
x:\directories\value1\value2\value3\dir1
x:\directories\value1\value2\value3\dir2

*copy files*
x:\file_location\filename1.ext to x:\directories\value1\value2\value3\renamed_filename1.ext
x:\file_location\filename2.ext to x:\directories\value1\value2\value3\renamed_filename2.ext

*open folder*
x:\directories\value1\value2\value3

*open_file*
x:\directories\value1\value2\value3\renamed_filename1.ext


_What I have so far_

-for creating the directory value1:
Private Sub Command337_Click()
strPath = "X:\directories" & "\" & value1
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

-for creating the directory value2:
Private Sub Command337_Click()
strPath = "X:\directories" & "\" & value1 & value2
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

-for creating the directory value3:
Private Sub Command337_Click()
strPath = "X:\directories" & "\" & value1 & value2 & value3
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

FileCopy "x:\file_location\filename1.ext" ????
-Stuck here

-open folder
Shell "explorer.exe " & strPath, vbNormalFocus

-open file
-Stuck here


Thank you

 

[apr pillai]

strPath = "X:\directories" & "\" & value1 & value2 & value3

Correct path spec.:

strPath = "X:\directories" & "\" & value1 & "\" & value2 & "\" & value3

FileCopy "x:\file_location\filename1.ext" ????

Correct Syntax:

FileCopy "x:\file_location\filename1.ext",  "y:\file_location\filename1.ext"

 

[brichard]

the issue is, that the destination file path is "dynamic", created by the macro
the directory creation is working fine
i have issues with the error message box and the file paths
thank you

 

[sparks80]

Hi,

If you are trying to manipulate files and directories it is well worth learning to use the FileSystemObject. This will allow you to create, rename, copy, move and delete both files and folders.

You will first need to set a reference to the Scripting Runtime library. From the visual basic editor go to the Tools menu and select Reference (Tools > Reference).

Then scroll down the list and select "Microsoft Scripting Runtime".

Here is the code I would write to carry out what you have requested:

Sub FileOperations()
  Dim fso as New FileSystemObject
  Dim fo As Scripting.Folder
  Dim fi As Scripting.File
  Dim strPath As String
  Dim strSubPath As String
  Dim I as Integer
  strPath = "x:\directories\" & value1 & "\" & value2 & "\" & value3

  ' Create main folder
  If fso.FolderExists(strPath) Then
    MsgBox "Sorry the following folder already exists:" & vbCrLf & _
      strPath
  Else
    fso.CreateFolder strPath
  End If
 
  ' Create Subfolders
  For I = 1 To 2
    strSubPath = strPath & "\Dir" & I
    If fso.FolderExists(strSubPath) Then
      MsgBox "Sorry, subdirectory " & I & " already exists"
    Else
      fso.CreateFolder strSubPath
    End If
  Next

  strOldFilePath = "x:\file_location\filename1.ext"
  strNewFilePath = "x:\directories\value1\value2\value3\renamed_filena  me1.ext"

  If not fso.FileExists(strOldFilePath) Then
    Msgbox "File not found"
  Else
    Set fi = fso.GetFile(strOldFilePath)
    fi.Move strNewFilePath
  End If

End Sub

Hopefully this should get you started on the file manipulation.

I would suggest checking this URL on how to open files from VBA:

http://www.erlandsendata.no/english/index.php?d=envbafoldersfileopen

 

[brichard]

GREAT

I am still left with the following issues:

1
strNewFilePath = "x:\directories\value1\value2\value3\renamed_filena me1.ext"
the new file path need to be the directory created by fso.CreateFolder strPath

2
I need to copy the files instead of moving them, two files.

3
Here
For I = 1 To 2
strSubPath = strPath & "\Dir" & I
Sorry, I have made a mistake naming it dir1 and dir2, but their names are not related, they are directoryabc and directorydef.

4
How can I run this script from a button (click)?

I would dig into this, but this seems to be a one time task and hoping that you can save me the time.

I am more like a business person, not a coder and do understand that I won't be able to write a spot-on code without investing time what wouldn't be in balance with the task.
That's why I am asking for your help.

Thank you very much

 

[sparks80]

Hi,

1) Ok, you can get the new file path like this:
strNewFilePath = strPath & "renamed_filena me1.ext"

2) To copy instead of move use the following:

set fi = fso.GetFile(strOldFilePath)
fi.Copy strNewFilePath

3) I used the For loop to simplify the code a bit. You can just do this long-hand instead:

    strSubPath = strPath & "\directoryabc"
    If fso.FolderExists(strSubPath) Then
      MsgBox "Sorry, subdirectory " & I & " already exists"
    Else
      fso.CreateFolder strSubPath
    End If

    strSubPath = strPath & "\directorydef"
    If fso.FolderExists(strSubPath) Then
      MsgBox "Sorry, subdirectory " & I & " already exists"
    Else
      fso.CreateFolder strSubPath
    End If

4) To run the code from a form you will need to add a command button, and use the On Click event. For example if you add a button called "cmdManipulateFiles" then the code would be:

Private Sub cmdManipulateFiles_Click()
  FileOperations
End Sub

 

[brichard]

Thank you for your reply
It looks perfect, but when trying to run the code I am getting the following error for line Dim fso as New FileSystemObject

Compile error: User-defined type not defined

Option Compare Database

Sub FileOperations()
  Dim fso As New FileSystemObject
  Dim fo As Scripting.Folder
  Dim fi As Scripting.File
  Dim strPath As String
  Dim strSubPath As String
  Dim I As Integer
  strPath = "x:\..." & ... & "\" & ...

  ' Create main folder
  If fso.FolderExists(strPath) Then
    MsgBox "Sorry the following folder already exists:" & vbCrLf & _
      strPath
  Else
    fso.CreateFolder strPath
  End If
 
    strSubPath = strPath & "\..."
    If fso.FolderExists(strSubPath) Then
      MsgBox "Sorry, subdirectory " & I & " already exists"
    Else
      fso.CreateFolder strSubPath
    End If

    strSubPath = strPath & "\..."
    If fso.FolderExists(strSubPath) Then
      MsgBox "Sorry, subdirectory " & I & " already exists"
    Else
      fso.CreateFolder strSubPath
    End If
    
  Next

  
  strOldFilePath = "x:\....txt"
  strNewFilePath = strPath & "....txt"

  If Not fso.FileExists(strOldFilePath) Then
    MsgBox "File not found"
  Else
    Set fi = fso.GetFile(strOldFilePath)
    fi.Copy strNewFilePath
  End If
  

End Sub


Private Sub Command145_Click()
FileOperations
End Sub

 

[sparks80]

That is because you need to reference a file called scrrun.dll to get this to work.

Follow these instructions:

1) Press ALT+F11 to open the visual basic editor
2) Go to the Tools Menu and select References
3) Scroll down the list and check the box next to "Microsoft Scripting Runtime"

Hopefully you should now be able to use the FileSystemObject.

 

[brichard]

Now it's running, but it seems that it's only able to create one level of directory.
If trying to create
X:\directories\value1\value2
getting the error message: Runtime Error 76 Path Not Found
If value2 is empty, it's creating
X:\directories\value1
and copying the files there, that part is perfect

Maybe the directories need to be built level-by-level?


And one more question. I am running this from a form, where value1 is stored as number but displayed as text (combo box, bound table).
Is it possible to use not the stored but the displayed values of the combo box to create the directories?

*form*
value1 - stored: 1 displayed: aaa (combo box)
value2 - stored: 2 displayed: bbb (combo box)

*bound table* Row source
comboboxkey - 1, 2
comboboxvalue - aaa, bbb

*directory need to be created*
x:\directories\aaa\2

^one is a displayed value, one is a stored value

 

[sparks80]

You are correct, you need to build the directories level by level if they do not exist. Rather than starting from scratch I suggest you use the function written by Chip Pearson on his site here:

http://www.cpearson.com/Excel/MakeDirMulti.aspx

Copy the code from his site into your module, and instead of using

fso.CreateFolder YourFolderPath

use

  Dim Result As EMakeDirStatus
  Result = MakeMultiStepDirectory("YourDirectoryPath")
  If Not Result = ErrSuccess Then
    MsgBox "Folder not created..."
  End If

The combobox is made up of multiple columns, which are numbered from 0 to n-1, where n is the number of columns.
The value of the combobox is determined by the "bound column" property. By default this will be the first column, in your case "1" and "aaa".
If you want to get the value from a different column then use the column

    ' This will return the value in the "bound column"
    cboBackstage.Value
    ' This will return the value in the second column
    cboBackstage.Column(1).Value

 

[brichard]

Thank you very much for your help, everything is working perfect.

 

[brichard]

How can I close a thread/mark it solved?

 

[sparks80]

That's my pleasure, glad you got it working. Not sure about closing a thread though!

 

[brichard]

Do you have an idea, how could I include value1 in the renamed file name of the copied file?

If I am trying to include value1 in strNewFilePath it won't accept "\" and if leaving out the "\" the renamed file is landing one directory above.

*copy files*
x:\file_location\filename1.ext to x:\directories\value1\value1_filename1.ext

strNewFilePath = strPath & "\filename1.ext"
^is copying well

strNewFilePath = strPath & "\" & value1 & "_filename1.ext"
^not possible

I was trying to add/modify the following
Dim strNewfn As String
strNewfn = value1 & "_filename1.ext"
strNewFilePath = strPath & strNewfn

But it's a mess

 

[sparks80]

Firstly make sure that the directory you are copying the file to exists. In your previous post this was x:\directories\value1\

Assuming this directory exists you should then be able to copy the file into that directory with any valid filename you like. This means you are able to include value1 in the filename as long as it does not include any invalid characters.

Example:

strOldPath = "x:\file_location\filename1.ext"
strNewPath = "x:\directories\" & value1 & "\" & value1 & "_filename1.ext"
set fi = fso.GetFile(strOldPath)
fi.copy strNewPath

If you are still having difficulties can you post the code and I'll take a look.

 

[brichard]

I had typos/wrong format, your version is working!
Thank you again, it was a big help.