Ends up with same error. below is the code. This only happens with newer excel versions like 2016. it works perfectly with 2010.
Private Sub UserForm_Initialize()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim FileName As String
Dim NoDupes As New Collection
customerName = Range("C2").Value
projectName = Range("C3").Value
Application.ScreenUpdating = False
FileName = customerName + " - " + projectName
strPath = "Z:\Projects\Records"
'Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Dim searchStarted As Boolean
searchStarted = False
'Check type of file in the folder and open file.
For Each objFile In objFolder.Files
'FileNotFound = True
'MsgBox objFile.Name
Post = InStr(objFile.Name, FileName)
If Post <> 0 Then
searchStarted = True
testString = Right(objFile.Name, Len(objFile.Name) - InStr(objFile.Name, "Lot") - 3)
'Workbooks(FileName).Close savechanges:=True
NoDupes.Add Left(testString, InStr(testString, ".x") - 1)
Else
If searchStarted = True Then Exit For
End If
Next 'objFile
For i = 1 To NoDupes.count - 1
For j = i + 1 To NoDupes.count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, Before:=j
NoDupes.Add Swap2, Before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
ListBox1.AddItem Item
Next Item
ListBox1.ListIndex = 0
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End Sub
Bookmarks