Move Files Containing Two Sheetnames to Another Folder
Hi All,
I am attempting to move excel files from that contain both sheetnames ("Copy", and "Nutritional") from a target folder ("C:\Testing") to a destination folder ("C:\Sheetnames"). There appears to be something wrong with the nested loop and Access recognizing my Excel objects....Can anyone help with this??? Modification to the existing code would help the most or another example.
Private Sub _Click()
Dim Workbook As Excel
Dim ws As Worksheets
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim Fdate As Date
Dim FileInFromFolder As Object
FromPath = "C:\Testing" '<< Change
ToPath = "C:\Sheetnames" '<< Change
If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
End If
If Right(ToPath, 1) <> "\" Then
ToPath = ToPath & "\"
End If
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If
If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If
For Each FileInFromFolder In FSO.GetFolder(FromPath).Files
For Each ws In Workbook.Sheets
If InStr(1, ws.Name, "Copy") And InStr(ws.Name, "Nutritional") Then
FileInFromFolder.Copy ToPath
End If
Next
Next FileInFromFolder
MsgBox "You can find the files from " & FromPath & " in " & ToPath
End Sub
Re: Move Files Containing Two Sheetnames to Another Folder
Quote:
There appears to be something wrong with the nested loop
this is not very descriptive
Quote:
For Each FileInFromFolder In FSO.GetFolder(FromPath).Files
For Each ws In Workbook.Sheets
If InStr(1, ws.Name, "Copy") And InStr(ws.Name, "Nutritional") Then
to read the sheets in the workbook, you must first open the workbook, which means at some point you need to create and instance of excel, preferably once only, not within the loop, you would need to close the workbook before trying to copy or move it
the last line quoted is looking for one sheet with the words copy and nutritional in the sheet name, not separate sheets
you should try like
Code:
onefound = false
for each ws in workbook.sheets
if lcase(ws.name) = "copy" or lcase(ws.name) = "nutritional" then
if not onefound then
onefound = true
else
' move workbook
exit for
end if
end if
next
not tested