Every data-oriented process has an application flow — a succession of applications that take the data from creation to end-user. Sometimes a dataset is touched by only one application, such as when you're creating a report and presenting it in Excel. In many cases, however, data is moved from a database such as Microsoft Access, analyzed and aggregated in Excel, and then distributed via a Word document, PowerPoint presentation, or even e-mail. In this Part, we look at some of the useful macros you can implement to have Excel integrate with other Office applications.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 92: Running an Access Query from Excel
Here's a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the background and output the results into Excel.
How it works
In this macro, you point Excel to an Access database and pull data from an existing Access query. You then store that query in a Recordset object, which you can use to populate your Excel spreadsheet.
Because you are automating Access, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
In addition to the Access Object Library, you need to set a reference to Microsoft DAO XX Object Library, where the XX is the version number. Note that you may see multiple versions of this library in the Reference dialog box. You should generally select the latest version of the Microsoft DAO Library available. While still in the Reference dialog box, select the check box next to the entry.
Sub Macro92()
‘Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
‘Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(“C:TempYourAccessDatabse.accdb”)
Set MyQueryDef = MyDatabase.QueryDefs(“Your Query Name”)
‘Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
‘Step 4: Clear previous contents
Sheets(“Sheet1”).Select
ActiveSheet.Range(“A6:K10000”).ClearContents
‘Step 5: Copy the recordset to Excel
ActiveSheet.Range(“A7”).CopyFromRecordset MyRecordset
‘Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
End Sub
1. Step 1 declares the necessary variables. The MyDatabase object variable exposes your Access database application via the DAO Object Library. MyQueryDef is also an object variable that serves as a memory container for the target query. MyRecordset is a Recordset object that holds the results of the data pull. In addition to these, the i integer variable is used to add column headings.
2. Step 2 specifies the database that holds your target query as well as which query will be run. Assigning the query to a QueryDef object allows you to essentially open the query in memory.
3. Step 3 literally runs the query in memory. The results of the query are then stored into the MyRecordset object. After the results are in a recordset, you can output the data to Excel.
4. Step 4 prepares for the recordset output by clearing the output area. This ensures no residual data is left from previous data pulls.
5. This step uses Excel's CopyFromRecordset method to get the returned dataset into the spreadsheet. In this example, the macro copies the data in the MyRecordset object onto Sheet1 at cell A7.
6. Finally, you enumerate through each field in the recordset to automatically get the name of each header and enter it into Excel.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code into the newly created module.
Macro 93: Running an Access Macro from Excel
You can run Access macros from Excel, using automation to fire the macro without opening Access. This technique can be useful not only for running those epic macros that involve a multistep series of 20 queries, but can also come in handy for everyday tasks like outputting a Access data to an Excel file.
How it works
The following macro is a simple way to trigger an Access macro programmatically.
Note that you will need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
Sub Macro93()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase _
(“C:TempYourAccessDatabse.accdb”)
‘Step 3: Open the target report and send to Word
With AC
.DoCmd.RunMacro “MyMacro”
.Quit
End With
End Sub
1. The first thing the macro does is declare the AC object variable. This variable exposes the Access database application library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and open the database that houses the target macro.
3. Step 3 runs the appropriate macro and closes the database.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code into the newly created module.
Macro 94: Opening an Access Report from Excel
Access reports allow you to build professional looking reports that have a clean PDF-style look and feel. If you run and distribute a great deal of Access reports, the following macro can help automate your processes.
How it works
This macro demonstrates how you can open your Access reports right from Excel. The appealing thing about this technique is that you don't see Access at all; the report goes straight to a Word rich text file.
Note that you will need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where XX is your version of Access. Select the check box next to the entry.
Sub Macro94()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase _
(“C:TempYourAccessDatabase.accdb”)
‘Step 3: Open the target report as a Word rich text file
With AC
.DoCmd.OpenReport “Revenue Report”, acViewPreview
.DoCmd.RunCommand acCmdOutputToRTF
.Quit
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database application library.
2. In Step 2, the AC variable starts a new instance of Microsoft Access and opens the database that houses the target report.
3. Step 3 simply runs the appropriate report, sending the output to a Microsoft Word rich text file. After the file is output, the database closes.
How to use it
To implement this macro, you can copy and paste it into a standard module
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code into the newly created module.
Macro 95: Opening an Access Form from Excel
In some instances, you or your clients may need to switch focus to an Access form. This example demonstrates how you can open an Access form from Excel.
How it works
With this macro, you point Excel to an Access database and trigger a specific Access form to open.
Because you are automating Access, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box activates. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
Sub Macro95()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase _
(“C:TempYourAccessDatabase.accdb”)
‘Step 3: Open the target form and make Access visible
With AC
.DoCmd.OpenForm “MainForm”, acNormal
.Visible = True
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database application library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and opens the database that houses the target form.
3. Step 3 opens the appropriate form. The Access form opens in a new Microsoft Access window. Note that you are not closing the database in the last line of Step 3 (as with the previous macros). Instead, you make the Access application visible.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click project/workbook name in the Project window
3. Choose Insert⇒Module.
4. Type or paste the code into the newly created module.
Macro 96: Compacting an Access Database from Excel
During your integrated processes, you may routinely increase or decrease the number of records and tables in your database. As time goes on, you may notice that your Access database gets bigger. This is because Access does not release file space. All the space needed for the data you move in and out of your database is held by your Access file, regardless of whether the data is still there. In that light, it's critical that you run Compact and Repair on your Access database regularly. Among other things, running Compact and Repair defragments your database, releasing any unused space and ensuring your database does not grow to an unmanageable size. Office automation enables you to Compact and Repair your databases right from code.
How it works
When you compact and repair an Access database manually, it seems as though Access compresses your original database; this is not the case. Access is really doing nothing more than creating a copy of your Access database (minus the empty file space) and deleting the old file.
This macro essentially mimics those actions in order to programmatically Compact and Repair an Access application.
Note that in order to use this code, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
Sub Macro96()
‘Step 1: Declare your variables
Dim OriginalFile As String
Dim BackupFile As String
Dim TempFile As String
‘Step 2: Identify the target database assign file paths
OriginalFile = “C:TempMyDatabase.accdb”
BackupFile = “C:TempMyDatabaseBackup.accdb”
TempFile = “C:TempMyDatabaseTemporary.accdb”
‘Step 3: Make a backup copy of database
FileCopy OriginalFile, BackupFile
‘Step 4: Perform the compact and repair
DBEngine.CompactDatabase OriginalFile, TempFile
‘Step 5: Delete the old database
Kill OriginalFile
‘Step 6: Rename the temporary database to the old database name
Name TempFile As OriginalFile
End Sub
1. Step 1 declares three string variables that hold filenames.
2. Step 2 then assigns each of the string variables a filename. The OriginalFile variable is assigned the file path and name of the target database. The BackupFile variable is assigned the file path and name of a backup file we will create during this procedure. The TempFile variable is assigned the file path and name of a temporary file we create during this procedure.
3. Step 3 uses the FileCopy function to make a backup of the OriginalFile (the target database). Although this step is not necessary for the Compact and Repair procedure, it's generally a good practice to make a backup of your database before running this level of VBA on it.
4. Step 4 executes the Compact and Repair, specifying the original database and specifying the file path of the temporary database.
5. At this point, you have two copies of your database: the original database and a second database, which is a copy of your original without the empty file space. Step 5 deletes the original database, leaving you with the copy.
6. Step 6 simply renames the temporary file, giving it the name of your original database. This leaves you with a database that is compact and optimized.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code.
Macro 97: Sending Excel Data to a Word Document
If you find that you are constantly copying and pasting data into Microsoft Word, you can use a macro to automate this task.
How it works
Before walking through the macro, it's important to go over a few set-up steps.
To get ready for a process like this, you must have a template Word document already created. In that document, create a bookmark tagging the location where you want your Excel data to be copied.
To create a bookmark in a Word document, place your cursor where you want the bookmark, select the Insert tab, and select Bookmark (found under the Links group). This activates the Bookmark dialog box where you assign a name for your bookmark. After the name has been assigned, click the Add button.
One of the sample files for this Part is a document called PasteTable.docx. This document is a simple template that contains one bookmark called DataTableHere. In this sample code, you copy a range to that PasteTable.docx template, using the DataTableHere bookmark to specify where to paste the copied range.
You also need to set a reference to the Microsoft Word Object Library. To do so, open the Visual Basic Editor in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Word XX Object Library, where the XX is your version of Word. Select the check box next to the entry.
Sub Macro97()
‘Step 1: Declare your variables
Dim MyRange As Excel.Range
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim WdRange As Word.Range
‘Step 2: Copy the defined range
Sheets(“Revenue Table”).Range(“B4:F10”).Copy
‘Step 3: Open the target Word document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Open _
(ThisWorkbook.Path & “” & “PasteTable.docx”)
wd.Visible = True
‘Step 4: Set focus on the target bookmark
Set WdRange = wdDoc.Bookmarks(“DataTableHere”).Range
‘Step 5: Delete the old table and paste new
On Error Resume Next
WdRange.Tables(1).Delete
WdRange.Paste ‘paste in the table
‘Step 6: Adjust column widths
WdRange.Tables(1).Columns.SetWidth _
(MyRange.Width / MyRange.Columns.Count), wdAdjustSameWidth
‘Step 7: Reinsert the bookmark
wdDoc.Bookmarks.Add “DataTableHere”, WdRange
‘Step 8: Memory cleanup
Set wd = Nothing
Set wdDoc = Nothing
Set WdRange = Nothing
End Sub
1. Step 1 declares four variables: MyRange contains the target Excel range you want copied; wd is an object variable that exposes the Word Application object; wdDoc is an object variable that exposes the Word Document object; and wdRange is an object variable that exposes the Word Range object.
2. Step 2 copies a range from the Revenue Table worksheet. In this example, the range is hard-coded, but we can always make this range into something more variable.
3. Step 3 opens an existing target Word document that serves as a template. Note that we are setting the Visible property of the Word application to True. This ensures that we can see the action in Word as the code runs.
4. Step 4 uses Word's Range object to set focus on the target bookmark. This essentially selects the bookmark as a range, allowing you to take actions in that range.
5. Step 5 deletes any table that may exist within the bookmark, and then pastes the copied Excel range. If we don't delete any existing tables first, the copied range is appended to the existing data.
6. When you're pasting an Excel range into a Word document, the column widths don't always fit the content in the cells appropriately. Step 6 fixes this issue by adjusting the column widths. Here, each column's width is set to a number that equals the total width of the table divided by the number of columns in the table.
7. When we paste an Excel range to the target bookmark, we essentially overwrite the bookmark. Step 7 re-creates the bookmark to ensure that the next time you run this code, the bookmark is there.
8. Finally, the macro releases the objects assigned to the variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code in the newly created module.
Macro 98: Simulating Mail Merge with a Word Document
One of the most requested forms of integration with Word is the mail merge. In most cases, mail merge refers to the process of creating one letter or document and then combining it with a separate document containing their names and addresses. For example, suppose you had a list of customers and you wanted to compose a letter to each customer. With mail merge, you can write the body of the letter one time, and then run the mail merge feature in Word to automatically create a letter for each customer, affixing the appropriate, address, name and other information to each letter.
For you automation buffs, you can use a macro to simulate the Word mail merge function from Excel.
How it works
The idea is relatively simple. You start with a template that contains bookmarks identifying where each element of contact information should go. After the template is ready, the idea is to simply loop through each contact in your contact list, assigning the component pieces of their contact information to the respective bookmarks.
One of the sample files for this Part is a document called MailMerge.docx. This document has all the bookmarks needed to run the sample code shown here.
Note that you will need to set a reference to the Microsoft Word Object Library. To do so, open the Visual Basic Editor in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Word XX Object Library, where the XX is your version of Word. Select the check box next to the entry.
Sub Macro98()
‘Step 1: Declare your variables
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim MyRange As Excel.Range
Dim MyCell As Excel.Range
Dim txtAddress As String
Dim txtCity As String
Dim txtState As String
Dim txtPostalCode As String
Dim txtFname As String
Dim txtFullname As String
‘Step 2: Start Word and add a new document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Add
wd.Visible = True
‘Step 3: Set the range of your contact list
Set MyRange = Sheets(“Contact List”).Range(“A5:A24”)
‘Step 4: Start the loop through each cell
For Each MyCell In MyRange.Cells
‘Step 5: Assign values to each component of the letter
txtAddress = MyCell.Value
txtCity = MyCell.Offset(, 1).Value
txtState = MyCell.Offset(, 2).Value
txtPostalCode = MyCell.Offset(, 3).Value
txtFname = MyCell.Offset(, 5).Value
txtFullname = MyCell.Offset(, 6).Value
‘Step 6: Insert the structure of template document
wd.Selection.InsertFile _
ThisWorkbook.Path & “” & “MailMerge.docx”
‘Step 7: Fill each relevant bookmark with respective value
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Customer”
wd.Selection.TypeText Text:=txtFullname
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Address”
wd.Selection.TypeText Text:=txtAddress
wd.Selection.Goto What:=wdGoToBookmark, Name:=”City”
wd.Selection.TypeText Text:=txtCity
wd.Selection.Goto What:=wdGoToBookmark, Name:=”State”
wd.Selection.TypeText Text:=txtState
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Zip”
wd.Selection.TypeText Text:=txtPostalCode
wd.Selection.Goto What:=wdGoToBookmark, Name:=”FirstName”
wd.Selection.TypeText Text:=txtFname
‘Step 8: Clear any remaining bookmarks
On Error Resume Next
wdDoc.Bookmarks(“Address”).Delete
wdDoc.Bookmarks(“Customer”).Delete
wdDoc.Bookmarks(“City”).Delete
wdDoc.Bookmarks(“State”).Delete
wdDoc.Bookmarks(“FirstName”).Delete
wdDoc.Bookmarks(“Zip”).Delete
‘Step 9: Go to the end, insert new page, and start with the next cell
wd.Selection.EndKey Unit:=wdStory
wd.Selection.InsertBreak Type:=wdPageBreak
Next MyCell
‘Step 10: Set cursor to beginning and clean up memory
wd.Selection.HomeKey Unit:=wdStory
wd.Activate
Set wd = Nothing
Set wdDoc = Nothing
End Sub
1. Step 1 declares four variables: wd is an object variable that exposes the Word Application object, wdDoc is an object variable that exposes the Word Document object, MyRange contains the range defining the contact list, and MyCell is used to pass cell values into the string variables. We also declare six string variables. Each of the string variables holds a component piece of information for each contact in the contact list.
2. This step opens Word with a blank document. Note that we set the Visible property of the Word application to True. This ensures that we can see the action in Word as the code runs.
3. Step 3 defines each contact in the contact list. Note that this range only selects the first column in the contacts table. This is because each cell in the range must be passed individually to string variables. Selecting only the first column gives us one cell per row. From that one cell, we can easily adjust the cursor to the right or left to capture the cells around it. The idea is that if we move to the right one space, we get the value of the next field in that row. If we move to the right two spaces, we get the value of that field, and so on.
4. This step starts the loop through each contact as defined in the range set in Step 3.
5. Step 5 uses Excel's Offset method to capture the value of each field in a particular row. We start with the range defined in Step 3 (the first column in the list of contacts). We then use Offset to move the cursor a certain number of columns to the right to capture the data in each relevant field. As each field is covered, we assign their values to the appropriate string variable.
6. In Step 6, we insert the existing template into the empty document in Word. This is tantamount to copying the structure of our template and pasting it into a blank document.
7. Step 7 assigns the value of each string variable to its respective bookmark. As you can see in the code, this step selects the bookmark by name, and then changes the text to equal the value of the assigned string variable.
8. The goal in Step 8 is to remove any stray bookmarks. If any bookmarks linger, we get duplicate bookmarks as the procedure loops through each cell.
9. At this point in the code, we have created a document for one contact in our list of contacts. The idea now is to create a new blank document so that we can perform the same procedure for the next contact. Inserting a page break effectively creates the new blank document. We then loop back to Step 5, where we pick up the contact information for the next row in the list. Then at Step 6, we insert the blank template (complete with bookmarks) into the new page. Finally, we assign values to the bookmarks and clean up. The For…Next loop ensures that this cycle is repeated for each row in the contact list.
10. Step 10 releases the objects assigned to your variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code in the newly created module.
Macro 99: Sending Excel Data to a PowerPoint Presentation
It's been said that up to 50 percent of PowerPoint presentations contain data that has been copied straight out of Excel. This is not difficult to believe. It's often much easier to analyze and create charts and data views in Excel than in PowerPoint. After you've created those charts and data views, why wouldn't you simply move them into PowerPoint? The macro in this section allows you to dynamically create PowerPoint slides that contain data from a range you specify.
How it works
In this example, you are copying a range from an Excel file and pasting that range to a slide in a newly created PowerPoint presentation.
Keep in mind that because this code is run from Excel, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select the check box next to the entry.
Sub CopyRangeToPresentation ()
‘Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
‘Step 2: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
‘Step 3: Add new slide as slide 1 and set focus to it
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
PPSlide.Select
‘Step 4: Copy the range as a picture
Sheets(“Slide Data”).Range(“A1:J28”).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
‘Step 5: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
‘Step 6: Add the title to the slide
SlideTitle = “My First PowerPoint Slide”
PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
‘Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End sub
1. Step 1 declares four variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; and SlideTitle is an string variable used to pass the text for the slide title.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that you can see the action as the code runs.
3. Step 3 adds a new slide to the presentation using the Add method of Slide object. Note that we are using the ppLayoutTitleOnly, ensuring the slide is created with a title text frame. We then take an extra step here and actually set focus on the slide. That is to say, we explicitly tell PowerPoint to select this slide, making it active.
4. Step 4 uses the CopyPicture method to copy the target range as a picture. The range being copied here is range A1 to J28 in the Slide Data tab.
5. Step 5 pastes the picture into the active slide and centers the picture both horizontally and vertically.
6. Step 6 stores the text for the title in a string variable, and then passes that variable to PowerPoint to apply text to the title text frame.
7. Step 7 releases the objects assigned to our variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code.
Macro 100: Sending All Excel Charts to a PowerPoint Presentation
It's not uncommon to see multiple charts on one worksheet. Many of us occasionally need to copy our charts to PowerPoint presentations. The macro here assists in that task, effectively automating the process of copying each one of these charts into its own slide.
How it works
In this macro, we loop through the ActiveSheet.ChartObjects collection to copy each chart as a picture into its own page in a newly created PowerPoint presentation.
Keep in mind that because this code will be run from Excel, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select the check box next to the entry.
Sub CopyAllChartsToPresentation()
‘Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim i As Integer
‘Step 2: Check for charts; exit if no charts exist
Sheets(“Slide Data”).Select
If ActiveSheet.ChartObjects.Count < 1 Then
MsgBox “No charts existing the active sheet”
Exit Sub
End If
‘Step 3: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
‘Step 4: Start the loop based on chart count
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 5: Copy the chart as a picture
ActiveSheet.ChartObjects(i).Chart.CopyPicture _
Size:=xlScreen, Format:=xlPicture
Application.Wait (Now + TimeValue(“0:00:1”))
‘Step 6: Count slides and add new slide as next available slide number
ppSlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPSlide.Select
‘Step 7: Paste the picture and adjust its position; Go to next chart
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
Next i
‘Step 8: Memory Cleanup
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End Sub
1. Step 1 declares four variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; and i is used as a counter to help loop through the charts in the worksheet.
2. Step 2 does an administrative check to ensure that there are actually charts in the specified worksheet. If no charts are found, the macro exits the procedure with no further action.
3. Step 3 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that we can see the action as the code runs.
4. Step 4 establishes how many times the macro will go through the procedure by capturing the number of charts in the worksheet. In other words, if the worksheet contains five charts, the code will loop five times. The macro starts the loop with one and keeps looping through the procedure until we hit the number of charts in the worksheet. The variable i ultimately represents the chart number we are currently on.
5. Step 5 uses the CopyPicture method to copy the chart as a picture. The variable i passes the actual chart number we are currently working with. The Application.Wait method tells the macro to pause for a second, allowing the clipboard to catch up with all the copying going on.
6. Step 6 adds a new slide to the presentation using the Add method of the Slide object. Note that we are using SlideCount+1 to specify the index number of the added slide. Because we are looping through an unknown number of charts, we can't hard-code the index number for each slide. Using SlideCount+1 allows us to dynamically assign the next available number as the slide index. Note that in Step 6, we are using ppLayoutBlank, ensuring that the newly created slides start with a blank layout. The macro then takes an extra step here and actually sets focus on the slide. In other words, the code explicitly tells PowerPoint to select this slide, making it active.
7. Step 7 pastes the picture into the active slide, centers the picture both horizontally and vertically, and then moves to the next chart.
8. Step 8 releases the objects assigned to your variables, reducing the chance of any problems caused by rouge objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code.
Macro 101: Convert a Workbook into a PowerPoint Presentation
This macro takes the concept of using Excel data in PowerPoint to the extreme. Open the sample workbook called Macro 101 Convert a Workbook into a PowerPoint Presentation.xlsm. In this workbook, notice that each worksheet contains its own data about a region. It's almost like each worksheet is its own separate slide, providing information on a particular region.
The idea here is that you can build a workbook in such a way that it mimics a PowerPoint presentation; the workbook is the presentation itself and each worksheet becomes a slide in the presentation. After you do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of automation.
With this technique, you can build entire presentations in Excel, where you have better analytical and automation tools. Then you can simply convert the Excel version of your presentation to a PowerPoint presentation.
How it works
Before you implement this macro in your workbook, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select a check box next to the entry.
Sub Macro101()
‘Step 1: Declare your variables
Dim pp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim xlwksht As Excel.Worksheet
Dim MyRange As String
Dim MyTitle As String
‘Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = New PowerPoint.Application
Set PPPres = pp.Presentations.Add
pp.Visible = True
‘Step 3: Set the ranges for your data and title
MyRange = “A1:I27”
‘Step 4: Start the loop through each worksheet
For Each xlwksht In ActiveWorkbook.Worksheets
xlwksht.Select
Application.Wait (Now + TimeValue(“0:00:1”))
MyTitle = xlwksht.Range(“C19”).Value
‘Step 5: Copy the range as picture
xlwksht.Range(MyRange).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
‘Step 6: Count slides and add new slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
PPSlide.Select
‘Step 7: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pp.ActiveWindow.Selection.ShapeRange.Top = 100
‘Step 8: Add the title to the slide then move to next worksheet
PPSlide.Shapes.Title.TextFrame.TextRange.Text = MyTitle
Next xlwksht
‘Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub
1. Step 1 declares six variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; xlwksht is an object variable that exposes the Worksheet object; MyRange is a string variable used to store and pass a range name as a string; and MyTitle is a string variable used to store and pass a title for each slide.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that we can see the action as the code runs.
3. Step 3 fills the MyRange variable with a string representing the range we want to capture as the slide content. We also fill the MyTitle variable with the value of cell C19. The value here becomes the title for the slide.
4. Step 4 starts the loop through each worksheet in the workbook. The loop stops when all worksheets have been looped through. Note that we are using the Application.Wait method, telling the macro to pause for a second. This allows the chart to render completely before the range is copied.
5. Step 5 uses the CopyPicture method to copy our specified range as a picture.
6. Step 6 adds a new slide to the presentation using the Add method of the Slide object. Note that we are using SlideCount+1 to specify the index number of the added slide. Using SlideCount+1 allows us to dynamically assign the next available number as the slide index. Also note that we are using ppLayoutTitleOnly, ensuring our slide is created with a title text frame.
7. Step 7 pastes the picture into the active slide, centers the picture horizontally, and adjusts the picture vertically 100 pixels from the top margin.
8. Step 8 passes the MyTitle variable to apply text to the title text frame.
9. Step 9 releases the objects assigned to the variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code.