PDA

View Full Version : Solved: Move excel files to a difference folder if sheet name is not "Payment Request"



aravindhan_3
06-16-2009, 01:51 AM
Hi,

I have some 50 excel files in a folder, I open each file manually and see the sheet name if the sheet name is not "Payment Request" then i will move that file to a new folder in D:/temp.

is it possible in VBA to check all the files if the sheet name is Payment Request if not move those files to a folder.

cheers
Arvind

Bob Phillips
06-16-2009, 03:28 AM
Sub LoopFiles()
Const FOLDER_SOURCE As String = "C:\Test\" '<<<< Change to suit
Const FOLDER_DEST As String = "C:\Target\" '<<<< Change to suit
Dim Filename As String

On Error Resume Next
MkDir FOLDER_DEST
On Error GoTo 0

Filename = Dir(FOLDER_SOURCE & "*.xls")
Do While Filename <> ""

Workbooks.Open FOLDER_SOURCE & Filename
If SheetExists("Payment Request", ActiveWorkbook) Then

ActiveWorkbook.Close savechanges:=False
Name FOLDER_SOURCE & Filename As FOLDER_DEST & Filename
Else

ActiveWorkbook.Close savechanges:=False
End If

Filename = Dir
Loop
End Sub

'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

aravindhan_3
06-20-2009, 10:02 PM
Hi,

Its perfect and working

Thank you so much

Arvind

Omer
02-04-2015, 10:09 PM
hi,
will this code work for .pdf files and if yes where do I save the code. I am new to vba :-). Thanks

Dianne
02-07-2015, 02:38 AM
Is there a way to have a bit of code or a macro in Excel to export one sheet only, with formatting, to a new/blank database, and then save that new sheet as a specified string to include the name of the sheet it originated from?