+ Reply to Thread
Results 1 to 7 of 7

PDF generated list order

Hybrid View

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    London
    MS-Off Ver
    MS365 Enterprise Version 2310
    Posts
    52

    PDF generated list order

    Hi

    I have a small problem in the ORDER that my pdf's are generated compared to the way this order is displayed on my main front sheet.

    My code loops through and checks for topics on which a candidate scored than 75% on a variety of topics. It then produces 10 pdf's on those topics only with a main cover page.

    The problem I have is that order of the pdf names on the front page is NOT in the same order as when they are generated. This confuses the candidate who could see e.g. "Bears.pdf", "Dogs.pdf", etc. on the front page but the 1st pdf in the booklet could be "Cats.pdf". Could I possibly add a number at the beginning of each pdf (e.g. 1,2,3 etc.) so that they are in the same order as the ones displayed on my main front sheet?

    Here is the code for the main front page with the order of the pdfs being displayed.

    'UPDATE WORKLISTS
            For i = 2 To 3
                count = 1
                'ADD IF STATEMENT FOR EACH WORKLIST
                If i = 2 Then
                    LastRow = ActiveSheet.Cells(Rows.count, 4).End(xlUp).Row
                    Set rng = Worksheets("Main").Range("D5:D" & LastRow)
                ElseIf i = 3 Then
                    LastRow = ActiveSheet.Cells(Rows.count, 12).End(xlUp).Row
                    Set rng = Worksheets("Main").Range("L5:L" & LastRow)
    
                End If
                Set dnary = CreateObject("Scripting.Dictionary")
                For Each cell In rng
                    If cell.Offset(0, 3) <= 0.75 And count <= 10 Then
                        If Not dnary.Exists(cell.Value) Then
                            dnary.Add cell.Value, 1
                            'CHANGE SHEET NUMBER TO MATCHING WORKLIST SHEET NUMBER
                            Worksheets(i).Range("A" & count + 1) = count
                            count = count + 1
                        End If
                    End If
                Next cell
            Next i
        End If
        Target.Select

    Here is part of my loop code to generate the pdfs

        'Iterate each candidate reference to update Main sheet.
        For i = 1 To rng2.Rows.count
          wsM.[F1] = rng2(i).Offset(, -1) 'Reference number for candidate
          rn = Trim(wsM.[F1])
          count = 1
          'Iterate each file item and copy files and make pdf for candidate.
          For Each c1 In rng1
            If c1.Offset(, 3) <= 0.75 And count <= 10 And _
            Not .FileExists(p2 & Trim(wsM.[c2]) & "\" & Trim(wsM.[c2]) & rn & "." & .GetFileName(c1)) Then
              If .FileExists(c1) Then
              '.CopyFile c1, p2 & rn & "." & .GetFileName(c1), True
                .CopyFile c1, p2 & Trim(wsM.[c2]) & "\" & Trim(wsM.[c2]) & rn & "." & .GetFileName(c1)
                count = count + 1
              End If
            End If
          Next c1
    I hope the above makes sense. Still new to this and still learning. Any help would be greatly appreciated.

    TIA
    Last edited by F1ash; 03-25-2024 at 10:26 AM.

  2. #2
    Registered User
    Join Date
    09-22-2015
    Location
    London
    MS-Off Ver
    MS365 Enterprise Version 2310
    Posts
    52

    Re: PDF generated list order

    I have added some photos to illustrate my problem further as I am still having no joy with this.

    Basically I need the order of the pdf's (in my folder) to be the same as the order on main page sheet (shown in the table picture attached).

    I think a simple count (1., 2., 3., etc) added to each file name (which corresponds to the order of the list on the main page sheet) may work but not sure how to add it to the code above in post #1.

    Can anyone help please?
    Attached Images Attached Images
    Last edited by F1ash; 03-25-2024 at 10:27 AM.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,015

    Re: PDF generated list order

    Pictures are nice but having a sample workbook to manipulate is so much better. Please post a sample workbook.

  4. #4
    Registered User
    Join Date
    09-22-2015
    Location
    London
    MS-Off Ver
    MS365 Enterprise Version 2310
    Posts
    52

    Re: PDF generated list order

    I only have my master sheets all of which are quite cumbersome - hence the code on #1 and pictures.

    Appreciate you taking a look anyway.

  5. #5
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,242

    Re: PDF generated list order

    Probably
    .CopyFile c1, p2 & Trim(wsM.[c2]) & "\" & Trim(wsM.[c2]) & rn & "." & Format(count,"00") & " " & .GetFileName(c1)
    Artik

  6. #6
    Registered User
    Join Date
    09-22-2015
    Location
    London
    MS-Off Ver
    MS365 Enterprise Version 2310
    Posts
    52

    Re: PDF generated list order

    Thanks Artik - I think that works !

  7. #7
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,047

    Re: PDF generated list order

    I think you are right. The sheet is listed by Topic number, but the Files are listed alphabetically. If the topic number is just 10 pdfs numbered maybe you could sort by Name then add the numbers? Other options would be to include the topic number in the file name or sort the folder by date modified.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Truly dynamic list generated by selection of static list?
    By obidon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-06-2015, 11:16 AM
  2. Replies: 0
    Last Post: 02-15-2014, 10:30 AM
  3. Replies: 1
    Last Post: 11-19-2013, 12:03 AM
  4. Open Word Work Order Template with excel generated work order #
    By Tivka in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-26-2012, 02:24 PM
  5. generated parts list from list of assemblies
    By staticchanger in forum Excel General
    Replies: 1
    Last Post: 06-24-2012, 05:20 PM
  6. purchase order list & order number generator
    By podaf in forum Excel General
    Replies: 2
    Last Post: 06-28-2009, 06:02 PM
  7. List generated from value of one cell
    By seanrigby in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2008, 10:12 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1