<div>One of the features in Excel is the ability to convert Excel files to PDF format. In this article we explain how to create a button in the excel worksheet, which when clicked generates a PDF file of that sheet.</div>
<div>Let us take the picture below as example to take a PDF and save it in current folder.</div>
<div style=”text-align: center;”><img class=”aligncenter size-full wp-image-1187″ title=”Selected file for PDF” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Selected-file-for-PDF.png” alt=”” width=”550″ height=”230″ /></div>
<h3>Below are the steps to get a button, which converts the excel file to a PDF file:</h3>
<h5><img class=”alignnone size-full wp-image-1188″ title=”1″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/1.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Select </span><strong style=”font-size: 14px;”>Developer</strong><span style=”font-size: 14px; font-weight: normal;”>, select </span><strong style=”font-size: 14px;”>Insert</strong><span style=”font-size: 14px; font-weight: normal;”> from </span><strong style=”font-size: 14px;”>Control</strong><span style=”font-size: 14px; font-weight: normal;”> group and select </span><strong style=”font-size: 14px;”>Button</strong><span style=”font-size: 14px; font-weight: normal;”> from </span><strong style=”font-size: 14px;”>Form Controls</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><img class=”aligncenter size-full wp-image-1189″ title=”Insert Button” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Insert-Button.png” alt=””
width=”331″ height=”184″ /></div>
<h5><img class=”alignnone size-full wp-image-1190″ title=”2″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/2.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 15px; font-weight: normal;”>Drag the control on the spreadsheet, a window appears.</span></h5>
<h5><img class=”alignnone size-full wp-image-1191″ title=”3″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/3.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Enter the name of the macro in the tab provided for </span><strong style=”font-size: 14px;”>Macro name</strong><span style=”font-size: 14px; font-weight: normal;”> and click </span><strong style=”font-size: 14px;”>OK</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><img class=”aligncenter size-full wp-image-1199″ title=”Assign Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-Macro.png” alt=”” width=”381″ height=”366″ /></div>
<h5><img class=”alignnone size-full wp-image-1192″ title=”4″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/4.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>To edit the name of the button, Right click on the button and select </span><strong style=”font-size: 14px;”>Edit Text</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1200″ title=”Edit Text” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Edit-Text.png” alt=”” width=”293″ height=”233″ /> </span></div>
<h5><img class=”alignnone size-full wp-image-1193″ title=”5″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/5.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Right click on the button and select </span><strong style=”font-size: 14px;”>Assign Macro</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1201″ title=”Assign Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-Macro1.png” alt=”” width=”381″ height=”366″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1194″ title=”6″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/6.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Select the </span><strong style=”font-size: 14px;”>Macro name</strong><span style=”font-size: 14px; font-weight: normal;”> and select </span><strong style=”font-size: 14px;”>New</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1202″ title=”Assign New Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-New-Macro.png” alt=”” width=”391″ height=”373″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1195″ title=”7″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/7.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>A Microsoft Visual Basic window appears.</span></h5>
<div style=”text-align: center;”><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1209″ title=”Visual Basic Application” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Visual-Basic-Application2.png” alt=”” width=”539″ height=”262″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1196″ title=”8″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/8.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Enter the below code in the application where the arrow is pointed, under ‘</span><strong style=”font-size: 14px;”>Sub CreatePDF()’.</strong></h5>
<blockquote>
<div>Sub CreatePDF()</div>
<div>ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _</div>
<div>”Generate Report.pdf”, Quality:=xlQualityStandard, _</div>
<div>IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False</div>
<div>End Sub</div></blockquote>
<div style=”text-align: center;”><img class=”aligncenter size-full wp-image-1205″ title=”Code to Generate PDF” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Code-to-Generate-PDF.png”
alt=”” width=”600″ height=”200″ /></div>
<h5><img class=”alignnone size-full wp-image-1197″ style=”font-size: 11px; font-weight: bold;” title=”9″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/9.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Close the Microsoft Visual Basic application, now just click on the </span><strong style=”font-size: 14px;”>CreatePDF</strong><span style=”font-size: 14px; font-weight: normal;”> button. That’s all a PDF will be generated and saved in the same folder.</span></h5>
<h5><img class=”alignnone size-full wp-image-1206″ title=”10″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/101.png” alt=”” width=”50″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>If we want to change the name of the PDF, we can change it in the code. For example in the second line we have “Generate Report.pdf”, change it to “</span><strong style=”font-size: 14px;”>Create Report.pdf</strong><span style=”font-size: 14px; font-weight: normal;”>”.</span></h5>
<div></div>
<div><strong>————————————————————————————————————–</strong></div>
<strong>
<div>Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in <a href=”../../chaitanya_sagar/”>Excel Modeling</a>, <a href=”../../excel-dashboard-reporting.php”>Excel Dashboards</a>, <a href=”../../cost-profitability-analysis.php”>Profitability Analysis</a>, <a href=”../../custom-invoices.php”>Excel Invoices</a>and <a href=”../../custom-invoices.php”>Excel Bid sheets</a>. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.</div>
</strong>
<div></div>