I have a list of beginnings of filesnames in excel ,
I want to make a macro that will move them from one defined directory to the other
The file name are named like this AAAAXXX
AAAA- are unique numbers that i will put in excel
XXX- are also unique number but i want macro to skip them while moving/copying files
I tried using *
after the file name but it read it as part of file name and not as a wildcard
I found out that Fso.Movefile
doesnt work when i put variable in it . how can i solve it ?
Or i do need to use other command
Is it possible to do it without using library?
Sub movingfilename()
Dim a As String
Dim cell As Range
Dim locationstart As String
Dim locationend As String
Dim filename As String
Dim notfoundfiles As New Collection
Dim message As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
locationstart = "G:Teststart" & filename
locationend = "G:Teststop" & filename
For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(-4162).Row)
If Not IsEmpty(cell) Then
filename = cell.Value
fso.MoveFile "G:Teststartfilename*", "G:Teststop"
End If
Next cell
End Sub
I want to move file from place a to place b. I have beginning of their names and want excel to move them ignoring rest of their name
Your code isn’t working because you are incorrectly constructing your code arguments, not because it won’t take a variable.
Adapt the following working code to your other requirements. The code as written has no error checking or checking for missing files in start
or duplicate files in end
:
Please note my preferences:
- the use of
Option Explicit
(you can google for what that does) - not relying on
ActiveSheet
when setting your range but explicitly stating the workbook and worksheet. - not using a variable name that is very similar to a property name (eg:
cell
vscells
vsc
) - using column and row numbers instead of alphabetical column names (easier to keep track of things).
- Early binding for the filesystem object is a bit more efficient, but can be problematic if you have to distribute the macro, so I would not object to the late binding you show in your code
Option Explicit
Sub movingFilename()
Dim fso As FileSystemObject
Dim locationStart As String
Dim locationEnd As String
Dim fileName As String
Dim c As Range
locationStart = "G:Teststart"
locationEnd = "G:Testend"
Set fso = New FileSystemObject
With ThisWorkbook.Worksheets("Sheet5")
For Each c In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
If Not IsEmpty(c) Then
fileName = c.Value & "*"
fso.MoveFile locationStart & fileName, locationEnd
End If
Next c
End With
End Sub
2
Move File Matching Pattern
- This will overwrite any existing files!!!
Sub MoveFilesMatchingPattern()
' Define constants.
Const PROC_TITLE As String = "Move Files Matching Pattern"
Const SRC_FOLDER_PATH As String = "G:Teststart"
Const DST_FOLDER_PATH As String = "G:Teststop"
Const DST_OVERWRITE_FILES As Boolean = True ' do not modify!
' Reference the range.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim rg As Range:
Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
' Return the unique values from the range in the keys of a dictionary
' and a 'False' for each corresponding item (value).
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range, FilePattern As String
For Each cell In rg.Cells
FilePattern = CStr(cell.Value) & "*"
If Len(FilePattern) > 1 Then
dict(FilePattern) = False
End If
Next cell
' For each file in the source folder, loop through
' the elements of the dictionary.
' Using the 'Like' operator, attempt to find a match.
' For each match, copy the source file to the destination folder
' overwriting existing files, delete the source file,
' and set the corresponding Boolean
' in the items of the dictionary to 'True',
Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim fsoFile As Object, Key As Variant, MovedFilesCount As Long
For Each fsoFile In fso.GetFolder(SRC_FOLDER_PATH).Files
For Each Key In dict.Keys
With fsoFile
If .Name Like Key Then ' pattern matched; case-sensitive
.Copy DST_FOLDER_PATH, DST_OVERWRITE_FILES
.Delete
dict(Key) = True
MovedFilesCount = MovedFilesCount + 1
Exit For
End If
End With
Next Key
Next fsoFile
' Loop through the elements of the dictionary and remove each element
' whose corresponding item is set to 'True'.
For Each Key In dict.Keys
If dict(Key) Then dict.Remove Key
Next Key
' Inform.
Dim MsgMoved As String: MsgMoved = MovedFilesCount & " file" _
& IIf(MovedFilesCount = 1, "", "s") & " moved."
If dict.Count = 0 Then
MsgBox MsgMoved & vbLf & vbLf & "All patterns matched.", _
vbInformation, PROC_TITLE
Else
MsgBox MsgMoved & vbLf & vbLf & "No files matching the following " _
& dict.Count & " pattern" & IIf(dict.Count = 1, "", "s") _
& " found:" & vbLf & vbLf & Join(dict.Keys, vbLf), _
vbInformation, PROC_TITLE
End If
End Sub