4 Replies Latest reply on Sep 1, 2016 2:04 PM by Cody Kirsch

    Using macro to update drawing rev from excel

    Cody Kirsch

      First some background:

      So I work a lot with building (like an actual building, a noun not a verb) were we don't really do individual part drawings just multiple displays of a top level assembly and sub assemblies for building instructions. So I cant really do this by adding a custom properties to the parts or assemblies because the assembly may change but if its at the end of the construction phase it wont affect the previous drawings.      

       

      The problem:

      I am trying to come up with a macro that will

           1) Look take the name of the drawing

           2) Open a specific excel file

           3) Find that drawing name and collect the rev information

           4) Then update the rev table

      I already have a macro that can do 1-3, but am having a hard time thinking of a good way to do 4. My first thought was to add properties to the drawing, but then I need to have all the call outs (I dont know what their called but the $PROPERTIES{VALUE} text used in the sheet format) for each rev and all the rev info (by, description, date, ect.). But this is the only way I can think of that will work. I'm not sure how I could identify a table using the SW API as it doesn't seem to be an object in the drawing. The only other way I can think of is to have the macro add a note at specific XY points depending of the rev but then if the drawing rev table were to change that would be a pain to re configure.

        • Re: Using macro to update drawing rev from excel
          Christian Chu

          can you post your macro here so we can take a look and see if we'll be able to modify the macro for your needs

            • Re: Using macro to update drawing rev from excel
              Cody Kirsch

              Sorry for the cut and past, cant upload a file. Also for the sloppy code, most of the lines are Debug.print since this one is giving me a hard time.

              Also feel free to make other suggestions, I'm still pretty new to VBA so I don't really understand the finer points of Application and Object settings. And the first While in the GetDataFromExcel sub is not working for some reason, thought I had that worked out.

               

               

                                       THE MACRO

              'This macro finds the SW drawing name and file location,

              'opens the Excel job sheet, looks for the drawing name and returns the latest rev and adds it to the part config

               

               

              Dim strPartRev As String

              Dim strPartDescription As String

              Dim strPartBy As String

              Dim strPartDate As String

              ' SW part dims

              Dim strPartName As String

              Dim strSWPartLocatione As String

              ' Excel part dims

              Dim strExcelFileName As String

              Dim strExcelFileLocation As String

              Dim intNumOfProp As Integer

              '//////////////////////////////////////////////////////////////////////////////////////////////////////////////

              Sub GetDataFromExcel()

              Debug.Print

              Debug.Print " GetDataFromExcel /////////////////////////////////////////////////////////////////////////////"

              Debug.Print

               

               

                  Dim xlApp As Excel.Application

                  Dim xlWB As Excel.Workbook

                  Set xlApp = CreateObject("Excel.Application")

                  Set xlWB = xlApp.Workbooks.Open(strExcelFileLocation)

                  xlApp.Visible = False

               

               

                  'Excel row counter

                  Dim intRow As Integer

                  intRow = 1

               

               

              Debug.Print " Looking for " & strPartName

                  'Find Part Row in Excel

                  With xlWB.Worksheets(1)

                      While Cells(intRow, 3).Value <> "strPartName"   'For some reson this does not not work, it just enters the macro into an infinit loop

              Debug.Print "Cell Value: " & Cells(intRow, 3).Value

                           intRow = intRow + 1

                      Wend

                  End With

               

               

                

                  'Find latest Rev

                  With xlWB.Worksheets(1)

                      While Cells(intRow, 4).Value <> ""

                          intRow = intRow + 1

                      Wend

                  End With

                  

              intRow = intRow - 1

                  'Get Rev info

                  With xlWB.Worksheets(1)

              Debug.Print Cells(intRow, 4).Value

              Debug.Print Cells(intRow, 5).Value

              Debug.Print Cells(intRow, 6).Value

              Debug.Print Cells(intRow, 7).Value

               

               

                          strPartRev = Cells(intRow, 4).Value

                          strPartDescription = Cells(intRow, 5).Value

                          strPartBy = Cells(intRow, 6).Value

                          strPartDate = Cells(intRow, 7).Value

                  End With

               

               

              'Value Check

              Debug.Print "Rev: " & strPartRev

              Debug.Print "Description: " & strPartDescription

              Debug.Print "By: " & strPartBy

              Debug.Print "Date: " & strPartDate

               

               

               

               

                  'Clean up

                  xlApp.Visible = True

                  xlWB.Close False 'Closes document (but not Excel) and do not save Document

                  xlApp.Quit       'Closes Excel

                  Set xlApp = Nothing

                  Set xlWB = Nothing

              Debug.Print "End GetDataFromExcel //////////////////////////////////////////////////////////////////////////////////////////"

              Debug.Print

              End Sub

              '//////////////////////////////////////////////////////////////////////////////////////////////////////////////

               

               

              '//////////////////////////////////////////////////////////////////////////////////////////////////////////////

              Sub main()

              Debug.Print

              Debug.Print "Sub main ////////////////////////////////////////////////////////////////////////////////////////"

              Debug.Print

               

               

                  Dim swApp As SldWorks.SldWorks

                  Dim swModel As SldWorks.ModelDoc2

                  Set swApp = Application.SldWorks

                  Set swModel = swApp.ActiveDoc

                  Set swPart = swModel

                 

              Debug.Print "/// Getting file info ////////////////////////////////////////////////////////////////////////////////////////"

                 

                  'Auto find file

                  strSWPartLocation = swModel.GetPathName ' SW file name & location

                  strPartName = Left(swModel.GetTitle, Len(swModel.GetTitle) - 9) ' SW part name

                 

                  strExcelFileName = Left(strPartName, 7) ' Excel file name

                  strExcelFileLocation = CurDir$ & "\" & strExcelFileName ' SW file name & location

               

               

                 

              Debug.Print "File Location = " & strSWPartLocation

              Debug.Print "Part name: " & strPartName

              Debug.Print "Excel name: " & strExcelFileName

              Debug.Print "Excel Location = " & strExcelFileLocation

                 

              Debug.Print "/// Got file info ////////////////////////////////////////////////////////////////////////////////////////"

               

               

                  'Open data from excel and put in arrays

                  Call GetDataFromExcel

               

               

                  'Put data into Part conf

                  'Call ConfigPart

                 

              Debug.Print

              Debug.Print "End Main Sub /////////////////////////////////////////////////////////////////////////////////////////"

              Debug.Print

              Debug.Print

              Debug.Print

              End Sub

            • Re: Using macro to update drawing rev from excel
              Christian Chu

              From my understanding (might be wrong), but control SW file's rev. from Excel is an extra work ???

              • Re: Using macro to update drawing rev from excel
                John Stoltzfus

                I don't use the SW revision tables - I have my own setup and I use the attached Excel Macro, (written by Markku Lehtola )to collect all the data and then I can change it and export that data right back to the SW files..

                 

                There are two versions of the macro, the one macro extracts Custom Properties from the Assemblies and Parts with Configurations files and the other is just the default Configuration.  The great thing with this macro is that you can extract the information, then change it and export it right back to the Assembly or Part file..