Memindahkan Hasil Olah Data Dari Excel ke PowerPoint Menggunakan VBA

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()

'Add a reference to the Microsoft PowerPoint Library by:

'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.

Jangan lupa baca yang ini juga