how to solve excel copy to ppt

Get help with using AutoHotkey and its commands and hotkeys
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

how to solve excel copy to ppt

13 Jun 2020, 00:33

The code of excel copy to ppt is wronge, who can help me.
The wrong is "oSlide.Slides(pptIndex).Shapes.Paste"

Code: Select all

#SingleInstance,Force
#NoEnv
#Include 11DateSet.ahk
SourcePattern := "F:\华鹤煤化\生产管理部\生产管理部台帐\工艺装备例会汇报材料.pptx"
DestPattern := "F:\华鹤煤化\生产管理部\生产管理部台帐\" DateCn "工艺装备例会汇报材料.pptx"
FileCopy,% SourcePattern,% DestPattern
oPPT := ComObjCreate("PowerPoint.Application")
pptPath := DestPattern
oSlide := oPPT.Presentations.Open(pptPath,,,0)
xl := ComObjCreate("Excel.Application")
Global oSlide,xl
;--------------------------------------------------------------------------------------
ExcelToPPT(ExcelPath,Sheet,oRange,pptIndex,ShapeLeft,ShapeTop){
	Clipboard := ""
	xl :=appExcel.Workbooks.Open(ExcelPath)
	xl.Sheets(Sheet).Range(oRange).Copy
	[b]oSlide.Slides(pptIndex).Shapes.Paste[/b]
    num := oSlide.Slides(pptIndex).Shapes.Count
    Chart := oSlide.Slides(pptIndex).Shapes(num)
    Chart.Left := ShapeLeft
    Chart.Top := ShapeTop
	Clipboard := ""
	xl.Close(0)
}
ExcelPath := "F:\华鹤煤化\生产管理部\生产管理部台帐\" 年 "年台账\" 年 "产量能耗统计.xlsx"
Sheet := "产量"
oRange := "A2:D10"
pptIndex := 5
ShapeLeft := 80
ShapeTop := 200
ExcelToPPT(ExcelPath,Sheet,oRange,pptIndex,ShapeLeft,ShapeTop)
;------------------------------------------------------------------------------------
appExcel.Quit()
appExcel := ""
oSlide.Save()
oSlide.Close(0)    ;Close(0) 不保存待定的更改,Close(-1) 自动保存待定更改,而不提示用户
oSlide := ""
oPPT.quit()
oPPT := ""
ExitApp
User avatar
boiler
Posts: 5909
Joined: 21 Dec 2014, 02:44

Re: how to solve excel copy to ppt

13 Jun 2020, 03:55

It works for me. Just need to change this line:

Code: Select all

xl.Close(0)
to this:

Code: Select all

xl.Workbooks.Close()
It should work anyway if you click "Yes" when it asks if you want to continue after the error. If you click "No", then it never gets to the part where it saves the PPT file, so you don't see the output.
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Re: how to solve excel copy to ppt

13 Jun 2020, 05:36

Thanks yout reply. But the error code is "oSlide.Slides(pptIndex).Shapes.Paste" .
not "xl.Close(0)"
User avatar
boiler
Posts: 5909
Joined: 21 Dec 2014, 02:44

Re: how to solve excel copy to ppt

13 Jun 2020, 08:20

Does it actually show an error for that line? As I said, it works for me. Do you have 5 slides in your PPT file? If not, that would cause an error since it is trying to paste onto slide 5.
User avatar
flyingDman
Posts: 844
Joined: 29 Sep 2013, 19:01

Re: how to solve excel copy to ppt

13 Jun 2020, 13:15

Work on your naming convention. You have have xl := ComObjCreate("Excel.Application") but then you use xl :=appExcel.Workbooks.Open(ExcelPath). appExcel is not defined. This is probably not the reason for the error but it needs to be fixed.
So follow this order and naming convention for the excel part:

Code: Select all

xl := ComObjCreate("xl.Application")
wrkbk := xl.Workbooks.Open(xlPath)
wrkbk.worksheets(Sheet).Range(oRange).Copy           ; or xl.Range(oRange).Copy
wrkbk.Close(0)
xl.Quit()
xl := ""
Also your function seems to be in the middle of the script. It does not have to. For clarity put it at the end. I would also recommend that you put all constants at the top. This helps when you are troubleshooting. Order is your friend...

Edit: if the clipboard is empty you will get the error you describe. So, it is likely that your issue with Excel as I mention above is the reason for your error. oSlide.Slides(pptIndex).Shapes.Paste works fine if there is an image or an excel range in the clipboard.
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Re: how to solve excel copy to ppt

13 Jun 2020, 22:39

Thanks flyingDman reply. According your tips the error is fixed. The code run well.

Return to “Ask For Help”

Who is online

Users browsing this forum: Google [Bot], littlegandhi1199, RyConnMD, tatagi and 28 guests