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.
Function CopyMyFile(byval pvSrc)
FileCopy pvSrc, "\\server\folder\file.txt"
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.
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!