Sub CopyOverWriteFile2ManyFolders()
'Tools > References > Microsoft Scripting Runtime > OK
‘This code Copy existing folder’s existing file in many folders you can also decide that if target folder
‘not exist code can make that folder & copy you file also on that folder
‘THIS PROCEDGURE USE 3 ASPECT OF CODDING
(1) Fso (File System Object)-‘core of this procedure
(2) Array
(3) Shell Commands (to work with Dos commands to create folder/file)
Sub CopyCreateOverWriteFile2ManyFolders ()
Dim fso As New FileSystemObject
Dim F As Object
Dim p As String
Dim fn As String,
Dim a as variant ‘– A Variable for array , named a , should be variable type as variant
Dim e as variant ‘- e variable for element of array, for file , -should by variable type as variant
‘++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
With fso
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
‘(1) set path to existing folder in which , your existing file reside which you want to copy
p = "E:\BUYER MASTER\" & "\" 'Set as you like.
If Not .FolderExists(p) Then Exit Sub ‘pending for understand what is use of this statement
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'(2) An array of target folders (to copy or create new folder named in array ), change to suit.-
‘Please Note:- If folders you mentioned in below array , if not exist in your path & if Shell command ‘(below )is uncommented, then that folder (not exist anywhere (on path “p” above ) create by shell ‘command & copy your file in that folders also,
‘ if shell command commented, then copy your file only in just existing folder in array without make ‘any folder mentioned in array, if any folder mentioned in array , not existing anywhere in path (p) ‘nothing do code exit from sub
‘you can decide this.. what you want to do …base on shell command commented or uncommented
' a = Array(p & "1 1\", p & "2 2\", "C:\Users\admin\Desktop\")-Original code
a = Array(p & "myfold1 \", p & "myfold2 \", "C:\Users\admin\Desktop\myfol3\")- ‘modify as your desire
‘++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'(3) Below code is for work on file ..if you want to create new file , if file name not exist in folder
‘ Then remove comments on below code, OR if you do not want to create new file if file not exist
‘ in folder then comment this file creation code.
'Make a test file for the example. Set fn to suit.
fn = p & "test.txt" '-ORIGINAL CODE (variable p used for path)
‘THERE ARE TWO TYPS OF CODE STYLE YOU CAN USE TO CREATE FILE WITH HELP OF FSO
‘please focus on first starting dot (“.”) that reference to its parent - FSO
‘(1)
' .CreateTextFile(fn, True).WriteLine ("Hello World!") '-original code For create new file you can ‘comment this code if you wish to create new file with below mentioned 2nd style
‘OR (2)
'Set f = .CreateTextFile(fn, True) ‘-ORIGINAL CODE to create file through FSO,
‘note that variable f assigned value now as whole process of
‘ new file object creation under FSO & after create file by FSO
‘Now you can give reference of this object named f to do more
‘on this file object
'f.WriteLine ("Hello World!") ‘ what you want to write in your newly created file
'f.Close’ file close after write something there
If Not .FileExists(fn) Then Exit Sub ‘pending for understand what is use of this statement
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'(4) Code for create folder & create file in arrayed path
‘If folder name not found in array then below code create new folder in that path , based on above ‘conditions…
'Iterate folders and copy fn with overwrite. (without alert message of Overwrite from excel)
For Each e In a ‘ Each element e (file) of array named a,
'Make folder(s) if needed. Handles more than one subfolder.
' Shell "cmd /c md " & """" & e & """", vbHide 'ORIGINAL CODE-Command prompt triggered by
‘shell command to create folder first & then create new file
‘there
If Not .FolderExists(e) Then GoTo NextE ‘ this is optional & pending for understand…need help What to do here & what is relation to below code?
‘++++++++++++++++++++++++++++++++++++++++++++++++++++++++’
‘(5) if you want to just copy & overwrite your existing file then below is for you
.CopyFile fn, e & .GetFile(fn).Name, True
NextE:
Next e
End With
Set fso = Nothing ‘here ,end FSO’s work & gracefully dissolve this variable in universe
End Sub
