Do you know how the fso.GetFolder(Folderpath).Files command returns? I'm trying to get into a folder with bunch of .png files. I want to pull each of them in alphabetical order, copy and place those .png into an Excel sheet. I got a code which kind of does what I need, except the alphabetical part. The fso.GetFolder(Folderpath).Files seems to grab a list of all the files in the folder. But I do not understand in what order it does it; it's not in alphabetical, not in time modified either. Can anyone explain to me?
Below is the VBA code I use in case you're interested:
Sub insert()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
Sheets("Sheet1").Activate
Folderpath = "myPath"
Set fso = CreateObject("Scripting.FileSystemObject")
Set listfiles = fso.GetFolder(Folderpath).Files
For Each fls In listfiles
strCompFilePath = Folderpath & "" & Trim(fls.Name)
If strCompFilePath <> "" Then
If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then
counter = counter + 1
Sheets("Sheet1").Range("A" & counter).ColumnWidth = 45
Sheets("Sheet1").Range("A" & counter).Value = fls.Name
Sheets("Sheet1").Range("B" & counter).ColumnWidth = 40
Sheets("Sheet1").Range("B" & counter).RowHeight = 150
Sheets("Sheet1").Range("B" & counter).Activate
With ActiveSheet.Shapes.AddPicture(strCompFilePath, msoFalse, msoCTrue, ActiveSheet.Range("B" & counter).Left + 1, ActiveSheet.Range("B" & counter).Top + 1, 200, 140)
End With
Sheets("Sheet1").Activate
End If
End If
Next
mainWorkBook.Save
End Sub
Below is the VBA code I use in case you're interested:
Sub insert()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
Sheets("Sheet1").Activate
Folderpath = "myPath"
Set fso = CreateObject("Scripting.FileSystemObject")
Set listfiles = fso.GetFolder(Folderpath).Files
For Each fls In listfiles
strCompFilePath = Folderpath & "" & Trim(fls.Name)
If strCompFilePath <> "" Then
If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then
counter = counter + 1
Sheets("Sheet1").Range("A" & counter).ColumnWidth = 45
Sheets("Sheet1").Range("A" & counter).Value = fls.Name
Sheets("Sheet1").Range("B" & counter).ColumnWidth = 40
Sheets("Sheet1").Range("B" & counter).RowHeight = 150
Sheets("Sheet1").Range("B" & counter).Activate
With ActiveSheet.Shapes.AddPicture(strCompFilePath, msoFalse, msoCTrue, ActiveSheet.Range("B" & counter).Left + 1, ActiveSheet.Range("B" & counter).Top + 1, 200, 140)
End With
Sheets("Sheet1").Activate
End If
End If
Next
mainWorkBook.Save
End Sub