Jill
08-22-2017, 11:17 PM
Hi All,
Im fairly new to excel macros. I have some scripting experience in other programs but I would consider myself a noob.
Just for transparency purposes, I've tried to do this in another application and failed. So I'm switching to excel
http://www.vbaexpress.com/forum/show...-to-SubFolders (http://www.vbaexpress.com/forum/showthread.php?60432-Move-Files-to-SubFolders)
I have a Directory that includes Folders and Files. If the Filename contains the Foldername I want to move the file into its respective folder.
I've made numerous attempts. I hope someone out there can advise me on what I'm doing wrong. Thanks in advance
Jill
Code is below.... Also including a screenshot of the directory for clarity. Also I left in some of the attempts within the code but commented them out.
At this line i get this error
FSO.Movefile Source:=SourceFileName, Destination:=DestinFileName
run-time error '5':
Invalid procedure call or argument
Sub FileSort()
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim MyFolder As String
Dim MyFile As String
Dim directory As String
Dim i As Integer
Dim FSO As Object
Dim File
Dim Folder
Dim Fldr
Dim SourceFileName As String
Dim DestinFileName As String
''THIS CHECKS TO SEE IF FILE EXISTS...THIS WORKED
'File = Dir("C:\Rendermation\Renders\*.png")
'MsgBox FileEnd Sub
''THIS CHECKS TO SEE IF A FOLDER EXISTS AND IF NOT, CREATES THE FOLDER....THIS WORKED
'Folder = "C:\Rendermation\Renders\*.3dm"
'Fldr = Dir(Folder, vbDirectory)
'If Len(Fldr) > 0 Then
' MsgBox (Fldr & " Already Exists")
'Else
' MkDir Folder
' MsgBox ("Folder Created")
' End If
'End Sub
Folder = "C:\Rendermation\Renders\*.3dm"
Fldr = Dir(Folder, vbDirectory)
File = Dir("C:\Rendermation\Renders\*.png")
Set FSO = CreateObject("Scripting.Filesystemobject")
SourceFileName = "C:\Rendermation\Renders\perspective_" & (Folder) & ".png"
DestinFileName = "C:\Rendermation\Renders\" & (Folder)
FSO.Movefile Source:=SourceFileName, Destination:=DestinFileName 'THIS IS THE LINE THAT ERRORS
MsgBox (SourceFileName + " Moved to " + DestinFileName)
'FSO.Movefile "C:\Rendermation\Renders\perspective_" & (Folder) & ".png", "C:\Rendermation\Renders\" & (Folder) 'THIS LINE ALSO ERRORS
'End If
End Sub
Im fairly new to excel macros. I have some scripting experience in other programs but I would consider myself a noob.
Just for transparency purposes, I've tried to do this in another application and failed. So I'm switching to excel
http://www.vbaexpress.com/forum/show...-to-SubFolders (http://www.vbaexpress.com/forum/showthread.php?60432-Move-Files-to-SubFolders)
I have a Directory that includes Folders and Files. If the Filename contains the Foldername I want to move the file into its respective folder.
I've made numerous attempts. I hope someone out there can advise me on what I'm doing wrong. Thanks in advance
Jill
Code is below.... Also including a screenshot of the directory for clarity. Also I left in some of the attempts within the code but commented them out.
At this line i get this error
FSO.Movefile Source:=SourceFileName, Destination:=DestinFileName
run-time error '5':
Invalid procedure call or argument
Sub FileSort()
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim MyFolder As String
Dim MyFile As String
Dim directory As String
Dim i As Integer
Dim FSO As Object
Dim File
Dim Folder
Dim Fldr
Dim SourceFileName As String
Dim DestinFileName As String
''THIS CHECKS TO SEE IF FILE EXISTS...THIS WORKED
'File = Dir("C:\Rendermation\Renders\*.png")
'MsgBox FileEnd Sub
''THIS CHECKS TO SEE IF A FOLDER EXISTS AND IF NOT, CREATES THE FOLDER....THIS WORKED
'Folder = "C:\Rendermation\Renders\*.3dm"
'Fldr = Dir(Folder, vbDirectory)
'If Len(Fldr) > 0 Then
' MsgBox (Fldr & " Already Exists")
'Else
' MkDir Folder
' MsgBox ("Folder Created")
' End If
'End Sub
Folder = "C:\Rendermation\Renders\*.3dm"
Fldr = Dir(Folder, vbDirectory)
File = Dir("C:\Rendermation\Renders\*.png")
Set FSO = CreateObject("Scripting.Filesystemobject")
SourceFileName = "C:\Rendermation\Renders\perspective_" & (Folder) & ".png"
DestinFileName = "C:\Rendermation\Renders\" & (Folder)
FSO.Movefile Source:=SourceFileName, Destination:=DestinFileName 'THIS IS THE LINE THAT ERRORS
MsgBox (SourceFileName + " Moved to " + DestinFileName)
'FSO.Movefile "C:\Rendermation\Renders\perspective_" & (Folder) & ".png", "C:\Rendermation\Renders\" & (Folder) 'THIS LINE ALSO ERRORS
'End If
End Sub