In my application that I am creating, I am using Excel VBA to create a folder
that carries the name of my spreadsheet (i.e. E28802A2).
We use this folder to add specific files (drawings, Reference files, etc)
required for the Engineering Order process.

During the Approval process, I need to have the folder relocated to a
Staging folder
(i.e. from \Pending EOs to \BackLog EOs). How can I do this? The Help file
is less than helpful other than to tell me that I need to create an Object.

Thanks in advance

John Petty

John,

Here is one way...
'-----------------------------------------------
' Requires reference to "Microsoft Scripting Runtime" in the VBA project.
' Following code moves the Solver folder from the Library folder to the C drive.
' Jim Cone - October 23, 2004

Sub CopyFolderWithScriptingRuntime()
Dim strFromPath As String
Dim strToPath As String

Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject

'Wildcards only allowed in the "from" path and only at the end.
strFromPath = "C:\Program Files\Microsoft Office\Office\Library\Solver"
strToPath = "C:\"

Fso.MoveFolder strFromPath, strToPath

'Technically not required, but I prefer it.
Set Fso = Nothing
End Sub
'-----------------------------------------------

'For more information and there is more, download
"Scripting Documentation\Script56.CHM" from Microsoft's web site..

Regards,
Jim Cone
San Francisco, CA

"asmenut" wrote in message ...
In my application that I am creating, I am using Excel VBA to create a folder
that carries the name of my spreadsheet (i.e. E28802A2).
We use this folder to add specific files (drawings, Reference files, etc)
required for the Engineering Order process.
During the Approval process, I need to have the folder relocated to a
Staging folder
(i.e. from \Pending EOs to \BackLog EOs). How can I do this? The Help file
is less than helpful other than to tell me that I need to create an Object.
Thanks in advance
John Petty

Thanks Jim. Works great. But, is there a way to rename the folder / file to
reflect a new location (i.e. E28802 - 28802). A little explanation might
help:

Once the file is authorized, the Log number is changed (so as to reflect the
approval of the Engineering Order) (Log Number E28802 - 28802). This folder
(once approved) will contain all the pertinent files required to process the
Order. When the file and folder is renamed and moved, the EO admin will be
able to set tracking options throughout the rest of the process (i.e.
routing, processing, archiving, etc)

John,

Here is one way...
'-----------------------------------------------
' Requires reference to "Microsoft Scripting Runtime" in the VBA project.
' Following code moves the Solver folder from the Library folder to the C drive.
' Jim Cone - October 23, 2004

Sub CopyFolderWithScriptingRuntime()
Dim strFromPath As String
Dim strToPath As String

Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject

'Wildcards only allowed in the "from" path and only at the end.
strFromPath = "C:\Program Files\Microsoft Office\Office\Library\Solver"
strToPath = "C:\"

Fso.MoveFolder strFromPath, strToPath

'Technically not required, but I prefer it.
Set Fso = Nothing
End Sub
'-----------------------------------------------

'For more information and there is more, download
"Scripting Documentation\Script56.CHM" from Microsoft's web site..

Regards,
Jim Cone
San Francisco, CA

"asmenut" wrote in message ...
In my application that I am creating, I am using Excel VBA to create a folder
that carries the name of my spreadsheet (i.e. E28802A2).
We use this folder to add specific files (drawings, Reference files, etc)
required for the Engineering Order process.
During the Approval process, I need to have the folder relocated to a
Staging folder
(i.e. from \Pending EOs to \BackLog EOs). How can I do this? The Help file
is less than helpful other than to tell me that I need to create an Object.
Thanks in advance
John Petty

John,

I just tried the following code and it worked for me...
(Note also that you can rename a file or folder using the Name statement in VBA)

'-----------------------------------------------
' Requires reference to "Microsoft Scripting Runtime" in the VBA project.
' Following code moves the Analysis folder from the Library folder to the C drive
' and renames the folder and its files.
' Jim Cone - October 24, 2004

Sub MoveFolderWithScriptingRuntime()
Dim strFromPath As String
Dim strToPath As String
Dim strName As String
Dim Fso As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File

Set Fso = New Scripting.FileSystemObject

'Specify path of folder to be moved.
strFromPath = "C:\Program Files\Microsoft Office\Office\Library\Analysis"
'Retain folder name in variable.
Set objFolder = Fso.GetFolder(strFromPath)
strName = objFolder.Name

'Specify new location for folder.
strToPath = "C:\"
'Move the folder.
Fso.MoveFolder strFromPath, strToPath

'Establish a reference to the folder in the new location.
Set objFolder = Fso.GetFolder(strToPath & "\" & strName)

'Skip this part if you only want to rename the folder
'Rename each file in the folder.
For Each objFile In objFolder.Files
objFile.Name = "X" & objFile.Name
Next 'objFile

'Rename the folder.
objFolder.Name = "X" & objFolder.Name

'Technically not required, but I prefer it.
Set objFolder = Nothing
Set objFile = Nothing
Set Fso = Nothing
End Sub
'-----------------------------------------------

Regards,
Jim Cone
San Francisco, CA

"asmenut" wrote in message ...
Thanks Jim. Works great. But, is there a way to rename the folder / file to
reflect a new location (i.e. E28802 - 28802). A little explanation might
help:

Once the file is authorized, the Log number is changed (so as to reflect the
approval of the Engineering Order) (Log Number E28802 - 28802). This folder
(once approved) will contain all the pertinent files required to process the
Order. When the file and folder is renamed and moved, the EO admin will be
able to set tracking options throughout the rest of the process (i.e.
routing, processing, archiving, etc)

John,

-snip-

Yep, that's the ticket. Thanks a million Jim. And thanks to all the MVP's
for their help. Time for this app to go to BETA. You Guys/Girls are the
best.

"Jim Cone" wrote:

John,

I just tried the following code and it worked for me...
(Note also that you can rename a file or folder using the Name statement in VBA)

'-----------------------------------------------
' Requires reference to "Microsoft Scripting Runtime" in the VBA project.
' Following code moves the Analysis folder from the Library folder to the C drive
' and renames the folder and its files.
' Jim Cone - October 24, 2004

Sub MoveFolderWithScriptingRuntime()
Dim strFromPath As String
Dim strToPath As String
Dim strName As String
Dim Fso As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File

Set Fso = New Scripting.FileSystemObject

'Specify path of folder to be moved.
strFromPath = "C:\Program Files\Microsoft Office\Office\Library\Analysis"
'Retain folder name in variable.
Set objFolder = Fso.GetFolder(strFromPath)
strName = objFolder.Name

'Specify new location for folder.
strToPath = "C:\"
'Move the folder.
Fso.MoveFolder strFromPath, strToPath

'Establish a reference to the folder in the new location.
Set objFolder = Fso.GetFolder(strToPath & "\" & strName)

'Skip this part if you only want to rename the folder
'Rename each file in the folder.
For Each objFile In objFolder.Files
objFile.Name = "X" & objFile.Name
Next 'objFile

'Rename the folder.
objFolder.Name = "X" & objFolder.Name

'Technically not required, but I prefer it.
Set objFolder = Nothing
Set objFile = Nothing
Set Fso = Nothing
End Sub
'-----------------------------------------------

Regards,
Jim Cone
San Francisco, CA

"asmenut" wrote in message ...
Thanks Jim. Works great. But, is there a way to rename the folder / file to
reflect a new location (i.e. E28802 - 28802). A little explanation might
help:

Once the file is authorized, the Log number is changed (so as to reflect the
approval of the Engineering Order) (Log Number E28802 - 28802). This folder
(once approved) will contain all the pertinent files required to process the
Order. When the file and folder is renamed and moved, the EO admin will be
able to set tracking options throughout the rest of the process (i.e.
routing, processing, archiving, etc)

John,

-snip-