Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
I have a workbook that posts to two other workbooks. As part of the setup
process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
One way is to open the files and do a SaveAs to the directory you want them in.
"David" wrote: I have a workbook that posts to two other workbooks. As part of the setup process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
Yes...this seems simple enough...but I do not have control of the file
locations. I need to set a variable for the current location, move up one directory, and copy the files to the location. Not so simple once you throw in that variable. Thanks for trying! "Don Guillett" wrote: maybe this simple idea will help? Modify to suit Sub movefile() OldName = "C:\oldfolder\oldname.xls" NewName = "C:\newfolder\newname.xls" Name OldName As NewName End Sub -- Don Guillett SalesAid Software "David" wrote in message ... I have a workbook that posts to two other workbooks. As part of the setup process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
demo'd from the immediate window:
? curdir C:\Documents and Settings\Thomas Ogilvy\My Documents chdir Curdir & "\.." ? curdir C:\Documents and Settings\Thomas Ogilvy -- Regards, Tom Ogilvy "David" wrote in message ... Yes...this seems simple enough...but I do not have control of the file locations. I need to set a variable for the current location, move up one directory, and copy the files to the location. Not so simple once you throw in that variable. Thanks for trying! "Don Guillett" wrote: maybe this simple idea will help? Modify to suit Sub movefile() OldName = "C:\oldfolder\oldname.xls" NewName = "C:\newfolder\newname.xls" Name OldName As NewName End Sub -- Don Guillett SalesAid Software "David" wrote in message ... I have a workbook that posts to two other workbooks. As part of the setup process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
Thanks Tom....
the line chdir Curdir & "\.." did it... I was just using chdir ".." Thanks much! "Tom Ogilvy" wrote: demo'd from the immediate window: ? curdir C:\Documents and Settings\Thomas Ogilvy\My Documents chdir Curdir & "\.." ? curdir C:\Documents and Settings\Thomas Ogilvy -- Regards, Tom Ogilvy "David" wrote in message ... Yes...this seems simple enough...but I do not have control of the file locations. I need to set a variable for the current location, move up one directory, and copy the files to the location. Not so simple once you throw in that variable. Thanks for trying! "Don Guillett" wrote: maybe this simple idea will help? Modify to suit Sub movefile() OldName = "C:\oldfolder\oldname.xls" NewName = "C:\newfolder\newname.xls" Name OldName As NewName End Sub -- Don Guillett SalesAid Software "David" wrote in message ... I have a workbook that posts to two other workbooks. As part of the setup process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move File to New Directory
David,
See http://www.cpearson.com/excel/clonefolder.htm . This will create a new folder from an existing folder, copying all files and subfolders from the source folder to the destination folder. All files and subfolders from the source folder are copied to the destination folder. The subfolder hierarchy that exists in the source folder (subfolders of subfolders of subfolder, etc) are preserved in the destination folder. If you know a folder name but not its Parent folder name, you can use the Scripting.FileSystemObject to get the parent folder. E.g., Dim FSO As Scripting.FileSystemObject Dim KnownFolderName As String Dim ParentFolderName As String Set FSO = New Scripting.FileSystemObject KnownFolderName = "C:\Temp Folder\New Folder" ParentFolderName = FSO.GetFolder(KnownFolderName).ParentFolder.Path Debug.Print ParentFolderName For the code above, you'll need a reference to the Microsoft Scripting Runtime library. In VBA, go to the Tools menu, choose References, and find "Microsoft Scripting Runtime" in the list and check that item. The CloneFolder code does a Copy operation, not a Move operation. You could modify the code to delete the source folder at the end of the operation (see http://www.cpearson.com/excel/recycle.htm for code to send a file or folder to the Recycle Bin --safer than using Kill to get rid of files/folders). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "David" wrote in message ... I have a workbook that posts to two other workbooks. As part of the setup process, all three files are in the same directory (wherever the user initially saves them by downloading from our website or saving from email). About 140 locations will use these files, and each one can initially place them wherever they want, on their local or network drives, so I cannot fix the path and force where the files are initially placed. When the main file is opened, VBA sets up a subdirectory called 2007 Budget, and creates a master file. I need to MOVE the other two files into this subdiretory as part of the set up process so they are in the same subfolder. I just need to go up one directory from the 2007 Budget directory and move these files down to the 2007 Budget directory. Can anyone help? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to move files from one directory to another | Excel Programming | |||
Move all files from one directory to another | Excel Programming | |||
move all files in a directory into another | Excel Programming | |||
Move files from one directory to another: if they are there | Excel Programming | |||
macro to move contents of directory | Excel Programming |