Memindahkan Hasil Olah Data Dari Excel ke PowerPoint Using 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 Duggirala yang 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.




2 Respones to "Memindahkan Hasil Olah Data Dari Excel ke PowerPoint Using VBA"

Anonim mengatakan...

Hеy there! I know thiѕ іs sort of off-topic but I needed to ask.

Does buіlding a wеll-eѕtablished blog suсh aѕ yours requіrе
а large amount of ωork? ӏ
аm completelу new to wrіting a blog but I do ωгіte in mу
journal on а daily baѕis. I'd like to start a blog so I can share my own experience and feelings online. Please let me know if you have any kind of ideas or tips for new aspiring blog owners. Thankyou!
My web site ... facebook software


13 Oktober 2012 02.33
Old Nakula mengatakan...

Firstly, I'd like to say that this isn't a well-established blog as you said, there's nothing special in connection with it.

Secondly, I honestly have no ideas or tips I can share to you or anyone else at all. As what you do, I also just write and share what I know and what I feel, hopefully it can inspire each other. But I frankly don't care whether or not my own experience in this blog read by visitor, because my first purpose of building this blog is merely to accomodate my idea, nothing else...

Anyway, thanks for visiting my blog buddy....


13 Oktober 2012 23.56

Poskan Komentar

 

Entri Populer

Recent Comments

Blog Statistic

Return to top of page Copyright © 2007 | Old Nakula