| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a file with VBA code
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ReportSmith" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
Try it this way:
Name MyPath & mybook.name AS newname -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
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 -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Try it this way: Name MyPath & mybook.name AS newname -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
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. "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Try it this way: Name MyPath & mybook.name AS newname -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
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. -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Try it this way: Name MyPath & mybook.name AS newname -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Moving a file with VBA code
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... Name MyPath & Filename As newname Again, thanks. "Vergel Adriano" wrote: 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. -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Try it this way: Name MyPath & mybook.name AS newname -- Hope that helps. Vergel Adriano "ReportSmith" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CODE 4 MOVING WITHIN A RANGE-TAB | Excel Discussion (Misc queries) | |||
| VB code for Moving Rows up and down | Excel Programming | |||
| Moving files via code | Excel Programming | |||
| Import VBA Code in Excel-File ? (Export VBA Code to file) | Excel Programming | |||
| Moving Code | Excel Programming | |||
Linear Mode
