VBA to compare my file names to the file location and add the new values

[DMO123]

Can someone help? (new to VBA)


I am trying to create a VBA that looks at my current list of file names in a spreadsheet and finds filenames that are not in my list and populates the spreadsheet with the new filenames.


So for example:


I have a list of file names in column “I” I need my VBA to compare this list to my file location in this case “Z:\CLIENTNAME\Waiting\Non-Priority” and add in new filenames. But the VBA cannot delete/ overwrite what is already there it must only add new values on the next available line. If it can be done the VBA should also not include the document type e.g. .docx (not essential as I can use a formula to get rid of this). I also need the VBA to run even when the workbook is closed (not sure how this is done).

The code I have been using is below, but the problems I am facing with this are:
- Only adding the file names to column A and I cannot seem to change it
- Add document type
- The code below replaces the existing values and does not compare the current values to the folder
- Does not run when workbook is closed

VBA CODE:


Sub GetFileNames()
Dim sPath As String
Dim sFile As String
Dim iRow As Integer
Dim iCol As Integer
Dim splitFile As Variant


'specify directory to use - must end in ""
sPath = "Z:\NAME\Waiting\Non_Priority"


iRow = 1
sFile = Dir(sPath)
Do While sFile <> ""
iRow = iRow + 1
splitFile = Split(sFile, "-")
For iCol = 0 To UBound(splitFile)
Sheet1.Cells(iRow, iCol + 1) = splitFile(iCol)
Next iCol
sFile = Dir ' Get next filename
Loop
End Sub


Thank you!!

 

[Fluff]

How about

Sub GetFileNames()
   Dim sPath As String
   Dim sFile As String
   Dim Cl As Range
   Dim Nme As String
   
   'specify directory to use - must end in ""
   sPath = "Z:\NAME\Waiting\Non_Priority"
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("I2", Range("I" & Rows.count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl

      sFile = Dir(sPath)
      Do While sFile <> ""
         Nme = Left(sFile, InStr(1, sFile, ".") - 1)
         If Not .exists(Nme) Then
            Range("I" & Rows.count).End(xlUp).Offset(1).Value = Nme
         End If
         sFile = Dir ' Get next filename
      Loop
   End With
End Sub

 

[DMO123]

Thanks for this. when i play the code line by line it seems to get stuck on the line:

.Item(Cl.Value) = Empty
Next Cl

it doesn't populate the spreadsheet and no errors show, am i missing something?

 

[Fluff]

What do you mean by "It gets stuck"?

 

[DMO123]

Sorry i ran this by pressing F8 to go line by line and the highlighted line kept going up and down at this point on just these two lines then just went to end.

 

[Fluff]

i ran this by pressing F8 to go line by line and the highlighted line kept going up and down at this point on just these two lines

Good That's what it's meant to do. It's looping through the values in col I & adding them to the dictionary.
It should then loop through the files in the directory & if any of those file names are not in the dictionary they should be added to the bottom of the list in col I.

 

[DMO123]

My apologies it seems to be working fine now, i forgot to add the on the end of the file path. thank you for your help!

 

[Fluff]

Glad to help & thanks for the feedback