Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-14-2020, 08:17 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default Macro to print to pdf files showing pupil names

Hi



I have a spreadsheet that has a macro that when clicked it prints a single page to a pdf and names it to a number. I would like this to name it as a student name not a number and send to the printer at the same time. Printing 15 pdf's at a time is very time consuming!

TIA
Reply With Quote
  #2  
Old 12-14-2020, 08:28 AM
NoSparks NoSparks is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Care to show us your current macro ?
Reply With Quote
  #3  
Old 12-14-2020, 08:48 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default Current macro

Code:
Sub CombinedScienceExport()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range

Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = True Then
        FolderName = .SelectedItems(1) & ""
    Else
        Exit Sub
    End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Student Report Combined Science").Range("S2")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)

'''' Loop through DataValidation list
For Each c In inputRange
    r.Value = c.Value
    fName = c.Value
    '''' Save as pdf
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next c
Application.ScreenUpdating = True
End Sub

Last edited by macropod; 12-14-2020 at 02:53 PM. Reason: Added code tags
Reply With Quote
  #4  
Old 12-15-2020, 02:10 PM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sending to the printer should be easy enough - just add in activesheet.printout after the activesheet.exportasfixedformat. but this will print our each one every time - might be better to wrap an if around it to ask if the user wants to print

Code:
For Each c In inputRange
    r.Value = c.Value
    fName = c.Value
    '''' Save as pdf
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
    ActiveSheet.PrintOut
Next c
As for the name instead of a number it must be the fname part that is causing the issue - i am guessing that Cell S2 has the data validation number? but with out seeing the actual file it would be a guess - can you upload the actual file? or at least a test
Reply With Quote
  #5  
Old 12-16-2020, 01:53 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default file attached

Hi. Master attached,

VBA is above my skills! so any help is very appreciated. I was sent this spreadsheet!
Thankyou
Attached Files
File Type: xlsm School Report Y8 (MASTER).xlsm (386.4 KB, 5 views)
Reply With Quote
  #6  
Old 12-16-2020, 02:10 AM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

it looks like it is using UPN (what is that?), what name do you want the PDF saved as - just first name? I cant see a surname
Attached Images
File Type: jpg Screenshot 2020-12-16 090709.jpg (19.5 KB, 16 views)
Reply With Quote
  #7  
Old 12-16-2020, 02:12 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default

UPN is a Unique pupil number. the "Nameformat1" is the full pupil name, this is what i'd like them to be called please.

Thanks
Reply With Quote
  #8  
Old 12-16-2020, 03:22 AM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Try this - it errors after the 2 populated rows but i assume it will only run for the number of children in the list
Attached Files
File Type: xlsm School Report Y8 (MASTER)_Purfleet.xlsm (387.9 KB, 5 views)
Reply With Quote
  #9  
Old 12-17-2020, 03:12 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default adjustments

Quote:
Originally Posted by Purfleet View Post
Try this - it errors after the 2 populated rows but i assume it will only run for the number of children in the list
Hi.

I tried this and there was a formula error in the main table. and I couldn't get it to work.
I have attached again with a line of data in to show how it works.

Richard
Attached Files
File Type: xlsm School Report Y8 AP1 1(TEST1).xlsm (386.6 KB, 5 views)
Reply With Quote
  #10  
Old 12-17-2020, 03:48 AM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Okay, so i have added a question - do you want to print yes/no. Yes will print all to PDF AND paper copies, No will just do the PDFs

I have then changed the file name to be the name - might be worth having the UPN as well in the file name in case you have names that are the same? also worth adding the year so next years wont over write the previous? - Monkhouse, Bob - UPN 123456789 - year 8 2020.pdf - only a suggestion
Attached Files
File Type: xlsm School Report Y8 AP1 1(TEST1)_Purfleet.xlsm (386.6 KB, 5 views)
Reply With Quote
  #11  
Old 12-17-2020, 04:14 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Cool

Quote:
Originally Posted by Purfleet View Post
Okay, so i have added a question - do you want to print yes/no. Yes will print all to PDF AND paper copies, No will just do the PDFs

I have then changed the file name to be the name - might be worth having the UPN as well in the file name in case you have names that are the same? also worth adding the year so next years wont over write the previous? - Monkhouse, Bob - UPN 123456789 - year 8 2020.pdf - only a suggestion

Yes i agree that would be wise to change!

Thankyou so much for this. Works a treat and saves me a lot of time!
Much Appreciated
Reply With Quote
  #12  
Old 12-17-2020, 05:27 AM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Didnt know if you knew how to update the file name

Also forgot to say that the macro will exit when it see a 0 in the UPN column (no more children)
Attached Files
File Type: xlsm School Report Y8 AP1 1(TEST1)_Purfleet.xlsm (387.9 KB, 6 views)
Reply With Quote
  #13  
Old 12-17-2020, 05:40 AM
Richystab Richystab is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Novice
Macro to print to pdf files showing pupil names
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Talking

Quote:
Originally Posted by Purfleet View Post
Didnt know if you knew how to update the file name

Also forgot to say that the macro will exit when it see a 0 in the UPN column (no more children)
Hi .
Yeah i don't know how to do that!
Reply With Quote
  #14  
Old 12-17-2020, 03:16 PM
Purfleet Purfleet is offline Macro to print to pdf files showing pupil names Windows 10 Macro to print to pdf files showing pupil names Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Did you see that i did it on the last upload?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to print to pdf files showing pupil names Footers not showing in print preview nor will they print Lee_Lee Word 3 09-19-2017 05:06 AM
Macro to print to pdf files showing pupil names How do I import list of names from excel to print placecards? jrawls Word 2 08-17-2017 06:53 AM
Why 'print forms data' print pictures for DOCX files koolprasad2003 Word 4 05-09-2014 03:26 PM
Macro to print to pdf files showing pupil names VBA: How to debug.Print enumeration names? tinfanide Word VBA 4 01-27-2012 01:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft