Moving a file with VBA code

[Guest]

I have some code that looks for and opens a file from location A, processes
and closes the file.

I now need to move the file from location A to location B (with the same
name). I have read up on the posts here and have looked at Excel Help and
Ron de Bruin's website (http://www.rondebruin.nl/folder.htm), but it still
doesn't work. This is what I currently have.....

MyPath = "C:\RECEIVED\"
NewPath = "C:\PROCESSED\"
...........some code to put all the files in the path (MyPath) into an
array..........
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
Filename = mybook.Name
newname = NewPath & Filename
...........some more code to do all the processing..........
mybook.Close savechanges:=False
Name mybook AS newname

and...no good. The file still remains in the original location and is not
moved. I have tried the above line to look like...FileCopy mybook.name,
newname and even FileCopy mybook.fullname, newname and still no good.

Any help is greatly appreciated.

 

[Ron de Bruin]

Use the example on my site for creating a copy of the folder and then use code from this site
to loop through all files in the new folder and do your stuff.
http://www.rondebruin.nl/copy4.htm
Change the copy code to your stuff

 

[Guest]

Try it this way:

Name MyPath & mybook.name AS newname

 

[Guest]

Correction. By the time you get to the line of code where you rename the
file, the workbook would have already been closed and so mybook.name would
error out. It should be like this:

Name MyPath & Filename As newname

 

[Guest]

Thanks Vergel, but it didn't work either way. I'm going to attack this again
tomorrow. I think I'll just step through the code with the Locals window
open to show me where the file name is getting "stuck."

Again, thanks.

 

[Guest]

hmmn.. that worked for me.. perhaps double check that a file with the same
name doesn't already exist in the destination folder... good luck.

 

[Guest]

Vergel,
Today, it worked. Probably to much processing yesterday (my brain not
functioning fully, crosslinked files (and my eyes), unrefreshed folder views
and all that stuff). But it works with the following code...

Again, thanks.