I would like to know what code I can use in my macro to move all .xls files
from P:\new-journals to P:\processed-journals.

Thank you
Prema

Roedd <<Prema wedi ysgrifennu:

I would like to know what code I can use in my macro to move all .xls
files from P:\new-journals to P:\processed-journals.


This requires a reference to the Microsoft Scripting Runtime library.

Sub test()
MoveAllFiles "P:\new-journals", "P:\processed-journals"
End Sub

Sub MoveAllFiles(Source As String, Dest As String)

Dim objFS As New FileSystemObject
Dim objSource As Folder
Dim objDest As Folder
Dim objFile As File

On Error GoTo MoveAllFiles_Err:

If objFS.FolderExists(Source) Then

If objFS.FolderExists(Dest) Then

Set objSource = objFS.GetFolder(Source)
Set objDest = objFS.GetFolder(Dest)

For Each objFile In objSource.Files
objFile.Move (objDest.Path & _
Application.PathSeparator & objFile.Name)

Next
Else
Err.Raise vbObjectError + 1001, "", _
"Folder " & Source & " not found."
End If

Else

Err.Raise vbObjectError + 1001, "", _
"Folder " & Source & " not found."

End If

Exit Sub
MoveAllFiles_Err:
With Err
.Raise .Number, "[MoveAllFiles]" & .Source, _
.Description, .HelpFile, .HelpContext
End With
End Sub

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Robert

Do I have to do something special to activate any reference to the Microsoft
Scripting Runtime library?

Prema

"Robert Bruce" wrote:

Roedd <<Prema wedi ysgrifennu:

I would like to know what code I can use in my macro to move all .xls
files from P:\new-journals to P:\processed-journals.


This requires a reference to the Microsoft Scripting Runtime library.

Sub test()
MoveAllFiles "P:\new-journals", "P:\processed-journals"
End Sub

Sub MoveAllFiles(Source As String, Dest As String)

Dim objFS As New FileSystemObject
Dim objSource As Folder
Dim objDest As Folder
Dim objFile As File

On Error GoTo MoveAllFiles_Err:

If objFS.FolderExists(Source) Then

If objFS.FolderExists(Dest) Then

Set objSource = objFS.GetFolder(Source)
Set objDest = objFS.GetFolder(Dest)

For Each objFile In objSource.Files
objFile.Move (objDest.Path & _
Application.PathSeparator & objFile.Name)

Next
Else
Err.Raise vbObjectError + 1001, "", _
"Folder " & Source & " not found."
End If

Else

Err.Raise vbObjectError + 1001, "", _
"Folder " & Source & " not found."

End If

Exit Sub
MoveAllFiles_Err:
With Err
.Raise .Number, "[MoveAllFiles]" & .Source, _
.Description, .HelpFile, .HelpContext
End With
End Sub

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Roedd <<Prema wedi ysgrifennu:

Do I have to do something special to activate any reference to the
Microsoft Scripting Runtime library?

In the VBE menus:Tools | References - In the resulting dialog, scroll down
to the entry for Microsoft Scripting Runtime. Check it and then click OK.
The project now has a reference set to the library.

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Thank you very much. It works perfectly.

Prema

"Robert Bruce" wrote:

Roedd <<Prema wedi ysgrifennu:

Do I have to do something special to activate any reference to the
Microsoft Scripting Runtime library?

In the VBE menus:Tools | References - In the resulting dialog, scroll down
to the entry for Microsoft Scripting Runtime. Check it and then click OK.
The project now has a reference set to the library.

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Rob,

First and Foremost, thank you for helping Prema with this post. I wa
looking for a similar situation and your code helped me get over th
hump.

However, your code is easier to understand and works better than mine
(i am still learning VBA).

I have a question.

How would I set up VBA to where the user can select a directory to mov
and its new destination?

Any help is GREATLY appreciated. Thank you in advance.

SM

--
sm
-----------------------------------------------------------------------
smh's Profile: http://www.excelforum.com/member.php...fo&userid=2524
View this thread: http://www.excelforum.com/showthread.php?threadid=37800