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!

Move File to New Directory
 

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!

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!

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!

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!

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!

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!