VBA code needed

[prashanthdivya]

Hi All,

Every day a folder is created with the currendate(ex:=05-jul-2013) as the folder name. I need a code which would automatically every day and every hour of that day to go into the folder and copy all those files into a target folder. Please help me with a vba codeEvery day a folder is created with the currendate(ex:=05-jul-2013) as the folder name. I need a code which would automatically every day and every hour of that day to go into the folder and copy all those files into a target folder. Please help me with a vba code



Thanks

 

[daverunt]

Hi,

there are probably a few ways of doing this. Here's one.
It uses VBA to create a batch file in the dated directory to XCopy the files to the Target Directory.
(The Bat file will overwrite)
There's a lot to set up.

You need to change the paths to those you will be using. Make sure to leave backslashes in place.

Sub CopyFilesToTargetFolder()

FileNumber = 1
[COLOR=#ff0000]FoldPath = "C:\junk\"

[/COLOR]TargetFolderPath = [COLOR=#ff0000]FoldPath[/COLOR] & "CopyTo\"

'Get the folder name with lower case month
DirectoryName = LCase(Format(Date, "dd-mmm-yyyy"))

ChDir FoldPath & DirectoryName

    CopyBatFile = FoldPath & DirectoryName & "\Copy.bat"
    CopyBatFilePath = FoldPath & DirectoryName & "\"
    CopyFileNme = "Copy.bat"

'Create XCopy batch file
    Open CopyBatFile For Output As #FileNumber
    Print #FileNumber, "Xcopy /y ""[COLOR=#ff0000]*.*[/COLOR]"" """ & TargetFolderPath & """"
    Close #FileNumber
     
'Run batch file - wait for path change
    ChDir (CopyBatFilePath)
    Application.Wait (Now + TimeValue("00:00:02"))
    retVal = Shell(CopyFileNme, vbNormalFocus)
      
'Check error.
    If retVal = 0 Then
        MsgBox "An Error Occured"
       Close #FileNumber
        End
    End If
    
'Wait for copy

    Application.Wait (Now + TimeValue("00:00:03"))
     
'Delete batch files
    Kill CopyBatFile

'As the bat file copies all files this includes the created bat file so we delete this from the target dir too.

 ChDir (TargetFolderPath)
    Kill CopyFileNme
    
'Open the Output Folder
    retVal = Shell("C:\WINDOWS\explorer.exe """ & TargetFolderPath & "", vbNormalFocus)
'Delete batch file
    
End Sub

Once you get this working then add the workbook open event.
Copy to 'ThisWorkbook in the Project Window.

Private Sub Workbook_Open()
  Call CopyFilesToTargetFolder
End Sub

Once that works you need create a BAT file <My_Macros.Bat> to be opened by the Task Scheduler.

The Bat file should contain start excel and the path to the file containing the above macro in:

start excel "C:\Documents and Settings\Fred Bloggs\My Documents\My Macros\My_Macros.xls"

Once you have done that then the final thing is to set up the Windows Task Sheduler.

I can get it to run every minute on my PC but haven't tested whether it will continue doing the same the next day. I am assuming it will work. If it doesn't you will have to look at the Application OnTime method.

Scheduler - set the path to the bat file created to start excel.

Run: "C:\Documents and Settings\Fred Bloggs\Desktop\My_Macros.Bat"
Start in: "C:\Documents and Settings\Fred Bloggs\Desktop\My_Macros.Bat"

To get it to run every hour you need to set it to run daily then go to advanced and repeat every hour over 24 hours.

You also need to put the Excel file containing the macro into a trusted location so that there is no prompt to enable macros when the file opens!

 

[prashanthdivya]

Hi Daverunt,

Thank you very very much for your help I tried the first part today ant it works wonder thank you very musch . I am left with the scheduleder part will try that and let u know.

Thank you very much again..