Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: copying and moving files from one folder to another

    i hope some one can help

    I have a folder that is populated with around 400 files automatically overnight, what i need to do is frin all the files within the folder (H:\Retail Network\Private\4760files) that start with Branch_name (the files then have alot of different text after this like the time, server etc etc). I then need to copy all these files and move them to another location (H:\Retail Network\Private\Sorted4760Files)

    the problem i am having is i am unsure as to how i go about looking for the files that start with Branch_name

    hope this makes sense

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this an access problem? looks more like a vb script job
    arguably you could even do it as a good old dos job/batch file...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Should help!

    Hi,

    Below is some code I use to do something similar.
    If all of the files you want to copy begin w/the branch name you have to set up some type of loop that will put it into a variable and then place the variable into the dir string to grab that particular file.


    sServerName = "\\rtpdscel01dm06\tems_ops"
    sPathName = "\Central Expert Team\Data Quality\Source Data\StandardFeed"
    sFileDir = "\SourceFiles\"
    sLinkDir = "\LinkedTables\"
    sArchDir = "\Archive\"
    sExt = "*.txt"

    'source file GSK_order_events20080530.txt
    'linked file GSK_order_events.txt
    'archived file GSK_order_events20080530.txt

    sSourceFileName = Dir(sServerName & sPathName & sFileDir & sExt)
    iKount = 0

    Do While sSourceFileName <> ""
    iKount = iKount + 1
    iDotPos = InStr(sSourceFileName, ".")
    dFileDate = CDate(Mid(sSourceFileName, iDotPos - 4, 2) & "/" & Mid(sSourceFileName, iDotPos - 2, 2) & "/" & _
    Mid(sSourceFileName, iDotPos - 8, 4))
    'first time thru update table to show latest file date
    If iKount = 1 Then
    sUSQL = "update tblSourceFileDate set filedate = #" & dFileDate & "#, loaddate = #" & Format(Now(), "short date") & "#"
    DoCmd.RunSQL (sUSQL)
    End If
    sLinkFileName = Left(sSourceFileName, Len(sSourceFileName) - 12) & ".txt"
    'update stats table with current file/load info
    sUSQL = "update tblFileLoadStats set last_file_date = current_file_date,last_load_date = current_load_date, " & _
    "current_file_date = #" & dFileDate & "#, current_load_date = #" & Now() & "# where filename = '" & _
    Left(sLinkFileName, Len(sLinkFileName) - 4) & "'"
    DoCmd.RunSQL (sUSQL)
    'copy source file to link file
    FileCopy sServerName & sPathName & sFileDir & sSourceFileName, sServerName & sPathName & sLinkDir & sLinkFileName
    'copy source file to archive file
    ' FileCopy sServerName & sPathName & sFileDir & sSourceFileName, sServerName & sPathName & sArchDir & sSourceFileName
    'clean up source file, empty directory for next day's delivery
    Kill sServerName & sPathName & sFileDir & sSourceFileName
    sSourceFileName = Dir
    Loop

    Good luck!
    --If its free, take it for what its worth!

Posting Permissions

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