Copy files from a folder to another, rename it then delete from access vba

I am sure this is easy, I have bits and pieces, but I need the middle part.
I have a file that arrives in morning to location 1 on the network named the following:

MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx

1) I want to copy from location FeedPath >> DONE, See Code

2) rename it (removing the date),    >> NEED HELP WITH THIS STEP <<

3) Copy to location CopyToPath (automatically overrides file from day before).  >> DONE, See Code
4) Run a macro in my db >>DONE
5)delete files from location FeedPath.  >>DONE, ALREADY HAVE CODE, SEE SECOND FUNCTION DeleteFeedFiles()


Here is my code My macro is already set up to run the functions in order, I just need to know how to rename the file not sure of the order:

Copy Files from and to code:
Function CopyRADFeedFiles()

Dim FSO As Object
Dim FeedPath As String
Dim CopyToPath As String
Dim FileExt As String

FeedPath = "\\usporamfs01\share\RAD\Master_Data\Latest_Feed"
CopyToPath = "\\usporamfs01\share\RAD\Master_Data"

FileExt = "*.xlsx"

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FeedPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Function
    End If

    If FSO.FolderExists(CopyToPath) = False Then
        MsgBox CopyToPath & " doesn't exist"
        Exit Function
    End If

    FSO.CopyFile Source:=FeedPath & FileExt, Destination:=CopyToPath
    'MsgBox "You can find the files from " & FromPath & " in " & ToPath

End Function

Open in new window


Delete Files Code
Function DeleteFeedFiles()

  On Error GoTo errorHandler
    Dim FSO As Object
    Dim FeedPathName As String, ToPath As String, FileExt As String
    
    FeedPathName = "\\usporamfs01\share\RAD\Master_Data\Latest_Feed"

  Dim fileName As String
  If Len(FeedPathName) > 0 Then
    If Right(FeedPathName, 1) <> "\" Then FeedPathName = FeedPathName & "\"
  End If
  fileName = Dir(FeedPathName & "*.xlsx")

  While Len(fileName) > 0
    Kill FeedPathName & fileName
    fileName = Dir()
  Wend

  Exit Function
errorHandler:
  If Err.Number = 70 Then
    Select Case MsgBox("Could not delete " & fileName & ". Permission denied. File may be open by another user or otherwise locked.", vbAbortRetryIgnore, "Unable to Delete File")
      Case vbAbort:
        Exit Function
      Case vbIgnore:
        Resume Next
      Case vbRetry:
        Resume
    End Select
  Else
    MsgBox "Error deleting file " & fileName & ".", vbOKOnly Or vbCritical, "Error Deleting File"
  End If
End Function

Open in new window

gracie1972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Instead of
FSO.CopyFile Source:=FeedPath & FileExt, Destination:=CopyToPath
You use .Move
FSO.Move Source:=FeedPath & FileExt, Destination:=CopyToPath

That may obviate the need for the delete, too!
0
gracie1972Author Commented:
Okay, what about renaming the file?

Arrives as MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx, I need to remove the date and only have MSTR_RMA_DATAFEED.xlsx
0
Nick67Commented:
.Move is copy-and-rename in one step
You can also, unintuitively, use it as rename
FSO.Move "C:\temp\somefile.xls" c:\temp\NewName.xls"
basically renames somefile.xls to NewName.xls
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gracie1972Author Commented:
The business does not want to move them, only copy.  Then delete from the copied location after I run the macro so that will not work.  What is the best way to rename the file after I copy it?
0
gracie1972Author Commented:
This throws and error:

FSO.CopyFile  "\\usporamfs01\share\RAD\Master_Data\Latest_Feed\*.xlsx"  "\\usporamfs01\share\RAD\Master_Data\Current Wholesale RMA Range.xlsx"

Open in new window

0
Nick67Commented:
LOL :)
Pat-head, rub-belly.
If that's what they want -- it's completely redundant, but ok
Required
FSO.CopyFile "\\someshare\somefile.xlsx" "c:\temp\somefile.xlsx" 'copy the file
FSO.Move "C:\temp\somefile.xls" c:\temp\NewName.xls" 'rename the file
FSO.Deletefile "\\someshare\somefile.xlsx", true 'delete the old file
'End result c:\temp\somefile.xlsx exists and \\someshare\somefile.xlsx doesn't


Not permitted
FSO.Move "\\someshare\somefile.xlsx" c:\temp\NewName.xls"
'exact same end result c:\temp\somefile.xlsx exists and \\someshare\somefile.xlsx doesn't

FSO.Move is the method used to rename files by 'moving' them within the same folder to a new name
0
Nick67Commented:
This throws an error:
FSO.CopyFile  "\\usporamfs01\share\RAD\Master_Data\Latest_Feed\*.xlsx"  "\\usporamfs01\share\RAD\Master_Data\Current Wholesale RMA Range.xlsx"


Yes, I expect it would
"\\usporamfs01\share\RAD\Master_Data\Latest_Feed\*.xlsx" in practice has the possibility of being many files
"\\usporamfs01\share\RAD\Master_Data\Current Wholesale RMA Range.xlsx" can only be one file
FSO sees the possible discrepancy and throws an error.

If you have wildcards in the source, the destination MUST be a folder
0
gracie1972Author Commented:
I am getting expected end of statement:

FSO.Move "\\usporamfs01\share\RAD\Master_Data\*.xlsx" \\usporamfs01\share\RAD\Master_Data\*.xlsx"
FSO.Move "\\usporamfs01\share\RAD\Master_Data\*.xlsx" "\\usporamfs01\share\RAD\Master_Data\*.xlsx"

Tried both ways, am I missing something?
0
gracie1972Author Commented:
Okay, the file name changes every day as it has a date in it, would I just drop the file name entirely? Problem is we are copying multiple files from multiple locations to this folder:

Renaming might not work:

File names copied are the following:
MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx

Needs to be renamed to:
MSTR_RMA_DATAFEED.xlsx

File names copied are the following:
Current Wholesale RMA Range2014-12-10 15.02.12.303.xlsx

Needs to be renamed to:
Current Wholesale RMA Range.xlsx
0
Rey Obrero (Capricorn1)Commented:
you can use

Dim strName As String, newName As String, strFolder As String
strFolder = CurrentProject.Path

strName = "MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx"
newName = Left(strName, InStr(strName, "20") - 1) & ".xlsx"

Name strFolder & "\" & strName As strFolder & "\" & newName
0
Nick67Commented:
The second one is the correct syntax
FSO.Move "somepath" "anotherPath"

But am I missing something?
Both paths appear identical
FSO.Move "\\usporamfs01\share\RAD\Master_Data\*.xlsx" "\\usporamfs01\share\RAD\Master_Data\*.xlsx"

"\\usporamfs01\share\RAD\Master_Data\*.xlsx"
"\\usporamfs01\share\RAD\Master_Data\*.xlsx"

That won't work!
0
gracie1972Author Commented:
@Rey Obrero


Issue # 1
strName = "MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx"

The date in the file (2014-12-10 15.02.12.303) changes each day, can I add a *?

"you can use

Dim strName As String, newName As String, strFolder As String
strFolder = CurrentProject.Path

strName = "MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx"
newName = Left(strName, InStr(strName, "20") - 1) & ".xlsx"

Name strFolder & "\" & strName As strFolder & "\" & newName
"

I am confused where to place this in my code, I am not that versed in VB yet:

Function CopyRADFeedFiles()

Dim FSO As Object
Dim FeedPath As String
Dim CopyToPath As String
Dim FileExt As String
Dim strName As String
newName As String
strFolder As String

strFolder = CurrentProject.Path

FeedPath = "\\usporamfs01\share\RAD\Master_Data\Latest_Feed"
CopyToPath = "\\usporamfs01\share\RAD\Master_Data"

strName = "MSTR_RMA_DATAFEED20*.xlsx"
newName = Left(strName, InStr(strName, "20") - 1) & ".xlsx"


FileExt = "*.xlsx"

    If Right(FeedPath, 1) <> "\" Then
        FeedPath = FeedPath & "\"
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FeedPath) = False Then
        MsgBox FeedPath & " doesn't exist"
        Exit Function
    End If

    If FSO.FolderExists(CopyToPath) = False Then
        MsgBox CopyToPath & " doesn't exist"
        Exit Function
    End If

    FSO.CopyFile Source:=FeedPath & FileExt, Destination:=CopyToPath
    'MsgBox "You can find the files from " & FromPath & " in " & ToPath

End Function

Open in new window

0
Nick67Commented:
The date in the file (2014-12-10 15.02.12.303) changes each day, can I add a *?
Yes, you may -- but then you CANNOT specify ANYTHING about the name of the destination file, only its folder if you are using the filesystemobject to do the heavy lifting

Then I need to rename the files.
dim myfile as file
dim myfolder as folder
Set myfolder = fs.GetFolder("\\usporamfs01\share\RAD\Master_Data")
For Each myfile In myfolder.Files
    If myfile.Name Like "MSTR_RMA_DATAFEED2014*" Then
        myfile.Name = "MSTR_RMA_DATAFEED.xlsx"
    end if
next myfile
0
Rey Obrero (Capricorn1)Commented:
@gracie

looking at your original code,
where in your codes do you want to change the Name of the copied file?
0
gracie1972Author Commented:
The business unit has agreed for me to rename the file in the original directory then copy to another folder location.  Does this help?

File Name: MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx
File Location:  \\usporamfs01\share\RAD\Master_Data\Latest_Feed

dim myfile as file
dim myfolder as folder

These both are not working in Access VBA.
0
Nick67Commented:
It take it you are using the filesystemobject with Late Binding?
I set a reference to the Windows Script Host Object Model when I work with it
You can try
dim myfile as object
dim myfolder as object
if you don't want to set the reference
0
Rey Obrero (Capricorn1)Commented:
create a new sub

Sub RenameCopyFiles()
dim myfile as string, newFile as string
dim myfolder as string

 myfolder="\\usporamfs01\share\RAD\Master_Data\Latest_Feed\"

myfile=dir(myfolder & "*.xlsx")
while myfile <>""
       newName = Left(myfile, InStr(myfile, "20") - 1) & ".xlsx"
       Name myFolder & myfile As myFolder  & newName

       'code here to copy the fiel to another location
       'filecopy myFolder  & newName, destFolder & newName
      myfile=Dir
wend

end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
typo

Sub RenameCopyFiles()
dim myfile as string, newName as string
dim myfolder as string

 myfolder="\\usporamfs01\share\RAD\Master_Data\Latest_Feed\"

myfile=dir(myfolder & "*.xlsx")
while myfile <>""
       newName = Left(myfile, InStr(myfile, "20") - 1) & ".xlsx"
       Name myFolder & myfile As myFolder  & newName

       'code here to copy the fiel to another location
       'filecopy myFolder  & newName, destFolder & newName
      myfile=Dir
wend

end sub
0
gracie1972Author Commented:
Perfect, that was easy :)
0
gracie1972Author Commented:
@Rey Obrero

Quick Question, what if I have 2 data feeds with 2 different file names in the same location:
 
MSTR_RMA_DATAFEED2014-12-10 15.02.12.303.xlsx
Current Wholesale RMA Range-12-10 15.02.12.303.xlsx

Will the code get confused?
0
Nick67Commented:
Yes, it will get confused
myfile=dir(myfolder & "*.xlsx")
is very broad.

So refine it
myfile=dir(myfolder & "MSTR_RMA_DATAFEED*.xlsx")

or

myfile=dir(myfolder & "Current Wholesale RMA Range*.xlsx")
0
Rey Obrero (Capricorn1)Commented:
<Will the code get confused? > yes, it will raised an error when it reads the second file "Current Wholesale RMA Range-12-10 15.02.12.303.xlsx"

to handle that, you will need additional codes.
0
gracie1972Author Commented:
I figured out a work around and had the feed files into 2 separate folders.  
This fixes that, however, the
MSTR_RMA_DATAFEED-12-10 15.02.12.303.xlsx  
AND Current Wholesale RMA Range-12-10 15.02.12.303.xlsx

Those numbers -12-10 15.02.12.303 are a time stamp and might vary so the
While myfile <> ""
       newName = Left(myfile, InStr(myfile, "20") - 1) & ".xlsx"
       Name myfolder & myfile As myfolder & newName
      myfile = Dir

Could crap out if the number of spaces changes.  What is the best way around this whatif scenario?
0
Rey Obrero (Capricorn1)Commented:
if the format for this file are the same

MSTR_RMA_DATAFEED-12-10 15.02.12.303.xlsx  
AND Current Wholesale RMA Range-12-10 15.02.12.303.xlsx

use

newName = Left(myfile, InStr(myfile, "-") - 1) & ".xlsx"
0
gracie1972Author Commented:
Perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.