This is very useful. I will use the code and practice designing my report. This will save time from creating individual pdf which took me 2 days to create manually for more than 1400 students. Thanks.
Killer dude, ty. I ported this to work for my application inside an hour. One thing I found was to ensure the cell dimensions in the output form will fit the incoming text. My pdf generator threw a fit and output garbage until I spent more time formatting the cells.
This is the visual basic code: 'Copyrighted of geekdecorder Sub ExportingPDF() 'Defining worksheets Dim detailsSheet As Worksheet Dim reportSheet As Worksheet Set reportSheet = ActiveWorkbook.Sheets("Format") Set detailsSheet = ActiveWorkbook.Sheets("Details") 'Looping the through each row For i = 2 To 20 'Assigning values SName = detailsSheet.Cells(i, 1) SCommerece = detailsSheet.Cells(i, 2) SEnglish = detailsSheet.Cells(i, 3) SMaths = detailsSheet.Cells(i, 4) STotal = detailsSheet.Cells(i, 5) 'Generating the output reportSheet.Cells(3, 2).Value = SName reportSheet.Cells(4, 2).Value = SCommerece reportSheet.Cells(5, 2).Value = SEnglish reportSheet.Cells(6, 2).Value = SMaths reportSheet.Cells(7, 2).Value = STotal 'Save the PDF file ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "D:\app\" & SName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False Next i End Sub
Geek Decoders - Power BI Learning Thank you for your help so far, please I want all this looping process but I want the data to be saved as Jpeg instead of PDF, kindly help me with the VBA Code please.
I have more than 100 rows data in excel sheet. I want to make pdf files from these data. I need 10 rows record on each pdf file. so for 100 rows data, there will make 10 pdf sheets consist of 10 rows records. Is there any loop which will make pdf 10 rows and then again next 10 rows and so on?
hello, this is helpful but i keep getting an error: 'Run-time error '91': Object variable or With block variable not set. I have used the exact code you have provided trying to recreate your reports exactly to test it. Are you able to help please?
Hi Geek thanks so much it is really helpful! I am using this to create invoices for the supplier. If there are multiple same supplier in one column, and I want to group them and export as one invoice, what can I do? Thank you!
You need order (a to z) the excel data first. Then add a if condition when reading suppier colums, so when changing the supplier you can ask the program to create a new PDF file
Great video, what if the name has space (first name and surname) how do you stop it saving with % signs in the name. Also - what if data in excel is too big for one row and you need to merge 2 rows to see the data - how do you include merged rows in the macros?
Thanks!! If i want to change the name of the exported PDF so that the extension is not in de documents name (now it is something similar to D\app\ & Sname), how do i do that?
I'm new to vba so have question on who you would you essentially accomplish the same thing but if you have multiple rows per Name. For example....column A would be Name, column B Subject, column C Score and column D Name_Email. So it would create a seperate pdf for each Name and seperate line for each subject and score. Ex. PDF1 -> includes multiple rows per subject... * Row_1/col_1 would reflect header of "Name"; row_1/col_2 header of "Subject"; row_1/col3 header of "Score" * Row_2/col_1 would reflect student's name ("Callum"); row_2/col_2 reflects subject ("Commerce"); row_3/col3 reflects score ("65") * Row_3/col_1 would reflect student's name ("Callum"); row_3/col_2 reflects subject ("English"); row_3/col3 reflects score ("63") * Row_4/col_1 would reflect student's name ("Callum"); row_4/col_2 reflects subject ("Math"); row_3/col3 reflects score ("24") Next PDF for Student=Fred may only contain 3 rows because perhaps they didn't take "Math". In short, instead of each pdf only containing one row,, would contain multiple rows, one for each subject and score,that apply to that student. Would want to name the created pdf's such so they could ultimately using something like Doug Robbins mail merge to send each student an email with their corresponding scores via pdf attachment. Thanks!
This is great, thank you! I have been looking for it for so long! One quick question, it is possible to create a PPT slide instead of a PDF file? Second question: Could be possible to have different image by each observation (each row)?
Hi, I have a picture in my file(from a url), but I can’t get it to appear in the design sheet, so it shows in the pdf. Do you have any tips to fix this?
Have you followed the exact steps? It's important that you follow each step clearly. There are no additional libraries involved, so the code should work.
Hi! Thanks for sharing your code. So I used it with my file and it is giving me a 'Run-time error '1004': Application-defined or object-defined error. Here is the code I used: Sub ExportingPDF() Dim detailsSheet As Worksheet Dim reportSheet As Worksheet Set reportSheet = ActiveWorkbook.Sheets("Template") Set detailsSheet = ActiveWorkbook.Sheets("Data") For i = 2 To 44 SApplicant_Number = detailsSheet.Cells(i, 1) SDescribe_current_position = detailsSheet.Cells(i, 2) SCareer_in_5to10years = detailsSheet.Cells(i, 3) SChallenges_related_to_leadership = detailsSheet.Cells(i, 4) SHow_workshop_will_help = detailsSheet.Cells(i, 5) SActions_towards_inclusion = detailsSheet.Cells(i, 6) reportSheet.Cells(3, 2).Value = SApplicant_Name reportSheet.Cells(4, 2).Value = SDescribe_current_position reportSheet.Cells(5, 2).Value = SCareer_in_5to10years reportSheet.Cells(6, 2).Value = SChallenges_related_to_leadership reportSheet.Cells(7, 2).Value = SHow_workshop_will_help reportSheet.Cells(8, 2).Value = SActions_towards_inclusion ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\WSLW" & SApplicant_Name, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False Next i End Sub Thanks for your help!
This is very useful. I will use the code and practice designing my report. This will save time from creating individual pdf which took me 2 days to create manually for more than 1400 students. Thanks.
AMAZING!!! This is EXACTLY what I needed, and was SO simple to follow!!! YOU just saved me HOURS!!!
Killer dude, ty. I ported this to work for my application inside an hour. One thing I found was to ensure the cell dimensions in the output form will fit the incoming text. My pdf generator threw a fit and output garbage until I spent more time formatting the cells.
Thanks a lot, great video
This helps a lot thankyou sir and thanks for information era
This is the visual basic code:
'Copyrighted of geekdecorder
Sub ExportingPDF()
'Defining worksheets
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet
Set reportSheet = ActiveWorkbook.Sheets("Format")
Set detailsSheet = ActiveWorkbook.Sheets("Details")
'Looping the through each row
For i = 2 To 20
'Assigning values
SName = detailsSheet.Cells(i, 1)
SCommerece = detailsSheet.Cells(i, 2)
SEnglish = detailsSheet.Cells(i, 3)
SMaths = detailsSheet.Cells(i, 4)
STotal = detailsSheet.Cells(i, 5)
'Generating the output
reportSheet.Cells(3, 2).Value = SName
reportSheet.Cells(4, 2).Value = SCommerece
reportSheet.Cells(5, 2).Value = SEnglish
reportSheet.Cells(6, 2).Value = SMaths
reportSheet.Cells(7, 2).Value = STotal
'Save the PDF file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\app\" & SName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next i
End Sub
When 😘 I define loop i am getting runtime error object required..how to fix it
Thank you for this.
Never mind lol I figured it out I like and subscribed to help your channel Thanks for the great code
great, but how about picture in the cell, could this also work?
Geek Decoders - Power BI Learning Thank you for your help so far, please I want all this looping process but I want the data to be saved as Jpeg instead of PDF, kindly help me with the VBA Code please.
Include a msgbox at the end to notify when the code is done
Is there a way to make the PDFs in landscape rather than portrait?
I have more than 100 rows data in excel sheet. I want to make pdf files from these data. I need 10 rows record on each pdf file. so for 100 rows data, there will make 10 pdf sheets consist of 10 rows records. Is there any loop which will make pdf 10 rows and then again next 10 rows and so on?
The name of create pdf file will automatically in serial number like 1,2,3...
Same problem ? Need help!
hello, this is helpful but i keep getting an error: 'Run-time error '91': Object variable or With block variable not set. I have used the exact code you have provided trying to recreate your reports exactly to test it. Are you able to help please?
i keep getting this error too
Is there a way I can attach the respective pdf in a column against the row using a hyperlink to the pdf just created?
Any answer to this question?
Hi Geek thanks so much it is really helpful! I am using this to create invoices for the supplier. If there are multiple same supplier in one column, and I want to group them and export as one invoice, what can I do? Thank you!
You need order (a to z) the excel data first. Then add a if condition when reading suppier colums, so when changing the supplier you can ask the program to create a new PDF file
Great video, what if the name has space (first name and surname) how do you stop it saving with % signs in the name. Also - what if data in excel is too big for one row and you need to merge 2 rows to see the data - how do you include merged rows in the macros?
Thanks!! If i want to change the name of the exported PDF so that the extension is not in de documents name (now it is something similar to D\app\ & Sname), how do i do that?
Good Evening Sir, I have one querry that if I have to print report card of particular student than what change in the code would be??
what if we have pictures on the row, can it works?
I'm new to vba so have question on who you would you essentially accomplish the same thing but if you have multiple rows per Name. For example....column A would be Name, column B Subject, column C Score and column D Name_Email. So it would create a seperate pdf for each Name and seperate line for each subject and score. Ex. PDF1 -> includes multiple rows per subject...
* Row_1/col_1 would reflect header of "Name"; row_1/col_2 header of "Subject"; row_1/col3 header of "Score"
* Row_2/col_1 would reflect student's name ("Callum"); row_2/col_2 reflects subject ("Commerce"); row_3/col3 reflects score ("65")
* Row_3/col_1 would reflect student's name ("Callum"); row_3/col_2 reflects subject ("English"); row_3/col3 reflects score ("63")
* Row_4/col_1 would reflect student's name ("Callum"); row_4/col_2 reflects subject ("Math"); row_3/col3 reflects score ("24")
Next PDF for Student=Fred may only contain 3 rows because perhaps they didn't take "Math".
In short, instead of each pdf only containing one row,, would contain multiple rows, one for each subject and score,that apply to that student.
Would want to name the created pdf's such so they could ultimately using something like Doug Robbins mail merge to send each student an email with their corresponding scores via pdf attachment.
Thanks!
Hi. Great video and thank you. What if you wanted to have all of the generated PDFs attach and send as an email to a specified user?
Then we need to connect this to outlook or other email client. I will see a possible code.
@@GeekDecodersPowerBILearning Thanks. I’ve made a few attempts at it with no luck.
how to insert separate images to each pdf files?
Thanks really useful.. I want all 20 row sheet to be created as single pdf
This is very helpful! Is there a way to make the i more dynamic? Currently it's defined as i = 2 to 20. What if the number of rows constantly changes?
Then you need to find the numbee of rows from beginning of the code or loop until
a blank cell value.
Can you share code how to do that?
May I please have the code?
@@GeekDecodersPowerBILearning how do you loop only populated rows since the file will change number of rows?
This is great, thank you! I have been looking for it for so long! One quick question, it is possible to create a PPT slide instead of a PDF file? Second question: Could be possible to have different image by each observation (each row)?
Thank you 👍
Hi, I have a picture in my file(from a url), but I can’t get it to appear in the design sheet, so it shows in the pdf. Do you have any tips to fix this?
Can you add it the image to excel sheet it self..
@@GeekDecodersPowerBILearning The file has 500 rows and each product has a image set to it, via url & a vba formula
Just found your video! Any idea why I keep getting "error while printing?"
Have you followed the exact steps? It's important that you follow each step clearly. There are no additional libraries involved, so the code should work.
How to generate a single pdf for all reports rather than multiple PDFs?
Could you please help
Get the pdf printing code outside of the loop.
@@GeekDecodersPowerBILearning if we do in that way only the last name PDF is generated. I want all the persons PDf friles in One PDF file.
@@GeekDecodersPowerBILearning can you show us how? in the code
@@GeekDecodersPowerBILearning Also need this, all reports in just 1 pdf file with multiple pages
Hi! Thanks for sharing your code. So I used it with my file and it is giving me a 'Run-time error '1004': Application-defined or object-defined error. Here is the code I used:
Sub ExportingPDF()
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet
Set reportSheet = ActiveWorkbook.Sheets("Template")
Set detailsSheet = ActiveWorkbook.Sheets("Data")
For i = 2 To 44
SApplicant_Number = detailsSheet.Cells(i, 1)
SDescribe_current_position = detailsSheet.Cells(i, 2)
SCareer_in_5to10years = detailsSheet.Cells(i, 3)
SChallenges_related_to_leadership = detailsSheet.Cells(i, 4)
SHow_workshop_will_help = detailsSheet.Cells(i, 5)
SActions_towards_inclusion = detailsSheet.Cells(i, 6)
reportSheet.Cells(3, 2).Value = SApplicant_Name
reportSheet.Cells(4, 2).Value = SDescribe_current_position
reportSheet.Cells(5, 2).Value = SCareer_in_5to10years
reportSheet.Cells(6, 2).Value = SChallenges_related_to_leadership
reportSheet.Cells(7, 2).Value = SHow_workshop_will_help
reportSheet.Cells(8, 2).Value = SActions_towards_inclusion
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\WSLW" & SApplicant_Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next i
End Sub
Thanks for your help!
Hi, I need your help please.
Hi Sir, I am trying to create pdf per your video but I get error. Can I get your email address to share my file with you for assistance? Thank you
Hi Benjamin, I might not have time to check it. Can you try following the exact code that I have added. And what is the error you get?
Hi
Hi