Anda terbiasa mengolah data menggunakan Visual Basic Application (VBA) di Microsoft Excel? Atau, apakah anda sering membuat presentasi business menggunakan data-data hasil olahan Microsoft Excel (seperti Tabel & Grafik) yang (kemudian) anda pindahkan ke Microsoft PowerPoint (secara manual)?. Jika iya, maka ada trik VBA sederhana (dengan sedikit modifikasi dari penulis, red) dari seorang MVP (Microsoft Valuable Player) Excel asal India, Purna Duggiralayang mengupas mengenai cara memindahkan data-data presentasi dari Microsoft Excel ke Microsoft PowerPoint secara otomatis hanya dengan sekali “Click”. Berikut adalah langkah-langkahnya:
a). Build your charts in Excel (Silahkan anda buat chart beserta keterangannya di Excel). Contoh:
b). Open VBA. To do this, you can press ALT + F11 (silahkan anda buka VBA di Excel ada dengan menekan ALT + F11)
c). In your VBA Editor window, click Insert → Module. (Setelah jendela VBA Editor terbuka, klik menu Insert → Module)
d). Paste the following code into the module. (Copy dan paste syntax berikut pada Module1 yang sudah ada insert tersebut)
Sub CreatePowerPoint()
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
'First we declare the variables we will be using
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim cht As Excel.ChartObject
'Let's create a new PowerPoint
If newPowerPoint Is Nothing Then
Set newPowerPoint = New PowerPoint.Application
End If
'Make a presentation in PowerPoint
If newPowerPoint.Presentations.Count = 0 Then
newPowerPoint.Presentations.Add
End If
'Show the PowerPoint
newPowerPoint.Visible = True
'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
For Each cht In ActiveSheet.ChartObjects
'Add a new slide where we will paste the chart
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
'Copy the chart and paste it into the PowerPoint as a Metafile Picture
cht.Select
ActiveChart.ChartArea.Copy
activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
'Set the title of the slide the same as the title of the chart
With activeSlide.Shapes(1).TextFrame.TextRange
.Text = cht.Chart.ChartTitle.Text
.Font.Size = 28
End With
'Adjust the positioning of the Chart on Powerpoint Slide
With newPowerPoint.ActiveWindow.Selection.ShapeRange
.Left = 45
.Top = 125
End With
With activeSlide.Shapes(2)
.Width = 200
.Left = 505
End With
'If the chart is the "US" consumption chart, then enter the appropriate comments
If InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "CIANJUR") Then
activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J5").Value & vbNewLine
End If
'Now let's change the font size of the callouts box
activeSlide.Shapes(2).TextFrame.TextRange.Font.Size = 14
Next
AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing
End Sub
e). Click Tools → References. Add the Microsoft PowerPoint Library. (Masih pada jendela VBA Editor, klik menu Tools → References. Tambahkan “Microsoft PowerPoint 12.0 Library”.)
f). Now all you need to do is go to Excel and run the CreatePowerPoint macro! To make this easy, draw a rectangle shape in your Excel worksheet which contains all the charts you want to export to PowerPoint. (Sekarang, yang perlu anda lakukan adalah kembali ke jendela Excel dan menjalankan “CreatePowerPoint Macro”. Biar lebih mudah, anda bisa membuat “rectangle shape” atau kalau tidak, anda juga bisa menggunakan ActiveXControl [Command Button] di worksheet yang terdapat grafik yang ingin anda pindahkan/copy ke PowerPoint).
g). Right click the rectangle/ Command Button and click Assign Macro (if you use rectangle and Click on the CreatePowerPoint macro and press Okay) or view Code if you use Command Button then paste this code:
Private Sub Export_Click()
Run "CreatePowerPoint"
End Sub
h). That’s it! Just click your rectangle/ Command button then sit back and watch it run! You’ll have your presentation in no time!
Mudah-mudahan bermanfaat.
