PDA

View Full Version : Solved: Move XLS-Files To Another Drive



Wolfgang
09-27-2006, 07:17 AM
Hi All,

Before you call me “Not Being Structured In Any Way, Shape Or Form”, I fully agree with you...

I have hundreds of Excel-files all over my system some of them being generated as early as Win98 and Excel97.
Now, sometimes I do use the oldies and save them in their ancient folders.

Is it possible to have some code, which looks through my drive C: for all files with the extension .xls and moves them to another drive F: ?!
By doing so, I will gradually clean all of my old stuff and have the latest-and-greatest on a USB-stick.

This will be far easier for me than having to scan all folders manually...
Thank you and have a nice day...
Best,
Wolfgang

Bob Phillips
09-27-2006, 07:33 AM
Why not just sort your directories, select all the Excel files, and drag-and-move them?

Wolfgang
09-27-2006, 07:37 AM
Hi Bob,

Thank you very much for your quick answer....

Actually, I was hoping for a "lazier" way of doing this...

I have an ancient DOS-batchfile which does this...just too much hassle...if you know what I mean...

Best,
Wolfgang

Bob Phillips
09-27-2006, 07:51 AM
Personally, I feel this is one instance where the manual approach is better.

I would also use it as an opportunity to do some housekeeping.

mvidas
09-27-2006, 08:08 AM
Wolfgang,

I agree with Bob that this is a good opportunity to clean them up.

If you still want to go through with it, why not use windows search for *.xls on the C:\, then move them to the F drive from there?

If you really want a vba method:Option Explicit
Sub WolfgangMove()
Dim FileList() As String, i As Long, DestDir As String
DestDir = "F:\"
ReDim FileList(1, 0)
vFileSearch "C:\foldername", FileList, "xls"
If Len(FileList(0, 0)) > 0 Then
For i = 0 To UBound(FileList, 2)
If IsFileInUse(FileList(0, i) & FileList(1, i)) Then
MsgBox FileList(0, i) & FileList(1, i) & " is in use. Skipping file."
Else
If Len(Dir(DestDir & FileList(1, i))) > 0 Then
MsgBox FileList(1, i) & " already exists in " & DestDir & vbCrLf & _
"Skipping file."
Else
Name FileList(0, i) & FileList(1, i) As DestDir & FileList(1, i)
End If
End If
Next
End If
End Sub
Function vFileSearch(ByVal vPath As String, ByRef vsArray() As String, _
Optional ByVal vExt As String = "*") As Boolean
Dim tempStr As String, vDirs() As String, Cnt As Long, dirCnt As Long
If Len(vsArray(0, 0)) = 0 Then
Cnt = 0
Else
Cnt = UBound(vsArray, 2) + 1
End If
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
On Error Resume Next 'in case no 'read' rights to directory
tempStr = Dir(vPath & "*.", vbDirectory)
On Error GoTo 0
dirCnt = 0
Do Until Len(tempStr) = 0
If Left(tempStr, 1) <> "." Then
ReDim Preserve vDirs(dirCnt)
vDirs(dirCnt) = tempStr
dirCnt = dirCnt + 1
End If
tempStr = Dir
Loop
On Error Resume Next 'in case no 'read' rights to directory
tempStr = Dir(vPath & "*." & vExt)
On Error GoTo 0
Do Until Len(tempStr) = 0
ReDim Preserve vsArray(1, Cnt)
vsArray(0, Cnt) = vPath
vsArray(1, Cnt) = tempStr
Cnt = Cnt + 1
tempStr = Dir
Loop
If dirCnt > 0 Then
For dirCnt = 0 To UBound(vDirs)
If Len(Dir(vPath & vDirs(dirCnt))) = 0 Then
vFileSearch vPath & vDirs(dirCnt), vsArray, vExt
End If
Next
End If
End Function
Function IsFileInUse(ByVal vPathFile As String) As Boolean
Dim vFF As Long
vFF = FreeFile()
On Error GoTo AlreadyOpened:
Open vPathFile For Binary Access Read Lock Read As #vFF
Close #vFF
Exit Function
AlreadyOpened:
IsFileInUse = True
End FunctionMatt

Wolfgang
09-27-2006, 08:24 AM
Hi Matt,

Thank you very much too, I wasn't too clear with question...

I have files in various sub-directories according to their tasks which means there are at least 30 subs...

Therefore what I wanted to achieve is that after time only those files will be left that I haven't addressed yet, whereas the other have been moved to the stick...

Best,
Wolfgang

Wolfgang
09-27-2006, 08:35 AM
Gentlemen...

It sure is high time for me to go back to Elementary School in order to learn how to express myself properly...apologies to you...

Start again: Of course, I only want to move those files which I have saved today or any date which then is "today"...

So at the end of that funny day I will be able to move all files that I have been playing with (and saved) to the stick...

Very sorry for the confusion...

Best,
Wolfgang

mvidas
09-27-2006, 08:36 AM
Hi Wolfgang,

I do have to admit that I am a bit confused over what you actually want then. The way I now interpret it, you have a couple options:
-Use windows search for *.xls, sort by Modified, move over files modified within a specific amount of time
-From now on when accessing an excel sheet, make a conscious effort to click "save as" instead of save, and save it to the F drive
-Use my code above, but replace the WolfgangMove sub with:Sub WolfgangMove()
Dim FileList() As String, i As Long, DestDir As String, FSO As Object
DestDir = "F:\"
ReDim FileList(1, 0)
vFileSearch "C:\foldername", FileList, "xls"
If Len(FileList(0, 0)) > 0 Then
Set FSO = CreateObject("scripting.filesystemobject")
For i = 0 To UBound(FileList, 2)
'check to see if last saved date is within last 90 days
If FSO.GetFile(FileList(0, i) & FileList(1, i)).DateLastModified > Date - 90 Then
If IsFileInUse(FileList(0, i) & FileList(1, i)) Then
MsgBox FileList(0, i) & FileList(1, i) & " is in use. Skipping file."
Else
If Len(Dir(DestDir & FileList(1, i))) > 0 Then
MsgBox FileList(1, i) & " already exists in " & DestDir & vbCrLf & _
"Skipping file."
Else
Name FileList(0, i) & FileList(1, i) As DestDir & FileList(1, i)
End If
End If
End If
Next
Set FSO = Nothing
End If
End Subto only move file modified within 90 days (change to suit).

I again say that I agree with Bob, this really sounds like something you would be better off to do manually, but I could be completely missing your intent.

Bob Phillips
09-27-2006, 09:12 AM
Hate to harp on, but if you only want to move files you save today, why not just save them onto the new area?

Wolfgang
09-27-2006, 09:45 AM
Great idea, Bob ...

If this action would also delete the "old"-file...

To extend my laziness some more, the best solution would be to start at the maindrive and automatically include all subdirectories as well...

mdmackillop
09-27-2006, 10:37 AM
This is something I put together for handling picture files, roughly modified to list Excel files. It doesn't move anything directly, but code can be added to accomplish this.

Wolfgang
09-27-2006, 11:37 AM
Hi Malcolm...

That looks really great...

Would it be possible for you to: "...but code can be added to accomplish this." do that?

Best,
Wolfgang

mvidas
09-27-2006, 12:14 PM
Wolfgang,

Did you try my code above? If you use the second WolfgangMove sub with the rest of the first code, it should do what you're looking for (though if you only want files modified in the last day, change "Date - 90" to just "Date - 1"). If it doesnt work the way you want it, I understand, but I think with the timing of the posts it may have been skipped.

Matt

mdmackillop
09-27-2006, 12:36 PM
Hi Wolfgang,
Can you explain how you would want it to function?

Wolfgang
09-29-2006, 12:41 AM
Gentlemen...

apologies for coming back to you so late, but for some reason I did not get any e-mail notification...

Matt, your proggy runs just fine with the exception that it doesn't look through all of the subdirectories as well...
On drive C: I have plenty of subs which I created according to the various subjects...

Malcolm, your proggy runs just fine too, but it only picks up those subs that start with an "A" and forgets about the others...
It does list all of my files and if I could simply select the ones which I like to be moved to another drive would be great...

I don't want you to spend too much time on this, please, because Matt's solution already does what I expected...

So, Matt, if you would include that function into your solution I am very happy and greatful...

Again, thank you both for your time...

Best,
Wolfgang

mvidas
09-29-2006, 05:55 AM
Matt, your proggy runs just fine with the exception that it doesn't look through all of the subdirectories as well...
It should! The only reason it would not be able to look at some subdirectories is if you don't have Read access. If you could be referring to Hidden/System/ReadOnly, you can add that to the Dir statements, like: tempStr = Dir(vPath & "*." & vExt, vbHidden + vbSystem + vbReadOnly)If you're still not getting it to work the way you want, can you give me an example of a path/filename that exists that isn't being picked up by this?
Matt

Wolfgang
09-29-2006, 08:16 AM
Matt,

I just don't know whta to say...
I copied your macro to another machine and it ran the way you described...
Then I copied it back onto my work machine and....it runs there also...

"It' A Kind Of Magic" as Freddie Mercury would have called it...

Anyway...this is exactly what I wanted and I am really surprised by the amount of files a have...most of them originated from this very excellent forum...

Leaves me to thank you very much again and to wish you a very pleasant weekend...or "whatever floats your boat"...

Best,
Wolfgang

mvidas
10-02-2006, 05:25 AM
Hi Matt,

I showed your fine macro to a friend yesterday and she is simply speechless about its functionality...

Now, she asked me if I could get in touch with you and would would you please be so kind and adapt your macro such, that it does NOT move but only COPY those files to another drive...

I hate do ask you via the "official channel" since the subject is already closed...

Thank you very much in advance and have a nice weekend still...

All the best,
Wolfgang
Sorry I didnt get this until today, never got the PM notification.
You don't have to worry about adding it on here; it is a related question and you could be answering it for someone wanting to do the same thing in a year from now.

In the second "WolfgangMove" sub I posted, change this line:Name FileList(0, i) & FileList(1, i) As DestDir & FileList(1, i)To:FileCopy FileList(0, i) & FileList(1, i), DestDir & FileList(1, i)The "Name" statement is used to rename a file (used here to 'rename' the parent directory), and the FileCopy is used to make a copy :)
The downfall I see to using copy is that what if you open/edit "FileA.xls", save it to your F drive, and then a month later forget you did that. So after being modified, you try and copy FileA to the F drive and the file already existed, and the macro won't let you.
Isn't your goal to move the files off the C drive?
In any case, if you also want it to prompt you to overwrite existing files, use this as your WolfgangMove:Sub WolfgangMove()
Dim FileList() As String, i As Long, DestDir As String, FSO As Object, CopyIt As Boolean
DestDir = "F:\"
ReDim FileList(1, 0)
vFileSearch "C:\foldername", FileList, "xls"
If Len(FileList(0, 0)) > 0 Then
Set FSO = CreateObject("scripting.filesystemobject")
For i = 0 To UBound(FileList, 2)
'check to see if last saved date is within last 90 days
If FSO.GetFile(FileList(0, i) & FileList(1, i)).DateLastModified > Date - 90 Then
If IsFileInUse(FileList(0, i) & FileList(1, i)) Then
MsgBox FileList(0, i) & FileList(1, i) & " is in use. Skipping file."
Else
CopyIt = True
If Len(Dir(DestDir & FileList(1, i))) > 0 Then
If MsgBox(FileList(0, i) & FileList(1, i) & vbCrLf & _
"This file already exists in " & DestDir & vbCrLf & _
"Do you want to overwrite it?", vbYesNo) = vbNo Then CopyIt = False
End If
If CopyIt Then
FileCopy FileList(0, i) & FileList(1, i), DestDir & FileList(1, i)
End If
End If
End If
Next
Set FSO = Nothing
End If
End SubMatt

Wolfgang
10-02-2006, 05:33 AM
Hi Matt...

many thanks for your quick answer...

I am totally fine with the way your program works but when I showed to my friend then she wanted to have that option...

Now, I will send your code to her and maybe after some time, she will start using the MOVE-option as well...

Have a nice day in sunny New York...

Best,
Wolfgang

mvidas
10-02-2006, 05:35 AM
You too! If she has any more questions, feel free to ask away, or you could always encourage her to create an account here :)