.movefile

[SteveDB1]

Hello all.

Well, I'm a little closer than before, but still short of my goal.

We've found a macro that Ron DeBruin posted on his website last year and
have found that it works for our purposes, all but one item.

In his code, the FSO.MoveFile moves the entire directory's contents with the
specified file extension-- in this case, xl*.

Based on another poster's comments, and what I'd read in the help files,
msdn libraries, etc.... that MoveFile would move an open file. However, with
Ron's code this does not happen. I get a "permission denied" error. Which is
a Run time error # 70.

Which, if I understand correctly, occurs due to an attempt to move a file
which is open.

Our goal is to move only files that have thrown an error, and then return to
the processes we were running prior to finding the error.

This requires that the file name be a variable to store the name of the file
being operated on (by other macros in our routine).

It also appears to require emptying the file name once it is complete moving
that particular file.

And based on the 70 error, we'd need to close the file first- while
maintaining its name in the variable.

Thus far, the variables I've seen only maintain the file's name in its
memory as long as the file is pending an operation, and once that process is
complete, the file name being stored is deleted from memory.

So, if I understand this correctly, how would I accomplish the goals of:
1- making a variable that would maintain the file name in which an error
occurs, after closing the file.
2- moving only the file in which we found the particular error?

Here's Ron DeBRuin's macro.
----------------------------------------

Sub Move_Certain_Files_To_New_Folder()
'This example move all Excel files from FromPath to ToPath.
'Note: It will create the folder ToPath for you with a date-time stamp
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String
Dim FNames As String

FromPath = "C:\Users\Ron\Data" '<< Change
ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss") _
& " Excel Files" & "\" '<< Change only the destination folder

FileExt = "*.xl*" '<< Change
'You can use *.* for all files or *.doc for word files

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

FNames = Dir(FromPath & FileExt)
If Len(FNames) = 0 Then
MsgBox "No files in " & FromPath
Exit Sub
End If

Set FSO = CreateObject("scripting.filesystemobject")

FSO.CreateFolder (ToPath)

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

End Sub

 

[JLGWhiz]

You should be able to store the file name in a variable after you open the
file, then close the file and the variable should still have the file name.
You cannot, however, use the same scripting object variable that you
originally used to open the file, because it will go away when the file is
closed. I would suggest that when you store it that you use the full name
(include the path).

 

[SteveDB1]

Hi JLG,
Thanks for the response.
So instead of my FSO variable, I'd need to dim/use another scripting
variable to maintain that file name?
In which case, would it then be possible to just choose a new name for say,
FNames to retain the file name? Or since it's based on FNames, would it then
be emptied when I close the file?

 

[JLGWhiz]

I'm not sure how you are calling out the files but whatever you use to
represent the file collection in a directory, you could use the index number
to get he name, as an example:

myVarName = fs.Path & "\" & fNames(1).Name

Where if fNames is the files collection in the fs Object, the fNames(1)
would be the first file in that collection and the myVarName variable would
store that name, even after the fs Object is gone.