Web Analytics


This is part two of a new series I’m writing, teaching you how to get more functionality out of your Excel. If you are not familiar with Macros I suggest you read the first part, before beginning on this one.

In Part Two…

In this part we are starting off  using some of the commands you should know, and then create a Macro that decide filename, which folder to use, before exporting the document to PDF-format. I am also going to show you how to create a button to trigger the macro.

As in the first part, I am using Office 2010. Even though the code and programming are the same in older versions of Excel, the images and buttons may differ from what you see on your screen.

Enough Chit Chat, let’s get this party started…

Quick Introduction to VBA and Excel

As mentioned earlier (in the first Part) VBA is Visual Basic Light. The programming language is object-oriented, meaning we create code that relates to objects like Cells, Sheets and Workbooks. Each Object has many functions (or actions) and each function has settings we can use; like Object Name, Value, Id etc. Don’t worry about remembering them all, VBA has a built-in tool that show you a list of available settings for each function.

In addition to the objects – we are going to use what we call Variables. Variables temporarily store data we are going to use later (think of it as small post-it notes). To keep track of our variables we declare them (give them names). This is usually done in the Macro Header. We will get back to this later.

One last thing: In VBA we don’t use the name Macro. Instead we call it SUBS. The Sub has a begin-code and an end-code, which declare the Macro Name and End:

Sub EmptyIt()

… Content Here …

End Sub

Some of the functions we are going to use

Every function we use will trigger an event inside the Excel workbooks. When using Excel you maneuver within Cells and Sheets. Each time you Select a Cell it becomes Active. Instead of Clicking on a cell, we can tell the macro to do it for us. First, we tell the macro which Cell to activate, using the function RANGE (Cell reference enclosed in parentheses and quotes).  Then we tell it what we want to do; “click it”. The code looks like this:

Range(“B2”).Select  (or Range(“B2”).Activate)

To WRITE something in that Cell we have to use a different function: ActiveCell.

ActiveCell.Value =”Write this into the Cell”

To READ the contents of the Cell we need to use a variable. As mentioned earlier the variable has to be declared (given a name). Then we can write something in it. The code would look like this:

Dim CellContent
CellContent = ActiveCell.Value

Then we can use that information later on – even if the content of that cell should change.

To Move between workbooks or Sheets we use the functions called: Sheets or Workbook, like this:

Workbook(“prices.xls”).Activate
Sheets(“Calculations”).Activate

Creating a Macro

Now we should be ready to create our PDF Macro. For this lesson I have created a document containing a simplified order form. You can download it at the end of this article. The workbook contain the macros used in these articles. The first macro has been created using the RECORD MACRO function.

To make the macro as versatile as possible we are going to create a new worksheet: “Settings”. This Sheet is going to store information like default filename, default save-to folder(s), and order handler. Later we can add functionality like Open After Save, Overwrite existing … etc.

This will allow other users to change settings without having to edit the macro.

  1. Rename your main sheet to “Form” (Right click the sheet tab, choose Rename).
  2. Create a new worksheet within your document, rename it: “Settings”.
  3. In the workbook add some Labels to describe what information to store, and fill in the default values. (See example).
  4. Return to the Form Sheet (The main sheet)
  5. To create a new Macro, Click on the Macro Icon. In the Text box, type in the name for the Macro: “CreatePDF” and Click the Create-Button.

In the VBA-Editor you will now see an empty Macro, looking like this:

Sub ToPDF()

End Sub

All our code will go in-between these two lines. First we declare our post-it notes (variables):

Dim Name As String
Dim Stamp As String
Dim WhereTo As String
Dim sFileName As String

Now we need to retrieve data from the Settings Sheet, add the following lines:

Sheets("Settings").Activate
Range("B5").Activate
WhereTo = ActiveCell.Value
Range("B7").Activate
Stamp = ActiveCell.Value
If Stamp = "" Then Stamp = Date

The Cell references B5 and B7 may differ from your settings sheet. Use the apropriate cell references.

Then we will retrieve the customer name from the form:

Sheets("Form").Activate
Range("E5").Activate
Name = ActiveCell.Value

Next, we put the data to good use, by creating the filename:

sFileName =  WhereTo & Name & "_" & Stamp & ".pdf"

“sFileName”, “WhereTo”, “Name” and “Stamp” are the variables we use. The “=” sign means that the following information is to be stored. We link the information from each variable using the “&”-sign. Text is added between the quotes. The finished variable could look like this: sFileName = "C:\Sales\Thomas_09092010.pdf".

Last Thing we do; add the Save To PDF Command:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName, Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Create Button

Okay, before we test that everything works, let’s make a button to trigger the macro. In order to do this (in Office 2010) we need to activate a hidden Tab on the ribbon (older Office, look for “Form Controls”).

  1. Click on the Green File Tab
  2. Choose Options
  3. In the Options Dialogue, click “Customize Ribbon”
  4. In the List Window to the right, there is a list of all the available Ribbons.
    Locate the one Called: “Developer” and Check the box next to it
  5. Click OK to Save

Activate (Open) the Form Sheet, and click the Developer Tab in the Ribbon. In the middle of the Ribbon there is an icon called “Insert”. Click the little black arrow below it.

In the pull down menu, choose the first element that looks like a little grey button. With the crosshatch pointer, draw a button somewhere in the Sheet. This will trigger the Form Button Event Menu.

Choose the Macro you would like to run, and Click OK.

Now that the button has been created, we need to edit the Button label. With the button still activated, click the button text. This will allow you to type directly on the button. Write something clever on it, then press Enter.

Click somewhere on the sheet, to disable the button edit function, and to activate the button.

Now, all that is left, is to save your document. Make sure you save it as a Macro Activated Excel Document (xlsm).

DOWNLOAD

This File: Documents contains both an 2010-Version (xlsm)  and the XP-Version (xls).

Next Part

Part 3, will take this macro functionality longer. We will add Error handling, and make sure we don’t overwrite existing files. We will add more settings to the “Settings” sheet. Then we will add another Macro just for fun.

About Thomas

Computer geek from the age of 7, which amounts to 30 years of computer experience. From the early days (when every computer company had their own OS) of DOS, Windows 1.0 through Seven...

Free PC tips by email

Search Windows Guides




Comments

10 thoughts on “Advanced Macros in Excel—Save to PDF [Part 2 of 2]”

  1. Pingback: RealTime - Questions: "Excel Date Range Formula or Macro?"
  2. Peternkatz says:

    Where is part 3?

    1. Thomas says:

      still in the works i’m afraid.
      It will soon be ready

  3. rajiv sahoo says:

    Downloaded the file , unable to creat pdf,  runtime error 1004, plz help,

    1. Thomas says:

      Which version are you trying to run ?
      The 2010 edition or the XP edition ?

      I have also discovered a glitch in the macro code.
      Edit the macro: Create PDF

      Locate the line 25:
      sFileName = “C:UsersMusikkDocumentsInnotec” & Date & ” ” & aWB & “.pdf”

      Swap  with this:
       
      sFileName = WhereTo & Date & ” ” & aWB & “.pdf”
       

      Save and Exit the editor.
      In the Document open the Settings Sheet and make sure the Save To Path exist on your computer.
       

  4. Pwned555 says:

    There are a few Issues with the original code, and one big problem that is giving you this error.  First I will give you the original code and then explain what is wrong and give you code that should work.

    Sub CreatePDF()
     
    Dim Name As String
    Dim Stamp As String
    Dim WhereTo As String
    Dim sFileName As String
     
    ‘ Retrieve settings from the Form
    Sheets(“Settings”).Activate
    Range(“B5”).Activate
    WhereTo = ActiveCell.Value
    Range(“B7”).Activate
    Stamp = ActiveCell.Value

    ‘ Check if Stamp-field has any value at all
    ‘ if not, add the current date.
    If Stamp = “” Then Stamp = Date

    ‘Then we will retrieve the customer name from the form:
    Sheets(“Form”).Activate
    Range(“E5”).Activate
    Name = ActiveCell.Value

    ‘ Assemble the filename
         sFileName = “C:UsersMusikkDocumentsInnotec” & Date & ” ” & aWB & “.pdf”
     
    ‘ Save the File as PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            sFileName, Quality _
            :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
       
    ‘ Make sure we open the Order Form upon Exit
        Sheets(“Form”).Activate

    End Sub

    1) The CREATE PDF Button on the first page points to the wrong macro (At least for me it does, seems like you’ve already changed this if you’re getting the error you are).  If you go Right Click->Assign Macro it should be assigned to CreatePDF, not Tester.

    2) The employee name given on the settings sheet is not used anywhere , another variable should be added to use this value if we are going to provide it.

    3) As stated “C:UsersMusikkDocumentsInnotec” should be changed to WhereTo

    4) Since aWB hasn’t been declared anywhere (No Dim aWB as String) or assigned (No aWB = …) it has no value meaning every .pdf created is going to end in a black space (because of the blank space in sFileName after & Date).  This isn’t causing the error it is just something that should be changed.

    5) Finally Date is the main problem.  For today (Dec 13, 2011) It returns a value of 12/13/2011.  If you’ve ever saved a file before you’ll know that / slash characters are not allowed.  So in order to add the date, we have to take the date that VBA gives us (12/13/2011) and format it to something we can use.  In order to do this we can use this code Format(CDate(Date), “MMM-DD-YYYY”).  This says to change the value of a Date so that it displays 3 characters for the month, ‘-‘, 2 characters for the day, ‘-‘, 4 characters for the year.  The example above would now be converted to Dec-13-2011.  You can mess around with the “MMM-DD-YYYY” part of the Format to change it to whatever works best for you.

    Here is code that should work for you.  If you give it a company name of ‘Green Energy’, Employee Name ‘Fred’, Path ‘C:WorkingTesting’ It will create a file named ‘Green Energy_Fred_Dec-13-2011.pdf’ in the Folder C:WorkingTesting

    Sub CreatePDF()
     
    Dim customerName As String
    Dim employeeName As String
    Dim Stamp As String
    Dim WhereTo As String
    Dim sFileName As String
     
    ‘ Retrieve information from settings sheet
    Sheets(“Settings”).Activate
    Range(“B3”).Activate
    employeeName = ActiveCell.Value
    Range(“B5”).Activate
    WhereTo = ActiveCell.Value
    Range(“B7”).Activate
    Stamp = ActiveCell.Value

    ‘ Check if Stamp-field has any value at all
    ‘ if not, add the current date.
    If Stamp = “” Then Stamp = Date

    ‘Then we will retrieve the customer name from the form:
    Sheets(“Form”).Activate
    Range(“E5”).Activate
    customerName = ActiveCell.Value

    ‘ Assemble the filename
         sFileName = WhereTo & customerName & “_” & employeeName & “_” & Format(CDate(Date), “MMM-DD-YYYY”) & “.pdf”
     
    ‘ Save the File as PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            sFileName, Quality _
            :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
       
    ‘ Make sure we open the Order Form upon Exit
        Sheets(“Form”).Activate

    End Sub

    Hope this helps!!!

  5. Bilal says:

    I m also getting the same error 438 in 2003, focusing to activesheet.export…. command line

  6. JoeI says:

    I have a PDF reader, is this an offer of a useful document or a scam to load third party software?

  7. ricky says:

    Just stumbled upon this and its very useful. How do I get this to work with Adobe Acrobat? I don’t have pdf maker.

    1. Thomas says:

      Office 2010 has a built-in PDF-maker, which is what we use to create the PDF document.
      From then on you may use any PDF Reader Software.

Comments are closed.


Computer tips in your inbox
Sign up for the Windows Guides newsletter to get PC tips and access to free Windows books (More details)

Subscribe now
Popular Guides

See which sites have been visited on your PC (even if private browsing mode is used)

Create a Windows 7 System Repair Disc

Best Free Anti-malware

Hibernate vs. Sleep vs. Shut-Down

i3, i5, and i7; Dual, Quad, Hexa Core Processors. How to they Differ?

Intel's Ivy Bridge Processor: new Features

Windows Guides on Facebook