Need to move a HUGE list of files

[ccoyle]

Hi All,
I have a 450,000 row Excel doc listing files from different directories that I need to move to a new location and maintian directory structure.

Examples:
'N:\HR\Policies\evacuation.xls' needs to be moved to 'N:\Archive\HR\Policies\evacuation.xls' (Destination does not exist)
'N:\Accounts\Tech\HP\agreement.doc' needs to be moved to 'N:\Archive\Accounts\Tech\HP\agreement.doc' (Destination does not exist)

I was thinking to convert the data to a .txt and use VB to read the data, create the dest directory and move the files one line at a time. Unfortunately my vbs skills do not match my thinking skills . Any assistance would be greatly appreciated!

Happy Holidays!


Thanks,
-Chris

 

[MiggyD]

Unfortunately my vbs skills do not match my thinking skills

Since VBS is not your strong suit and you are using Excel, would you considered using VBA--if it <the VBA module> was installed when Excel was installed <or set to "1^st run">. It would make it easier instead of converting to text file.

If you answered yes to using VBA instead of VBS then repost your query in the Microsoft Office forum at

http://www.tek-tips.com/threadminder.cfm?pid=68.

--MiggyD


A common mistake people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools. - Douglas Adams

 

[ccoyle]

I'm not strong in VBS but I still thought it was the right tool for the job. I'm actually less familiar with VBA / Excell scripting but I will definately post in that forum as you suggest, hopefully between the 2, someone will be able to help me develop the solution.

Thanks!


Cheers,
-Chris

 

[guitarzan]

This can be done perfectly well in vbScript. Look into the FileSystem Object. The code below (not tested) should allow you to open a text file and read each line... I will leave the "move to new location" to you.

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("c:\filelist.txt", ForReading)
arrLines = Split(f.ReadAll, vbCrLf)
For Each sFilename in arrLines
   [COLOR=#4E9A06]'move "sfilename" to new location[/color]
Next

As for the processing the move, look into the MoveFile method

 

[SkipVought]

Forum68 is the OFFICE forum for native Application features.

Rather use forum707 for VBA code issues.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ccoyle]

Thanks Guitarzan, but without the complete syntax I won't be able to use your example. It looks like you are on the right track but I don't know how to "write" script and I'm not able to plug someone else's example of MoveFiles into this script if it has even slightly altered syntax (which inevitably it does). If you could provide additional assitance, that would be very much appreciated. Otherwise, thanks for your help.

Thanks,
-Chris