PLanetk Posted November 12, 2013 Share Posted November 12, 2013 Good morning- Thank you for taking the time to at least read this newbie's post and <hug> in advance if you provide me with any guidance as I'm new to the wonderful world of AutoIt! However, it has already improved my work-life balance greatly. Until yesterday morning. I am using AutoIt to update several Excel Spreadsheets which feed the digital signage out on the call center floor. The script then opens a pptm and clicks on a custom add-in that runs a macro to update the links in the charts housed on the slide and then promptly saves the slide as a jpg. It worked great Friday night when I left the office (no, cocktail hour had begun yet). I will also attach the image of the error received (just in case that may help any). I have changed the names to protect the innocent but here is my AutoIt Script (please chime in with script errors/efficiency suggestions): #include <Excel.au3> _ExcelBookOpen('\\location.file1name.xlsm') #include <Excel.au3> _ExcelBookOpen('\\location.file2name.xlsm') #include <Excel.au3> _ExcelBookOpen('\\location.file3name.xlsm') #include <Excel.au3> _ExcelBookOpen('\\location.file4name.xlsm') #include <Excel.au3> _ExcelBookOpen('\\location.file5name.xlsm') #include <Excel.au3> _ExcelBookOpen('\\location.file6name.xlsm') Sleep(1000) #include <Powerpoint.au3> $objPPT=_PPT_PowerPointApp() _PPT_PresentationOpen($objPPT, "\\location.MTDChartPresentation.pptm") MouseClick("Left", 630, 46, 1) MouseClick("Left", 48, 72, 1) Sleep(20) _PPT_PowerPointQuit($objPPT); Exit PowerPoint Here is the script of the custom add-in (MouseClick above): expandcollapse popupSub Auto_Open() Dim oToolbar As CommandBar Dim oButton As CommandBarButton Dim MyToolbar As String ' Give the toolbar a name MyToolbar = "Kewl Tools" On Error Resume Next ' so that it doesn't stop on the next line if the toolbar's already there ' Create the toolbar; PowerPoint will error if it already exists Set oToolbar = CommandBars.Add(Name:=MyToolbar, _ Position:=msoBarFloating, Temporary:=True) If Err.Number <> 0 Then ' The toolbar's already there, so we have nothing to do Exit Sub End If On Error GoTo ErrorHandler ' Now add a button to the new toolbar Set oButton = oToolbar.Controls.Add(Type:=msoControlButton) ' And set some of the button's properties With oButton .DescriptionText = "This is my first button" 'Tooltip text when mouse if placed over button .Caption = "Do Macro Stuff" 'Text if Text in Icon is chosen .OnAction = "Auto_Update_Thermos" 'Runs the Sub Button1() code when clicked .Style = msoButtonIcon ' Button displays as icon, not text or both .FaceId = 52 ' chooses icon #52 from the available Office icons End With ' Repeat the above for as many more buttons as you need to add ' Be sure to change the .OnAction property at least for each new button ' You can set the toolbar position and visibility here if you like ' By default, it'll be visible when created. Position will be ignored in PPT 2007 and later oToolbar.Top = 150 oToolbar.Left = 150 oToolbar.Visible = True NormalExit: Application.Quit Exit Sub ' so it doesn't go on to run the errorhandler code ErrorHandler: 'Just in case there is an error MsgBox Err.Number & vbCrLf & Err.Description Resume NormalExit: End Sub Sub Auto_Update_Thermos() ActivePresentation.UpdateLinks Application.DisplayAlerts = ppAlertsNone Application.ActivePresentation.Save Dim sImagePath As String Dim sImageName As String Dim oSlide As Slide '* Slide Object On Error GoTo Err_ImageSave sImagePath = "\\file location\" For Each oSlide In ActivePresentation.Slides sImageName = "SALES2GOAL_THERM" & ".jpg" oSlide.Export sImagePath & sImageName, "JPG" Next oSlide Err_ImageSave: If Err <> 0 Then MsgBox Err.Description End If [attachment=42445:pptm_error_message1.JPG]End Sub Link to comment Share on other sites More sharing options...
water Posted November 12, 2013 Share Posted November 12, 2013 What I would change first: #include <Excel.au3> is only needed once in your script. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 12, 2013 Share Posted November 12, 2013 I'm not sure the Excel UDF can handle so many Workbooks. It only acts on the active Workbook. Can't you process them in a loop? Do you know which line number of the macro issues the error message? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
PLanetk Posted November 12, 2013 Author Share Posted November 12, 2013 Thank you for the info about the include line only needing to be there once. The error comes up when the script opens the pptm and executes the MouseClicks for the add-in. I'm checking on here for possible errors in my AutoIt script because a co-worker seems to think that the way the script is forcing PowerPoint to close may be causing the issue if the add-in is closing the presentation. I put that in there because PowerPoint was staying open without it. Do you think it is an issue with the VB script in PowerPoint rather than my AutoIt script? Link to comment Share on other sites More sharing options...
water Posted November 12, 2013 Share Posted November 12, 2013 To make sure that the addin and macro have finished before you close PPT you could raise the Sleep value from 20 (milliseconds) to 60000 (60 seconds). If this works then you need another approach to close the PPT. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now