Using VBA to copy from Excel to an open Powerpoint presentation -


i know question has been asked in similar ways before new coding , finding difficult understand language used in of other posts.

  • essentially task copy row of data 1 excel spreadsheet creates charts single row.

  • it creates 6 charts in total , these need copied powerpoint presentation, 4 of them 1 slide , other 2 on next.

  • then code should loop beginning , begin process again next row of data pasting results of iteration 2 new slides.

i have managed write enough code take data excel convert charts , export powerpoint copies new powerpoint presentation rather new slide , need copy active presentation. here code:

    sub tranposer()     '     ' tranposer macro     ' copies , transposes answers graph calculator     '     ' keyboard shortcut: ctrl+h     '         windows("data spreadsheet.xlsx").activate         rows("2:2").select         selection.copy         windows("graph spreadsheet.xlsm").activate         range("b1").select         selection.pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:= _     false, transpose:=true      dim powerpointapp powerpoint.application     dim mypresentation powerpoint.presentation     dim myslide powerpoint.slide     dim myshaperange powerpoint.shape        'create instance of powerpoint       on error resume next          'is powerpoint opened?           set ppapp = getobject(, "powerpoint.application")           set pppres = ppapp.activepresentation            set powerpointapp = getobject(class:="powerpoint.application")          'clear error between errors           err.clear          'if powerpoint not open open powerpoint           if powerpointapp nothing set powerpointapp = createobject(class:="powerpoint.application")          'handle if powerpoint application not found           if err.number = 429             msgbox "powerpoint not found, aborting."             exit sub           end if        on error goto 0      'make powerpoint visible , active       powerpointapp.visible = true       powerpointapp.activate      'create new presentation       set mypresentation = powerpointapp.presentations.add      'add slide presentation       set myslide = mypresentation.slides.add(1, pplayouttitleonly)      'copy excel range     activesheet.chartobjects("chart 1").activate       activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile     'copy excel range     activesheet.chartobjects("chart 7").activate       activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     activesheet.chartobjects("chart 5").activate       activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile     'copy excel range     activesheet.chartobjects("chart 4").activate   activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     activesheet.chartobjects("chart 6").activate       activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     activesheet.chartobjects("chart 9").activate       activechart.chartarea.copy      'paste powerpoint , position       myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile     'clear clipboard       application.cutcopymode = false      end sub

i know lot of code , know loop through charts save time don't know how loop yet comfortable leaving how is. can me exporting powerpoint?

if understood well, want loop select next row in data spreadsheet copy/paste graph spreadsheet , paste 6 charts (on 2 slides) each row same presentation.

here code reviewed (modifications/options below code) :

sub tranposer() ' ' tranposer macro ' copies , transposes answers graph calculator ' ' keyboard shortcut: ctrl+h ' dim powerpointapp powerpoint.application, _     mypresentation powerpoint.presentation, _     myslide powerpoint.slide, _     myshaperange powerpoint.shape, _     wsdata worksheet, _     wsgraph worksheet  set wsdata = workbooks("data spreadsheet.xlsx").activesheet set wsgraph = workbooks("graph spreadsheet.xlsm").activesheet  on error resume next 'is powerpoint opened? set ppapp = getobject(, "powerpoint.application") set pppres = ppapp.activepresentation set powerpointapp = getobject(class:="powerpoint.application") 'clear error between errors err.clear 'if powerpoint not open open powerpoint if powerpointapp nothing set powerpointapp = createobject(class:="powerpoint.application") 'handle if powerpoint application not found if err.number = 429     msgbox "powerpoint not found, aborting."     exit sub end if on error goto 0 'make powerpoint visible , active powerpointapp.visible = true powerpointapp.activate   'create new presentation 'set mypresentation = powerpointapp.presentations.add 'or open existing 1 set mypresentation = powerpointapp.presentations.open("c:\test\ppt_test.pptx")   'add slide presentation set myslide = mypresentation.slides.add(mypresentation.slides.count, pplayouttitleonly)   = 2 5      'wsdata.range("a" & wsdata.rows.count).end(xlup).row     wsdata.rows(i & ":" & i).copy     wsgraph.range("b1").pastespecial paste:=xlpasteall, _                                     operation:=xlnone, _                                     skipblanks:=false, _                                     transpose:=true     'copy excel range     wsgraph.chartobjects("chart 1").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     wsgraph.chartobjects("chart 7").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     wsgraph.chartobjects("chart 5").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     wsgraph.chartobjects("chart 4").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile       'add new slide     set myslide = mypresentation.slides.add(mypresentation.slides.count, pplayouttitleonly)       'copy excel range     wsgraph.chartobjects("chart 6").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile      'copy excel range     wsgraph.chartobjects("chart 9").chartarea.copy     'paste powerpoint , position     myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile next    'clear clipboard application.cutcopymode = false  'set = nothing : free named object-variables set ppapp = nothing set pppres = nothing set powerpointapp = nothing set mypresentation = nothing set myslide = nothing set wsdata = nothing set wsgraph = nothing  end sub

first, need specify name of sheets in here set wsdata = workbooks("data spreadsheet.xlsx").activesheet, set wsdata = workbooks("data spreadsheet.xlsx").sheets("sheet_name").

then can either create new presentation set mypresentation = powerpointapp.presentations.add or open existing 1 set mypresentation = powerpointapp.presentations.open("c:\test\ppt_test.pptx").

for loop, moment, set loop row 2 row 5 in data spreadsheet for = 2 5, can loop way last row of data getting rid of 5 , replace wsdata.range("a" & wsdata.rows.count).end(xlup).row

finally, don't forget free object-variable setting them nothing.

btw, got rid of useless select , activate greedy in resources nothing of time.


Comments

Post a Comment

Popular posts from this blog

android - Pass an Serializable object in AIDL -

i have defined aidl file interface imyfeature { void dotask(class clazz); } but need pass class object process. possible in android? if so, how? i tried: interface imyfeature { serializable class; void dotask(class clazz); } but doesn’t work. aidl in android supports custom class implement parcelable interface. in case want send class object not possible. can send class canonical name , retrieve in service. for example have aidl file interface imyfeature { void dotask(string classname); } then call : binder.dotask(myclass.class.getcanonicalname()); and in service binder stub implementation : private final imyfeature mbinder = new imyfeature.stub() { public void dotask(string classname) { try { class<?> clazz = class.forname(classname); constructor<?> ctor = clazz.getconstructor((class[]) null); object object = ctor.newinstance(); ...
Read more

How to provide Authorization & Authentication using Asp.net, C#? -

Image
i have asp.net application in c#. i have provide page authorization specific user/usergroup. i have created 2 tables in database: 1) pagename 2)permissions[insert, update, view, delete] but i'm unable match tables , permissions assigning... can 1 please me, how created tables in database , how coding. please me. you can below explanation. configure access specific file , folder, set forms-based authentication. request page in application redirected logon.aspx automatically. in web.config file, type or paste following code. this code grants users access default1.aspx page , subdir1 folder . <configuration> <system.web> <authentication mode="forms" > <forms loginurl="login.aspx" name=".aspnetauth" protection="none" path="/" timeout="20" > </forms> </authentication> <!-- section denies access files in...
Read more

How to use Authorization & Authentication in Asp.net, C#? -

i using roll management , trying give page , folder access according user or user group, using server created ad group user authentication. i have default1.aspx page default , subdir1 folder give different access separate user group i using below logic in web.config. <location path="subdir1"> <system.web> <authorization> <allow users ="?" /> </authorization> </system.web> </location> i facing problem provide same access 2 or more directory same user should have provide allow user code twice both folder? i can use logic repeating value folder want access providing in 1 logic. i have got answer configure folder/page access, have make different access shown below.. configure access specific file , folder, set forms-based authentication. request page in application redirected logon.aspx automatically. in web.config file, done following code. this code grants user...
Read more