Copyright infringement

How to save a report as a PDF without the spreadsheet in Excel

There are two easy ways to save an active Microsoft Excel sheet to a PDF file: manually and with a macro. Here’s how to do both.

Save as PDF button

Photo: iStock/jurgenfr

We used to print charts and other analysis reports to share with others. Today, we can bypass the print route by sharing reports electronically. However, sending an entire Microsoft Excel workbook file is usually not appropriate; You rarely want to share source data along with reports. One way to share the report is just to save that paper in a PDF and then share it electronically – no fuss, no fuss, no waiting for a hard copy to be routed through an outdated internal mail system. In this article, I will show you how easy it is to save only the active sheet to a PDF using the interface and running a macro.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I am using Microsoft 365 on Windows 10 64 bit. You can work with your own data or download the .xlsx demo file. This article is not suitable for browser version nor xls format. Previous.

report vs. spreadsheet

If you’re like many users, you separate data from reports. This way, you can quickly print a report without sharing the source data as well. It’s fast and clean. Figure A It appears like a reporting sheet. If you submit the entire work electronically, others will have access to the source data, which may be confidential or proprietary. Besides, including data in the report is distracting.

Figure A


Generally, we share reports, but not source data.

As mentioned, you can print the report and route it appropriately, but instead, let’s save it to a PDF that you can share via email, on OneDrive, or attach via a chat window or team meeting. We won’t spend time creating the report sheet, so we can focus on saving the report sheet. (If you are using the demo file, you can view the source data on the Layer tab.)

How to manually save as PDF in Excel

Saving the report sheet to a PDF file is the easy part. From the report sheet, click the File tab. In the left pane, choose Save As or Save a Copy (OneDrive). In the resulting dialog, choose PDF (*.pdf) from the Save (*.pdf) dropdown list.Figure B), then click Save. You can also change the folder or file name if necessary. You can see the output PDF file in . format Figure C. If the workbook has more sheets (and more will), this operation saves only the active sheet.

Figure B


Save the paper as a PDF.

Figure C


The PDF file contains data from the active sheet only.

How to use a macro to save as PDF in Excel

You probably didn’t need me to show you how to save a sheet to a PDF but knowing that the task only saves the current sheet might be new to you. If you do this often, you may benefit from a macro that saves the current sheet to a PDF – it only takes one line of code. To add the code, do the following:

  1. Press Alt + F11 to open the Visual Basic Editor (VBE).
  2. Find Sheet2 (Stratified) in the Project Explorer to the left (update the sheet name if you are working with your own file). Double click to open the sheet module.
  3. Enter the code in Menu A in sheet unit (figure d). Do not copy from this web page. You can import the .bas demo file or enter the code manually.

Menu A

Sub SaveReportPDF ()

Save the report sheet (Sheet 2) as a pdf file.

ExportAsFixedFormat xlTypePDF

End Sub

figure d


Enter the sub-action in Menu A.

Next, go back to Excel and save the file as a macro-enabled file. To run the macro, click the Developer tab, click Macros in the Code group, then choose the SaveReportPDF sub-action and click Run.

If the Developer tab is not visible, you can find instructions in the article How to add Office macros to the QAT toolbar for quick access. (See the first paragraph that follows Figure C.)

The short procedure does not bear the bare minimum and makes a lot of decisions for you. As it stands, this action saves the active sheet (layers) in a PDF using the Excel file name to name the PDF, and saves that file in the same folder. He takes great care of you, but you may need to tweak things a bit. The full command form is

w.Export AsFixedFormat (TypeAnd File nameAnd QualityAnd IncludeDocPropertiesAnd Ignore print areasAnd FromAnd toAnd OpenAfterPublishAnd FixedFormatExtClassPtr)

where only Type wanted. In our case, we used the fixed xlTypePDF and left OpenAfterPublish As a default, true. The name of the demo workbook is ExcelPDF_demo.xls, so the macro saves the stratification sheet in a PDF file called ExcelPDF_demo.pdf in the same folder as the workbook file, and then opens the newly saved PDF file. You can explore more other parameters if you think you might need them.

You may want to control the name and folder where the macro saves the file. If so, you will probably want to add some error handling code. In addition, the w The expression, which must refer to a worksheet object, is not necessary and, when not explicitly referred to, defaults to the active sheet. This means that you have to go to the class sheet to execute the macro. You can easily select the worksheet with Dim ws As Worksheet line and define it accordingly.

This article shows you how to save the active sheet as a PDF and I have introduced a simple macro that does the same thing. You can, if necessary, add to this macro to specify the default file and folder name.

see also