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

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,
sharepoint0520Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TemodyPickalbatros, IT ManagerCommented:
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

NVITEnd-user supportCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TemodyPickalbatros, IT ManagerCommented:
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sharepoint0520Author 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.
sharepoint0520Author Commented:
NVIT,

 How to use this function in Module ? I would like to Button to click to move files. Any advice?
sharepoint0520Author Commented:
I got it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.