Results 1 to 6 of 6

Thread: RoboCopy Issue

  1. #1
    Join Date
    May 2015
    Posts
    7

    Unanswered: RoboCopy Issue

    Hey -

    Not sure if this is where this question should go or not, but going to give it a shot.

    So the issue I'm having is I have a text file that is dropped into a folder on a daily basis; each day the text file has todays date in the name, example: CCP2_Urgent_Inquiry_CMS_Rpt05_06_2015.txt. I have a macro within ms access that that runs daily and utilizes this file, which I have linked into my db. The way I currently do this is by moving the file into my C:/temp folder and removing the date portion of the name and having the text file linked into the db, example: CCP2_Urgent_Inquiry_CMS_Rpt.
    I was previously having to manually move the file to the temp folder but recently learned of robocopy and now have an task scheduled to auto pull this file into the C/:temp folder for me. However, my issue now is that I have to manually remove the date each morning in order to run the macro as the linked table within ms access will only recognize a linked file with the exact name as the link; same goes for importing a file.
    What I'm hoping someone can assist with is advising me on how to either A: create a scheduled task to change the file name within the temp folder to auto-drop the date portion of the file each morning or B: someone to advise me on how I can link or import a file each day that has a different date.

    I posted this similar question in the MS Access forum and received a few responses with links to sites but after reading through those I dont believe they will work either.
    I've done a good amount of google searching and believe the above A can be done though the xcopy command but I am not exactly savvy with xcopy as I honestly just found out it existed.

    I would appreciate any assistance in this matter.

    Thanks
    Dylan

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    In the macro, call a vb function
    Code:
    Function CopyMyFile(byval pvSrc)
    
    FileCopy pvSrc, "\\server\folder\file.txt"
    End function

  3. #3
    Join Date
    May 2015
    Posts
    7
    Quote Originally Posted by ranman256 View Post
    In the macro, call a vb function
    Code:
    Function CopyMyFile(byval pvSrc)
    
    FileCopy pvSrc, "\\server\folder\file.txt"
    End function

    Thanks for the VB; Just to clairfy am I creating a module for this? Also the "\\server\folder\file.txt" string; should that look something like this: "C:\temp\CCP2_Urgent_Inquiry.txt\" meaning the text file with the date already removed?
    Appreciate the assistance.

  4. #4
    Join Date
    May 2015
    Posts
    7

    New Thought

    Just had a thought; what about using task scheduler to run a Powershell task to update the name.

    I'm currently trying to utilize the below:

    Program/Script:
    Powershell.exe

    Argument:
    -noexit -command 'Q:\D453\F89783\SECURED\MIRR Inventory\Reports\Database\Daily Reporting Database\Daily Temp\CCP2_Urgent_Inquiry_CMS_Rptmm_dd_yyyy' -Filter "*.txt" -RecursRecurse Rename-Item -NewName {$_.name -replace 'mm_dd_yyyy','' }

    Having no luck... Any suggestions?

  5. #5
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Put my code in a module and it will do the copy.

  6. #6
    Join Date
    May 2015
    Posts
    7
    Hey everyone -

    I appreciate the assistance; I figured out a way to accomplish what I was needing by utilizing the Powershell Rename command to change my file name; along with task scheduler to run the Robocopy and Powershell. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •