1

I am using the following VBA code to list all of my excel files in a folder.

For some reason this only works correct 50% of the time.

Lets say i have 12 excel files in a folder, then sometimes only 1 file is listed and then other times all the files are listed.

Pleas can someone show me where i am going wrong?

Sub List()

On Error GoTo Message
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim i2 As Long
Dim i3 As Long
Dim j2 As Long
Dim name As String
Dim Txt As String
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ThisWorkbook.Worksheets(1).Range("M4").value)
i = 18
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.files
'print file path
Cells(i, 6) = objFile.path

'print file path
Cells(i, 7) = Replace(objFile.name, ".xlsx", "")

'print file removal icon
Cells(i, 30) = "Remove"

'Add Hyperlink
ThisWorkbook.Worksheets(1).Hyperlinks.Add Anchor:=Cells(i, 27), Address:=objFile.path, TextToDisplay:="Open Announcement"





'Lookup contact info

Cells(i, 11).Formula = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(""*"" & """ & Range("G" & i).value & """ & ""*"",Contacts!$B:$B,0)),IFERROR(INDEX(Contacts!$C:$C,MATCH(""*"" & """ & Left(Range("G" & i).value, 7) & """ & ""*"",Contacts!$B:$B,0)),""""))"
Cells(i, 14).Formula = "=IF(""" & Range("K" & i).value & """ = """","""",IFERROR(INDEX(Contacts!$D:$D,MATCH(""*"" & """ & Range("K" & i).value & """ & ""*"",Contacts!$C:$C,0)),""""))"
Cells(i, 18).Formula = "=IF(""" & Range("K" & i).value & """ = """","""",IFERROR(INDEX(Contacts!$E:$E,MATCH(""*"" & """ & Range("K" & i).value & """ & ""*"",Contacts!$C:$C,0)),""""))"
Cells(i, 23) = "=IF(K" & i & "="""",""Missing Contact! "","""")&IF(INDEX(Data!L:L,MATCH(G" & i & ",Data!F:F,0))=""TBC"",""Missing Data! "","""")&IF(U" & i & ">=DATE(2017,1,1),"""",""Check Date!"")"

'Delivery Dates
Cells(i, 21).Formula = "=IFERROR(INDEX(Data!$Q:$Q,MATCH(""*"" & """ & Range("G" & i).value & """ & ""*"",Data!$F:$F,0)),IFERROR(INDEX(Data!$Q:$Q,MATCH(""*"" & """ & Left(Range("G" & i).value, 7) & """ & ""*"",Data!$F:$F,0)),""""))"


Cells(i, 25) = "Sync"






i = i + 1

Next objFile

ThisWorkbook.Worksheets(1).Calculate

Application.DisplayAlerts = True
Application.ScreenUpdating = True


Exit Sub
Message:
Application.DisplayAlerts = False
Exit Sub
End Sub
user7415328
  • 1,053
  • 5
  • 24
  • 61
  • 3
    Get rid of the `On Error Goto Message` and see if any error is thrown. Also, please format (indent) your code before posting. – FunThomas Mar 08 '17 at 12:59
  • Anything about the content of `ThisWorkbook.Worksheets(1).Range("M4").value` that might be restricting the files being returned? – Ron Rosenfeld Mar 08 '17 at 13:12

1 Answers1

0

Try to change all your code looking like this:

Cells(i, 6) = objFile.Path

to this:

ThisWorkbook.Worksheets(1).Cells(i, 6) = objFile.Path

It will run probably better. Anyhow, make sure to refer the workbook and the worksheet.

And a bit offtopic:

  1. Use something like SmartIndent for the indentation. You cannot understand what is happening otherwise.

  2. Do not use integer in VBA.

Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100