Solved

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

Posted on 2014-12-10
25
689 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
  • 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
 

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 119

Expert Comment

by:Rey Obrero
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now