10 Replies Latest reply on Jul 27, 2016 2:36 PM by Tara Campese

    How to schedule a macro or add-in to run weekly

    Tara Campese

      I have a script that I wrote as an add-in that runs a SQL query and sends personalized emails with results of open work orders created by the user who is receiving the email. I want it to run every Monday at 6:00am without any user input to trigger it.

       

      Here is what I've tried so far and the problems I've run into:

      Create an epdm add-in

        • I cannot find an epdm hook that is based on time
        • If I have it always running and checking the time at every X time interval, it could potentially slow down the epdm system

      Use SW Task Scheduler (Run Custom Task) - I created a working macro that does the same thing

        • Task Scheduler runs macros on specific files in a folder, since I am not giving it a folder, it times out.
        • If I assign an arbitrary folder for the Task Scheduler, the task fails.

      Use Windows Task Scheduler to Run the Macro in Excel

        • A user would have to open excel to launch it before the task would trigger

      Use epdm Task Add-in

        • Fails to run macro
        • Don't know if I can schedule a task to run at a specific time, but I figured it was worth a shot.

      Schedule SQL Query emails

        • The emails need to be personally tailored to the individual receiving it.

       

      Does anyone know of a way to accomplish this without having an add-in constantly running in the background?

       

      Thank you,

      Tara

        • Re: How to schedule a macro or add-in to run weekly
          Deepak Gupta

          Are you running custom task via Task Scheduler?

            • Re: How to schedule a macro or add-in to run weekly
              Tara Campese

              The second method I mentioned was through the SolidWorks Task Scheduler. It fails or times out every time.

                • Re: How to schedule a macro or add-in to run weekly
                  Deepak Gupta

                  Sorry I mean to ask how are you setting up the custom task?

                    • Re: How to schedule a macro or add-in to run weekly
                      Tara Campese

                      I followed these instructions:

                      http://www.cadsharp.com/blog/run-solidworks-macros-using-task-scheduler/

                      And I've attached a screenshot of how mine is set up. Note that I am not pointing to a specific folder as I do not need to run on specific files. I have also attached my macro for reference.

                      Task Schedule Setup.JPG

                       

                      'My SQL Query Email Genertor

                      Sub WOReport()

                       

                      Dim swApp As SldWorks.SldWorks

                      Dim cnn As New ADODB.Connection

                      Dim rst As New ADODB.Recordset

                      Dim ConnectionString As String

                      Dim StrQuery As String

                      Dim varRecords As Variant

                      Dim intNumReturned As Integer

                      Dim intNumColumns As Integer

                      Dim intColumn As Integer

                      Dim intRow As Integer

                       

                      ' Set the Path variable equal to the path of your program's installation

                      Dim path As String

                      path = "C:\Program Files\SolidWorks Corp\SolidWorks\SLDWORKS.exe"

                       

                      Dim x As Variant

                      x = Shell(path, vbMinimizedFocus)

                       

                      On Error GoTo ErrorHandler

                          Set swApp = Application.SldWorks

                          'log into vault

                          Dim vault As IEdmVault5

                          Dim vaultName As String

                       

                          Set vault = New EdmVault5

                          vaultName = "RadiaBeamVault"

                          vault.LoginAuto vaultName, 0

                         

                          'Setup the connection string for accessing MS SQL database

                          ConnectionString = "Provider=SQLOLEDB.1;Password=RadiaBeam1;Persist Security Info=True;User ID=sa;Data Source=192.168.1.104;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=RadiaBeamVault"

                       

                          'Opens connection to the database

                          cnn.Open ConnectionString

                         

                          'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value

                          cnn.CommandTimeout = 900

                       

                          'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid

                          StrQuery = "SELECT * from WOStatusReports WHERE filename NOT LIKE 'WO-blank%'"

                       

                          'Performs the actual query

                          rst.Open StrQuery, cnn

                         

                          varRecords = rst.GetRows()

                          intNumReturned = UBound(varRecords, 2) + 1

                          intNumColumns = UBound(varRecords, 1) + 1

                         

                          'Get User Information

                          Dim userMgr As IEdmUserMgr5

                          Dim User As IEdmUser5

                          Dim UserPos As IEdmPos5

                          Dim username As String

                         

                          Set userMgr = vault

                          Set UserPos = userMgr.GetFirstUserPosition()

                         

                          'set email format

                          Dim mformat As String

                          mformat = "<a style=""border: none;"" href=""http://www.radiabeam.com/index.html""><img src=""http://www.radiabeam.com/images/logo.gif"" alt="""" border=""0""></a><BR><BR>"

                       

                          Dim Machining As String

                          Dim SignOff As String

                          Dim Approval As String 'Need to add funcitonality

                       

                          Do Until UserPos.IsNull

                              Set User = userMgr.GetNextUser(UserPos)

                              username = User.Name

                             

                              Dim count As Integer

                              count = 0

                             

                              Machining = ""

                              SignOff = ""

                              Approval = ""

                             

                              For intRow = 0 To intNumReturned - 1

                                  Dim RowName As String

                                  RowName = varRecords(1, intRow)

                                  Dim WONumber As String

                                  WONumber = varRecords(0, intRow)

                                  Dim Status As String

                                  Status = varRecords(2, intRow)

                       

                                  If RowName = "R. Agustsson" Or RowName = "S. Boucher" Then

                                      Approval = Approval & WONumber & "<BR>"

                                  End If

                                  If varRecords(1, intRow) = username Then

                                      count = count + 1

                                      If Status = "WO Sign-Off" Then

                                          SignOff = SignOff & WONumber & "<BR>"

                                      ElseIf Status = "WO Manufacturing" Then

                                          Machining = Machining & WONumber & "<BR>"

                                      End If

                                  End If

                              Next intRow

                             

                              If Machining <> "" Then Machining = "<b>In Process:</b><BR>" & Machining

                              If SignOff <> "" Then SignOff = "<b>Awaiting your sign-off:</b><BR>" & SignOff

                              If Approval <> "" Then Approval = "<b>Awaiting your approval:</b><BR>" & Approval

                             

                              Dim myMessage As String

                              If count = 0 And username <> "R. Agustsson" Then

                                  'MsgBox "User does not have open work orders"

                              ElseIf username = "R. Agustsson" Then

                                  myMessage = Replace(Approval & "<BR>" & SignOff & "<BR>" & Machining, ".xlsx", "")

                              ElseIf username = "S. Boucher" Then

                                  myMessage = Replace(Approval & "<BR>" & SignOff & "<BR>" & Machining, ".xlsx", "")

                              Else

                                  myMessage = Replace(SignOff & "<BR>" & Machining, ".xlsx", "")

                              End If

                             

                              'set subject

                              Dim mySubject As String

                              mySubject = "Weekly Work Order Report for " & username

                             

                              If username = "T. Campese" Then

                                  User.SendMsg mySubject, mformat & myMessage

                              End If

                                 

                          Loop

                       

                          rst.Close

                          cnn.Close

                         

                          Set rst = Nothing

                          Set cnn = Nothing

                       

                          swApp.ExitApp

                       

                      Exit Sub

                       

                      ErrorHandler:

                         MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

                      End Sub