More uniform search/insertion (if possible)

[Abdullah]

Hi guys,

I have the code below in a VB macro. You walk the macro to a folder, it then searches the folder for .jpg or .jpeg files, and adds the name (column 1), photo, and hyperlink (both in column 3) to the table. It runs and places all the photos within the folder in the table (as its own entry), which is fantastic....exactly what I need it to (Thanks to all the help various people in the forum have been nice enough to give me).

The only catch is that when I run it, more times than not, the table isn't in the correct order from top to bottom. I wasn't sure if it was because of how the code searches the folder, or if it has something to do with something else.

Ideally the photos would go down the table ordered by photo name.

Any ideas of how to resolve this issue?

Partial code is below.


For Each pic In fol.Files
If LCase(Right(pic.Path, 4)) = ".jpg" Or LCase(Right(pic.Path, 5)) = ".jpeg" Then
'add row and give reference to it
Set roe = ActiveDocument.Tables(1).Rows.Add
'gives reference to cell 1 then adds photo name
Set cel = roe.Cells(1)
cel.Range.Text = pic.Name
entry = entry + 1
'gives reference to cell 3 then adds pic
Set cel = roe.Cells(3)
'add photo
Set ish = cel.Range.InlineShapes.AddPicture(FileName:=pic.Path, LinkToFile:=False, SaveWithDocument:=True)
'add hyperlink to photo
Set MyLink = ActiveDocument.Range.Hyperlinks.Add(ish, pic.Path, , , "")
End If
Next

[fumei]

1. Please use the VBA code tags, and the underscore character (if required).

2. This code (extrapolated from your snippet) puts the text (the image name) in Column 1 in alphabetical order down the table. This is apparently what you want, and that is what it does. So what is different about your code?

Option Explicit

Sub Whatever()
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim pic As Scripting.File
Dim roe As Row
Dim cel As Cell
Dim ish As InlineShape
Dim MyLink As Hyperlink

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("C:\Outlook2003Project")

For Each pic In fld.Files
If LCase(Right(pic.Path, 4)) = ".jpg" _
Or LCase(Right(pic.Path, 5)) = ".jpeg" Then
'add row and give reference to it
Set roe = ActiveDocument.Tables(1).Rows.Add
'gives reference to cell 1 then adds photo name
Set cel = roe.Cells(1)
cel.Range.Text = pic.Name
'gives reference to cell 3 then adds pic
Set cel = roe.Cells(3)
'add photo
Set ish = cel.Range.InlineShapes _
.AddPicture(FileName:=pic.Path, LinkToFile:=False, _
SaveWithDocument:=True)
'add hyperlink to photo
Set MyLink = ActiveDocument.Range. _
Hyperlinks.Add(ish, pic.Path, , , "")
Set ish = Nothing
Set MyLink = Nothing
End If
Next
Set fld = Nothing
Set fso = Nothing
End Sub

[fumei]

In any case, the For Each loop - For Each pic In fld.Files - is going to process in the order it finds the files. This should be alphabetical order.

Are you saying some times - "The only catch is that when I run it, more times than not," - it DOES do it in alphabetical order, and some times it does NOT?

[Abdullah]

That is exactly what I want....but I ran the code on 10 different folders, and in 6 of them, the order was not in alphabetical/numerical. A seemingly random amount of rows gets put at the bottom....so the table may read:

17-63
1-16

Where rows that should be in the begginning get put at the end of the table...Also, sorry about lack of using VBA Tags.

Sub photos()
'Variables
Dim fso As FileSystemObject
Dim fol As Folder
Dim pic As File
Dim tbl As Table
Dim roe As Row
Dim cel As Cell
Dim ish As InlineShape
Dim pth As New MSComDlg.CommonDialog
Dim ish As InlineShape
Dim MyLink As Hyperlink
entry = 0
'Browse to folder
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
pname = .SelectedItems(1)
Else
MsgBox "You pressed Cancel"
End If
End With
'file path for pictures
Set fso = New FileSystemObject
Set fol = fso.GetFolder(pname)
'set row 1 as header for each page
Set tbl = ActiveDocument.Range.Tables(1)
ActiveDocument.Tables(1).Rows(1).HeadingFormat = True
'FILLING IN TABLE
For Each pic In fol.Files
If LCase(Right(pic.Path, 4)) = ".jpg" Or LCase(Right(pic.Path, 5)) = ".jpeg" Then
'add row and give reference to it
Set roe = ActiveDocument.Tables(1).Rows.Add
'gives reference to cell 1 then adds text
Set cel = roe.Cells(1)
cel.Range.Text = pic.Name
entry = entry + 1

'gives reference to cell 3 then adds pic
Set cel = roe.Cells(3)
'add photo
Set ish = cel.Range.InlineShapes.AddPicture(FileName:=pic.Path, LinkToFile:=False, SaveWithDocument:=True)
'add hyperlink
Set MyLink = ActiveDocument.Range.Hyperlinks.Add(ish, pic.Path, , , "")
End If
Next

End Sub

[Abdullah]

I did get more specific with

Dim fso As Scripting.FileSystemObject
Dim fol As Scritpting.Folder
Dim pic As Scripting.File

I'm not sure where the problem lies, any ideas?

[fumei]

I am not reading anymore as it is difficult to read. I asked if you could please use the underscore character, and even gave my example code with such.

I have no idea what:

"That is exactly what I want....but I ran the code on 10 different folders, and in 6 of them, the order was not in alphabetical/numerical. A seemingly random amount of rows gets put at the bottom....so the table may read:

17-63
1-16"

means.

What does

17-63
1-16

MEAN????

In column 1, and only TWO rows, you have - AS TEXT! :

17-63.jpg
1-16.jpg

with TWO files listed?? : 17-63.jpg and 1-16.jpg

In any case, scrolling back and forth gives me a headache. I sure someone will be able to help you.

[Abdullah]

When I said the table ended up like:
17-63
1-16

I ment there were 63 rows total....
Row 1: Photo #17
Row 2: Photo #18
Row 3: Photo # 19
and so on and so fourth until
Row 46: Photo # 63
Row 47: Photo # 1
Row 48: Photo # 2
and so on and so fourth until
Row 63: Photo # 16

So the photos do not actually end up in numberical/alphabetical order in the table.

The numbers aren't always the first 16 rows at the bottom, but rows that should be at the top for the sake of numberical/alphabetical order are placed at the bottom

[fumei]

The photo "number" is totally, completely, utterly irrelevant and meaningless.

WHAT you are processing?

The files, by filename. Period. For Each pic In fol.Files

You may think it is:

Row 1: Photo #17
Row 2: Photo #18
Row 3: Photo # 19

But it REALLY is:

Row 1: pic #1 of fol.Files (by filename)
Row 2: pic #2 of fol.Files (by filename)
Row 3: pic #3 of fol.Files (by filename)

For Each pic In fol.Files

The code processes by filename, and ONLY by filename. So unless your filenames are named in the order you want, THAT is what you are stuck with.

Unless of course you Sort the table.....

[Tinbendr]

The only catch is that when I run it, more times than not, the table isn't in the correct order from top to bottom. You'd think it would be in order, but most times not.

You'll have to read the list of files into an array, sort the array, then post them to the table to make sure they are in the order you desire.

[Abdullah]

I understand that the photo numbers aren't that relevanat, but they are when the files are NAMED the photo number.

The file name for all the files in fol.Files IS the photo number....that's why I am trying to figure out where the problem is. Because I assumed it would always go in alphatbetical/numerical order by filename (which is the photo number).

I will try loading them all into an array and sorting before putting them into the table.

Thanks guys

[fumei]

I am trying to duplicate this, and so far have not been able to. I ran listing code on 43 different folders and they have ALL been listed in alphabetical order, by filename.

I can not get it to list any NOT in alphabetical order.

[Abdullah]

I'm not sure what is causing it. I copied your code above and used it, and got the same issue with a folder of photos that has been doing it. Files 1-48 get moved to the bottom of the table, so it starts with file 49

I'm not sure if its because the file names are numbers (but I didn't think that would matter [files are named 0117001 to 0117153]).

I'm still working on getting the files sorted in an array....I'll let you know how that goes....thanks fumei

[Tinbendr]

I'm not sure what is causing it. The DOS (disk operating system) does not store files in alphabetical order. It stores them as required for best storage. When you look at files with Windows Explorer or Word->File->Open, the system reads the files, does a quick sort and displays them to you. But if you read them with FileSystemObject, it reads them as they are stored. You have to sort them.

You might get lucky and get a list back that is sorted, but there is no guarantee it will happen everytime.

[TonyJollans]

It [the OS] stores them [files] as required for best storage.

The OS isn't that smart. :)

More seriously, albeit a little OT, (AFAIK) how and where the files are stored is not relevant - it is the index (I don't know the correct term) of the folder from which the information is pulled - not that that helps at all as I don't know how that works either - and even if I did, it still can't be controlled from VBA.

[fumei]

Abdullah, you STILL must be very clear. You write:

"Files 1-48 get moved to the bottom of the table, so it starts with file 49"

There is no, so far, a File 1. What are EXACTLY - and I have repeatedly asked this - the filenames?

Do you mean: "Files 0117001.jpg - 0117048.jpg get moved to the bottom of the table, so it starts with file 0117049.jpg"?????

I will tell you why I am asking. Because I can not duplicate this with files named like you suggest - but do NOT actually say.

I made a bunch of files:

0117001.txt
0117002.txt
0117003.txt
0117004.txt
0117005.txt
0117006.txt
0117007.txt
0117008.txt
0117009.txt
0117010.txt
0117011.txt
0117012.txt
0117013.txt
0117014.txt
0117015.txt
0117016.txt
0117017.txt
0117018.txt
0117019.txt
0117020.txt

Now, I did this four separate times, in four separate folders. Each time I created the files in a different order.

0117001.txt
0117003.txt
0117020.txt
0117004.txt
0117005.txt
0117006.txt
0117007.txt
0117009.txt
0117010.txt
0117011.txt
0117002.txt
0117012.txt
0117018.txt
0117015.txt
0117008.txt
0117016.txt
0117013.txt
0117014.txt
0117017.txt
0117019.txt


etc. etc.

In other words, in one folder I created the files in the numeric order, and in other folders I created the files in random order.

Just to be VERY clear, I made FOUR separate folders, and 20 files in each one.

c:\TestData1 - files created in numeric order
c:\TestData2 - files created in random order
c:\TestData3 - files created in random order
c:\TestData4 - files created in random order

OK? Then I ran this code:Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File
Dim strFolder()
Dim var

strFolder = Array("c:\TestData1", _
"c:\TestData2", "c:\TestData3", "c:\TestData4")

Set fso = CreateObject("Scripting.FileSystemObject")

For var = 0 To UBound(strFolder)
Set fld = fso.GetFolder(strFolder(var))
For Each fil In fld.Files
If Right(fil.Name, 3) = "txt" Then
Selection.TypeText Text:=fil.Name & vbCrLf
End If
Next
' to put a space between list of files for each folder
Selection.TypeParagraph
Next
Set fld = Nothing
Set fso = NothingIt goes through each folder (TestData1, TestData2, TestData3, TestData4) and writes out the names. I ended up with FOUR lists - one for each folder.

In ALL cases, repeat ALL cases, the list looked like:

0117001.txt
0117002.txt
0117003.txt
0117004.txt
0117005.txt
0117006.txt
0117007.txt
0117008.txt
0117009.txt
0117010.txt
0117011.txt
0117012.txt
0117013.txt
0117014.txt
0117015.txt
0117016.txt
0117017.txt
0117018.txt
0117019.txt
0117020.txt


In numeric order.

[fumei]

BTW: Tony is correct. The OS file system does not store files for "best storage". It stores them in the first available place it CAN. If all of the file can not fit into that first place, it puts as much as it CAN - by cluster - then goes to the next available place and puts as much as it CAN. If there is still not enough space, it keeps going until all of the files is stored.

This is why parts of files can be literally stored in many locations. Parts of it.

This is why we have to defrag. The file system can, and does, fragment files into parts, with those parts stored where ever the file system CAN store them.

The names of the files - including all stored locations for its parts - are in the index (as Tony put it). While much more complicated that the old FAT system, essentially it is the same. There is a File Allocation Table. It is that table, or index, that keeps the information on where everything actually is on the disk.

When you query a folder for its contents, the file system first checks the information on the folder contents (the filenames themselves), THEN queries the filenames for their physical locations.