BLOG

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Share Button

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Download this Excel workbook that runs a PowerPoint presentation.
Download this accompanying PowerPoint presentation.

Here is how you can run a PowerPoint presentation from Excel, with the below macro named “PowerPointSlideshow”. This macro is also in the downloadable workbook named “RunPowerPointExample” through a link at the top of this blog entry, which runs a slideshow from your PowerPoint presentation. A PowerPoint presentation example that accompanies this Excel workbook’s macro can also be downloaded from the above link.

When the slideshow is completed, you can hit the Esc key or click anywhere on the screen to show the presentation which will still be open. I placed notes in the macro to explain what the code is doing along the way, and to indicate where you can adjust the PowerPoint presentation’s name, path location, and slide viewing time.

The accompanying downloadable presentation contains 3 slides, each shown for 5 seconds that will look like the following 3 pictures:

Slide 1 of 3

Slide 2 of 3

Slide 3 of 3

Here’s the macro, same as in the downloadable workbook:

Sub PowerPointSlideshow()

'Declare and define String variables (modify as desired).
Dim strFilePath As String, strFileName As String, strFilePathAndName
strFilePath = "C:\YourFilePath\"
strFileName = "SlideshowTest.pptx"
strFilePathAndName = strFilePath & strFileName

'Verify that the declared PowerPoint presentation name exists
'in the declared path.
If Len(Dir(strFilePathAndName, vbDirectory)) = 0 Then
MsgBox "There is no file named ''" & strFileName & "''" & vbCrLf & _
"in the path ''" & strFilePath & "''." & vbCrLf & vbCrLf & _
"Please check your PowerPoint presentation's" & vbCrLf & _
"name, and its expected location in the path" & vbCrLf & _
strFilePath & ".", 48, "Cannot continue -- file and path not found."
Exit Sub
End If

'Declare and define Object variables.
Dim ppApp As Object, ppPres As Object
Set ppApp = CreateObject("PowerPoint.Application")
Set ppPres = ppApp.Presentations.Open(strFilePath & strFileName)

'Establish the slides' viewing time duration.
'This example is for 5 seconds.
With ppPres.slides.Range.slideshowtransition
.advanceontime = True
.advancetime = 5
End With

'Run the slide show.
ppPres.slideshowsettings.Run

'Express as True that the PowerPoint presentation was saved,
'if you want to close it without being prompted to save it.
ppPres.Saved = True

'Release the Object variables from memory.
Set ppPres = Nothing
Set ppApp = Nothing

End Sub

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Share Button

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Here’s a shortcut to manually import an Access database table into your spreadsheet. It’ll come with the alternating shaded rows and field header drop-down arrows, but it’s fast and easy. The first picture shows an access table just for example, noting that in actual practice, the database file will be closed during this quick task that involves no programming code.

Step 1

Step 2

Step 3

Step 4

Your import is complete.

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Showing Your Column Headers as Letters or Numbers

Share Button

You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.

TTFE027a
TTFE027b
TTFE027c

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Knowing Any Workbook’s Last Saved Date and Time

Share Button

You can get the last saved date and time for any workbook whether it is open or closed.

In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window. In the Immediate window of the VBE, I typed the following line of code and pressed Enter to know the last saved date and time for a closed workbook named YourFileName.xlsm located in the path C:\Your\File\Path\:
? FileDateTime("C:\Your\File\Path\YourFileName.xlsm")

In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time.

TTFE026a

TTFE026b

TTFE026c

TTFE026d

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Moving a Cell’s Lines of Text into Separate Columns

Share Button

I previously posted this example showing how to enter separate lines of text into a single cell. This example shows how to move each line of a cell’s text into its own cell.

The first picture shows five cells in range A3:A7 that each have three lines of text for a person’s name, street address, and their city, state, and zip code.

TTFE025a

Step 1: Parse each cell’s lines of text. Select the range, and press the Alt+A+E keys to show the Text to Columns Step 1 Wizard.
TTFE025b

Step 2: In the Text to Columns Step 1 Wizard, choose the Delimited option and click the Next button.
TTFE025c

Step 3: In the Text to Columns Step 2 Wizard, select Otheras the delimiter, and in the Other field press the Ctrl+J keys. This will create an invisible character that delimits the cells’ individual lines of text. You do not need to go to Step 3; just click the Finish button.
TTFE025d

The final result.
TTFE025e

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: New in Version 2016, the “Tell me what you want to do” Field

Share Button

New in Excel 2016, you can click into the “Tell me what you want to do” field as shown in the picture, or press Alt+Q from your worksheet, to enter a topic for information. This is a fairly intuitive new tool, while saving the interim step of hitting the F1 key for help about a topic.
For example, I typed “array formulas” into that field and the following information was provided.
TTFE0023a
TTFE0023b

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: New in Version 2016, the Multi-Select Icon for Pivot Table Slicers

Share Button

New in Excel 2016 is the Multi-Select icon for pivot table slicers, a welcome convenience to the heretofore requirement of pressing the Ctrl key to select multiple labels.
In the picture for example, I clicked the Multi-Select icon, then clicked to deselect the states of Arizona, Iowa, and Michigan from appearing in the pivot table’s Sum of Sales.
TTFE0022

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers

Share Button

Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.
In the picture, the array formula in cell B14 that sums the San Francisco Giants’ hits per at-bat is
=SUM(LEFT(B5:B13,FIND("-",B5:B13)-1)+0)&"-"&SUM(RIGHT(B5:B13,LEN(B5:B13)-FIND("-",B5:B13))+0)
Recall, being an array formula, it is committed to the cell with Ctrl+Shift+Enter, not just with Enter.
This formula in cell E14 (not an array formula) sums the Oakland A’s hits per at-bat:
=SUMPRODUCT((LEFT(E5:E13,FIND("-",E5:E13)-1))+0)&"-"&SUMPRODUCT((MID(E5:E13,FIND("-",E5:E13)+1,10))+0)
Important to note, before the numbers were entered into ranges B5:B13 and E5:E13, the cells in those ranges were formatted as Text. Otherwise, Excel would have regarded most of those entries as dates.
TTFE0021

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Customizing the Status Bar

Share Button

Tom’s Tutorials For Excel: Customizing the Status Bar
You can right-click the status bar for additional edit, formatting, and analysis options.
TTFE0020a
The following picture shows a Before and After comparison of the additional information you can choose for the status bar to show. In this example, the Minimum and Maximum values were also chosen for the selected range.
TTFE0020b

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally

Share Button

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally
As the picture shows by example, you can horizontally transpose a vertical range at any cell outside the vertical range with the formula
=INDEX($A$2:$A$25,COLUMNS($A$2:A25))
Note the absolute and relative references.
TTFE0019

Posted in Tom's Tutorials for Excel
Tags: ,