Hi
I am trying to print some pdf files based on the cell value of column B. Currently the code I have (file attached courtesy of Zeddy and Maudibe), trawls through the filepaths in column C and prints all of the files.
What I want to do however is only print the first 5 files where the candidate scored <50%. Is this a case of adding a For loop in the VBA code for the macro printPDFfiles? Can anyone help here please as I'm very new to VBA?
Thanks
Ash
Last edited by F1ash; 06-20-2018 at 05:42 AM.
I think this may get it.
I changed the data on the Main Sheet to an Excel Table and then made a pivot table off this data. The reason for the table is because tables know how big they are so if you add data the pivot table will follow automatically. You may have to refresh the data in the pivot table whenever the data in the Main table changes. This could be automated - just say the word.
The pivot table is simple. it has the filenames and the scores and is sorted largest to smallest by percent. I manually highlighted the first 5 file names and gave them a static name: Print_Files.
I found the place in the code where it loops on the range temp. I replaced it with Print_Files
I think this will work. I don't happen to have a directory of PDF files handy to test it on.
Please Login or Register to view this content.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thank you very much dflak - your post is very much appreciated.
The issue I am having, and I don't know if Pivot Tables is the way to go here, is that the pdf files are not just for one candidate, they are for a class set so each candidate would have his/her own set of 5 pdfs. (Apologies I should have mentioned this earlier.)
For it to make more sense to you, I just attached my sheet with some dummy pdfs (with help from JM website) that I am working on. (For some reason getting an out of range error nowwhen trying to print Paper 1 topics)
Anyhow, any help/guidance here would be greatly appreciated as we are nearing the end of the year and I needed this set up by then.
Are you expecting each student to have his own workbook?
I am looking at the workbook. I see sheets for Paper1, 2 and 3. I assume the numbers in the max column is the maximum number of points a student can earn on the question. What do columns E:Y represent? This may not be relevant to the issue. It must mean something to you, so I will leave it alone
I see the Student Reference Sheet. That just seems to be a lookup of students. I have no idea what its intended purpose is, but also I don't think it's relevant to the issue.
Now we get to the analysis sheet. This seems to be the meat of the issue. It seems that each question has its own PDF so 1a, 1b, 1c, 1d are linked to the same pdf. It is from this list that you want to pick a top 5. I see the three buttons for each paper.
What I need you to do is looking at one of the Papers (pick the one you believe to be most representative) and walk me through the process you want to happen. On paper 2 the student has 11 100% - do I pick the top 5 from that. Or Q1, Q3, Q4a, Q4b and Q5a are all above 50% are these the top 5? Then having identified the top 5 what do you want me to do with them?
Hi dflak - thanks for the reply
You are right, the analysis tab is where it's all happening.
Basically after a candidate has done his/her test, the scores are recorded on Paper1,2,3 sheets. The analysis sheet then shows the % score for each topic and the file path for a "revision pdf document". I just want to be able to trawl through the list of file paths when the relevant PRINT paper button is clicked (in the order that the questions come in, without sorting percentages etc. because if they get <50% on the first 5 questions, then that's what they need to revise not any 5 where they got <50%.)
Once it goes through the list of file paths, the first associated (<50%) pdfs are printed.
I can get it to trawl through the file paths and print ALL of the pdfs but that's not what i want to do (as there will be topics that don't need to re-visit). I also don't want to print off all the topics they get <50% on as that could be loads of pdfs for some candidates.
You are right some questions have sub-questions like 1a,b,c,d so have the same pdf topic sheet. I'll have to find a workaround (like a merge or something) or manually just delete repeated file paths. But the above is the main priority right now.
Hope the above makes sense but if not please let me know.
There was one or two other small issues but i'll leave that for another time.
Your help is greatly appreciated - thanks.
Ash
Last edited by F1ash; 06-20-2018 at 11:14 PM.
I think I am getting the picture.
Let's look at paper 1
I will go down Column G
- Question 1b has 3% and is associated with nov-1-fp.pdf
- Question 3 has 33% and is associated with nov-3-fp.pdf
- Question 4 has 33% and is associated with nov-4-fp.pdf
- Question 5 has 20% and is associated with nov-5-fp.pdf
- Question 7 has 33% and is associated with nov-7-fp.pdf
These are the 5 PDF files you want.
Now for a hypothetical situation. On Paper 1 question 1c is 33% - if I take the first 5 then I stop at question 5. But I have a duplicate: nov-1-fp shows up twice. So there are only 4 PDFs identified. Do you want me to press on until there are 5 distinct PDF files?
Once the program identifies these 5 PDF files you want them printed out in hard copy.
Hi dflak
Yes that's pretty much it. If there's a duplicate then we want to "skip" the duplicate as I don't need the same file printing out twice.. so yes keep going til there are 5 distinct pdfs (where the score is <50%... in order top to bottom).
The other thing I was hoping to do was print the five pdfs 4-to-a-page (some of the other pdfs that I am using). These pdfs are all 8 pages long, so 4-to-a-page back to back means that these 8 page pdfs print out on one A4 paper (to save paper). For some reason when I click on 'click here to change printer' and then go into setup and set it to 'combination 4 to-a-page' it doesn't work and each page still prints on a separate page resulting in an 8 page document. That's 5x8=40 pages per candidate which is too much paper. So was wondering if you can adjust printer settings within the VBA code?
My network printer is already setup by default to back-to-back printing so I wouldn't need to adjust that, just the 4-to-a-page bit.
Thanks again for your help.
A
Last edited by F1ash; 06-21-2018 at 03:55 PM.
I should be able to line up the PDFs ready for printing by tomorrow.
Printing may be dicey. (1) I'll have to research it and (2) I don't have any A4 paper on this side of the pond to test it on. I'll see what I can do with letter-sized and see how that translates. I've done some printing 2-up with PDFs.
Yes the 4-to-a-page I would imagine won't be easy. I'm just frustrated as to why the 'setup' printer option is not dealing with this. It should and i'm thinking maybe it's a bug as the option is there.Originally Posted by dflak
Anyway thanks a bunch for all your help.
A
Great news, good news and bad news.
First the bad news. I have not been able to figure out a way to actually print the PDF files. I can get as far as the dialog box but SENDKEYS doesn't seem to work. So you will have to press ENTER on your own unless you can fix this. If you do figure out how to fix it, I'd like to see it.
Good news: I've been able to figure out a way to identify up to 5 PDFs. I use helper pivot tables to get the unique list of PDF files and the RAG associated with each. If the PDF file name isn't blank and the value is < 50% I print it. In the code, there is a constant called ADOBEPATH - set this to where your ADOBE Reader is.
Great News: the dialog box makes it easy to set up duplex printing. The dialog box remembers the 2 x 2 format for the sheets. I don't know why it doesn't remember Print Both Sides of Paper. If I could get SENDKEYS to work I would get there.
It's not totally automatic since you still have to click and punch a key, but it is close.
P.S. for purposes of testing I have a MsgBox instead of trying to print the PDFs - comment this out and uncomment the print command in.
Hi dflak
Thanks for this.
I've been working on it all night and with some help from Maud, realised its simpler than I first thought? Anyhow, it works a treat and only prints the first 5 files <50%. See below.
Many thanks for your efforts as I think I will use some of it for the printing issue.
Private Declare PtrSafe Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long
Public Sub Print_PDF() '(ByVal fname As String)
'--------------------------------------
'DECLARE AND SET VARIABLES
Dim cell As Range, rng As Range
Dim LastRow As Long, count As Long
LastRow = ActiveSheet.Cells(Rows.count, 3).End(xlUp).Row
count = 1
Set rng = Worksheets("Analysis").Range("D6:D" & LastRow)
'--------------------------------------
'PRINT PDF FILES IN COLUMN A
For Each cell In rng
If cell.Offset(0, -1) < 0.5 And count <= 5 Then
Call apiShellExecute(Application.hwnd, "print", cell.Value, vbNullString, vbNullString, 0)
count = count + 1
End If
Next cell
'--------------------------------------
'CLEANUP
Set cell = Nothing
Set rng = Nothing
End Sub
Forgot to mention, that the above does not print 5 DISTINCT pdf files - just the first 5 files that scored <50%.
I am trying to do this without pivot tables but may have to resort to that. But I think there's probably a way to make a simple modification to the code to check filenames and move on if already printed?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks