?
Solved

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

Posted on 2014-12-10
25
Medium Priority
?
891 Views
Last Modified: 2014-12-10
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

0
Comment
Question by:gracie1972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 6
25 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40491941
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
 

Author Comment

by:gracie1972
ID: 40491965
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40491979
.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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gracie1972
ID: 40491993
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
 

Author Comment

by:gracie1972
ID: 40492015
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492019
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492030
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
 

Author Comment

by:gracie1972
ID: 40492031
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
 

Author Comment

by:gracie1972
ID: 40492033
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492056
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492058
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
 

Author Comment

by:gracie1972
ID: 40492067
@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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492089
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492100
@gracie

looking at your original code,
where in your codes do you want to change the Name of the copied file?
0
 

Author Comment

by:gracie1972
ID: 40492162
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492172
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40492184
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492207
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
 

Author Closing Comment

by:gracie1972
ID: 40492231
Perfect, that was easy :)
0
 

Author Comment

by:gracie1972
ID: 40492245
@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
 
LVL 26

Expert Comment

by:Nick67
ID: 40492294
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492303
<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
 

Author Comment

by:gracie1972
ID: 40492350
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492378
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
 

Author Comment

by:gracie1972
ID: 40492522
Perfect!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question