Consulting

Results 1 to 16 of 16

Thread: More uniform search/insertion (if possible)

  1. #1

    More uniform search/insertion (if possible)

    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
    Thanks,

    Abdullah

  2. #2
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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?
    [vba]
    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
    [/vba]
    Last edited by fumei; 02-22-2008 at 10:54 AM.

  3. #3
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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?

  4. #4
    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.

    [VBA]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[/VBA]
    Thanks,

    Abdullah

  5. #5
    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?
    Thanks,

    Abdullah

  6. #6
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.

  7. #7
    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
    Thanks,

    Abdullah

  8. #8
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.....

  9. #9
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    992
    Location
    Quote Originally Posted by Abdullah
    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.

    David


  10. #10
    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
    Thanks,

    Abdullah

  11. #11
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.

  12. #12
    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
    Thanks,

    Abdullah

  13. #13
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    992
    Location
    Quote Originally Posted by Abdullah
    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.

    David


  14. #14
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  15. #15
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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:[vba]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 = Nothing[/vba]It 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.

  16. #16
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •