How to move all files from subfolders to one folder by VBA code ?

sharepoint0520
sharepoint0520 used Ask the Experts™
on
Experts,

 I have one Folders and it has some sub folders and Subfolder has files like excel , word etc... I would like to have one small program when i set source and destination folder and run the script to get all only excel files to Destination folder.

Please let me know if you need more clarification..

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
TemodyPickalbatros, IT Manager

Commented:
you can use this command
also you can save it as a batch file to run in scheduled time

SET MoveDirSource=c:\source
SET MoveDirDestination=d:\destination
ROBOCOPY "%MoveDirSource%" "%MoveDirDestination%" /MOVE /E

Open in new window

End-user support
Commented:
This works.

BrowseFolder is from http://www.robvanderwoude.com/wshexamples.php

Option Explicit

Dim sSrcDir, sTgtDir
Dim objFSO

sSrcDir = BrowseFolder( "", False )
sTgtDir = BrowseFolder( sSrcDir, False )

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.MoveFile sSrcDir & "\*.xls" , sTgtDir

' --------------------------------------------
' FUNCTIONS BELOW

Function BrowseFolder( myStartLocation, blnSimpleDialog )
' This function generates a Browse Folder dialog
' and returns the selected folder as a string.
'
' Arguments:
' myStartLocation   [string]  start folder for dialog, or "My Computer", or
'                             empty string to open in "Desktop\My Documents"
' blnSimpleDialog   [boolean] if False, an additional text field will be
'                             displayed where the folder can be selected
'                             by typing the fully qualified path
'
' Returns:          [string]  the fully qualified path to the selected folder
'
' Based on the Hey Scripting Guys article
' "How Can I Show Users a Dialog Box That Only Lets Them Select Folders?"
' http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun05/hey0617.mspx
'
' Function written by Rob van der Woude
' http://www.robvanderwoude.com
	Const MY_COMPUTER   = &H11&
	Const WINDOW_HANDLE = 0 ' Must ALWAYS be 0

	Dim numOptions, objFolder, objFolderItem
	Dim objPath, objShell, strPath, strPrompt

	' Set the options for the dialog window
	strPrompt = "Select a folder:"
	If blnSimpleDialog = True Then
		numOptions = 0      ' Simple dialog
	Else
		numOptions = &H10&  ' Additional text field to type folder path
	End If
	
	' Create a Windows Shell object
	Set objShell = CreateObject( "Shell.Application" )

	' If specified, convert "My Computer" to a valid
	' path for the Windows Shell's BrowseFolder method
	If UCase( myStartLocation ) = "MY COMPUTER" Then
		Set objFolder = objShell.Namespace( MY_COMPUTER )
		Set objFolderItem = objFolder.Self
		strPath = objFolderItem.Path
	Else
		strPath = myStartLocation
	End If

	Set objFolder = objShell.BrowseForFolder( WINDOW_HANDLE, strPrompt, _
	                                          numOptions, strPath )

	' Quit if no folder was selected
	If objFolder Is Nothing Then
    	BrowseFolder = ""
    	Exit Function
	End If

	' Retrieve the path of the selected folder
	Set objFolderItem = objFolder.Self
	objPath = objFolderItem.Path

	' Return the path of the selected folder
	BrowseFolder = objPath
End Function

Open in new window

TemodyPickalbatros, IT Manager

Commented:
sorry i don't see (only excel files)
please try this

SET MoveDirSource=c:\source
SET MoveDirDestination=d:\destnation
ROBOCOPY *.xls* "%MoveDirSource%" "%MoveDirDestination%" /MOVE /E

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi,

 Can you please write codes in Module or any Excel form ? Is it possible to attach Excel file for you guys ? I am not clear to use Function.

Author

Commented:
NVIT,

 How to use this function in Module ? I would like to Button to click to move files. Any advice?

Author

Commented:
I got it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial