16 Replies Latest reply on Nov 30, 2017 2:34 PM by Jacob Corder

    SQL Searches in excel

    Rodney Martin

      I am wanting to use the ODB import in Excel to pull the file state into excel based on an input of the parameter "PartNumber", or even the first 5 digits of the filename (which is also the part number).  I was hoping to be able to do this in the query builder in Excel, and not have to write some SQL script.  Possible?

       

      BTW, I have been successful in getting into the SW SQL tables in excel, but I'm not sure I'm finding what I need to build the query. 

       

      Thanks,

      Rod

        • Re: SQL Searches in excel
          Jacob Corder

          if you are trying to get data from enterprise, why not use the report generator?

           

          i say this because the tables have connections EVERYWHERE.

           

          The query is a huge join where PartNumber is a variableID and the value lies in VariableValues

          but the connection to it is Documents

           

          a better method might be to write an excel addin, super easy to do.

           

          what you can do then is use the EDMLib library to run searches for files that = "12345%" which means starts with 12345

           

          with the IEDMSearch it returns the Search Object which you can use to Get the file.

           

           

           

          2017 SOLIDWORKS API Help - IEdmSearch5 Interface

           

          i am trying to steer you away from the sql search because you will have probably 10 joins in your query

          all this to get where the workflow state of a part number that will apply to possibly many files.

           

          So your query will be something like this

          you will need to look in Variables to find the part number variable id

          you can query this but it wont ever change so hard code it, mine is 98

           

          Part number lies in DBO.VariableValue where VariableID = your part number variable ID

           

          ConfigurationID also is retrieved from DBO.Variablevalue.ConfigurationID

          however its likely you won't need to use this

           

          You need to get documentID from DBO.VariableValue which is retrieved from DBO.VariableValue.DocumentID

           

          you then can get data from the DBO.Document

           

          CurrentStatusID is in DBO.Documents. Use this to join to DBO.Status where DBO.Documents.Status = DBO.Status.Statusid

           

          you now have access to DBO.Status.Name which is what you are looking for.

           

          Clear as mud?

           

           

           

          Good Luck

          • Re: SQL Searches in excel
            Jacob Corder

            also.  be very very careful.  you could destroy your vault if you dont make sure its a read only connection.  if you do a bound connection, you will destroy your vault and solidworks will NOT help you fix it as it is not supported to do direct database modifications

              • Re: SQL Searches in excel
                Rodney Martin

                Definitely good advice on "read only".  I am careful to pick this option when I connect to the tables.  But, I do agree... it does seem rather fragile.  One slip-up and its off the the daily backup.

                 

                We are currently using standard.  Thus, no reports.  What I am trying to accomplish is a dynamic excel table that can be used to show BOMS and their current state that non-engineers can use in development projects.  Real-time view of the bom will help purchasing know when its okay to submit for quotes, etc.  Having all of that info ready at a glance and always fresh data is really nice. 

              • Re: SQL Searches in excel
                Ulf Stockburger

                Hi Rodney

                 

                In the Excel Query Builder, you can select the required columns and load all data records. Then you can search for the "PartNumber" with  Excel filters. In my opinion, this is the simplest way. However, if you need to join multiple tables, you probably won't get by without SQL.

                 

                BiI SAP-PDM

                  • Re: SQL Searches in excel
                    Rodney Martin

                    Makes sense.  There are a lot of tables out there.  I was hoping for some help in knowing which ones to join. 

                      • Re: SQL Searches in excel
                        Jacob Corder

                        i told you which ones to join above.

                        • Re: SQL Searches in excel
                          Ulf Stockburger

                          Hello Rodney,

                           

                          If you are familiar with Excel macros, this might help you. The Excel file containing the macro must contain two sheets with the names from the constants tab00 and tab01. Adjust the constants connString, tab00, tab01 and insert the code into a new module. Type the filename filter in tab00 cell(A1). For example, %.sldasm for all assemblies. When the macro is executed, you receive the current status of the files. Note that this is only an example and no error handling is implemented. For security reasons, you should store the SQL query in a Stored Procedure, since this cannot be modified without authorizations. I would also create an user who only has authorization to read the tables or columns that are really needed.

                           

                          Option Explicit On

                          Const connString = "Provider=SQLOLEDB.1;User ID=userName;Password=userPassword;Data Source=serverName;Initial Catalog=dbName"

                          Const tab00 = "Tab00"

                          Const tab01 = "Tab01"

                           

                          Sub GetStatus()

                              ' Get file name

                              Dim fName As String

                              fName = Worksheets(tab00).Cells(1, 1)

                              If fName <> "" Then

                                  ' Clear sheet

                                  Worksheets(tab01).Activate

                                  Worksheets(tab01).Cells.Select

                                  Selection.ClearContents

                                  Worksheets(tab01).Cells(1, 1).Select

                                  Dim adoCn As Object

                                  Set adoCn = CreateObject("ADODB.Connection")

                                  Dim adoRs As Object

                                  Set adoRs = CreateObject("ADODB.Recordset")

                                  adoCn.ConnectionString = connString

                                  Dim sqlQuery As String

                                  sqlQuery = "SELECT T0.Filename, T1.Name FROM Documents T0 INNER JOIN Status T1 " _

                                      & "ON T0.CurrentStatusID = T1.StatusID WHERE T0.Filename Like N'" & fName & "'"

                                  adoCn.Open

                                  If adoCn.State = 1 Then

                                      adoRs.Open sqlQuery, adoCn, 0, 1

                                      If Not (adoRs Is Nothing) Then

                                          Worksheets(tab01).Range("A2").CopyFromRecordset adoRs

                                      End If

                                  End If

                                  If Not (adoRs Is Nothing) Then

                                      If adoRs.State = 1 Then

                                          adoRs.Close

                                      End If

                                      Set adoRs = Nothing

                                  End If

                                  If Not (adoCn Is Nothing) Then

                                      If adoCn.State = 1 Then

                                          adoCn.Close

                                      End If

                                      Set adoCn = Nothing

                                  End If

                              End If

                          End Sub

                           

                          BiI SAP-PDM

                      • Re: SQL Searches in excel
                        Michael Dekoning

                        Why not create a Search Result column set, associate it with a Search Card and export the results to a CSV file?

                          • Re: SQL Searches in excel
                            Rodney Martin

                            It is a nice idea, but the result would not be "live".  Its would be a one-time dump.  I need to have a list that someone can work in, and have data update as the components in the design are released, checked out/pending, etc.

                              • Re: SQL Searches in excel
                                Jacob Corder

                                i say get rid of excel.  write a stand alone application.  Avoid Interop code at all cost.

                                 

                                Rodney, are you familar with sql queries and joins?

                                 

                                i figured you were so i left it at what i posted earlier. if you need the joins let me know.

                                  • Re: SQL Searches in excel
                                    Rodney Martin

                                    I have not written an SQL query.  I have just used the query builder in Access & Excel to build them with the GUI. 

                                     

                                    I only want excel because you can build a simple spreadsheet, and do other functions, but then have a live link to the part data.  I have used this in an SAP environment in the past, and the time savings to just normal individuals is tremendous.

                                     

                                    I eventually want to turn this into functions in excel... such as:::  =SWPartName(PartNo)  Returns the part name/description of a part based on the input part number.  I can have many such functions for a lot of the data.  Anyone can then get this info using simple excel functions.

                                     

                                    Another way I could foresee doing this is if I can have SW dump a report every so often that my excel can connect to and use no queries.  Is it possible to run a report (I don't have PDM Pro at the moment) and have it update every hour or so?  Then, its just a vlookup out of the resulting table.  Having data update every hour isn't perfect, but its close enough.

                                     

                                    Thoughts?

                                      • Re: SQL Searches in excel
                                        Jacob Corder

                                        i understand using excel. but for less time and suffering, you could write a simple .net form that self publishes that only allows end users to do what you want them to be able to do. 

                                         

                                        alternatively writing an excel addin is extremely easy also.

                                         

                                        The new excel addins also self publish when you deploy them.

                                        which is Awesome.

                                         

                                        Now if only solidworks was capable of doing anything cool like that. But they aren't. they are too busy making the program fat and slow and unstable.

                                • Re: SQL Searches in excel
                                  Rodney Martin

                                  Yes, I think the add-in would be the thing to try first.  But, since I havent dont those before... can you send me where I need to go to do it?  Also, I presume a data dump via a report is the way to get a file updated on a regular frequency.  But, that is a PDM Pro function... Yes?  I am simply not familiar enough with .net.  I'm savvy with many computer languages... perhaps I can pick it up if I am sent to where I can start seeing how that is done.  Any references?


                                  Thanks,
                                  Rod

                                    • Re: SQL Searches in excel
                                      Jacob Corder

                                      if you have visual studio

                                      start a new project

                                       

                                      they make it so easy to do now. 

                                      if you don't have visual studio you will need to get it to use this template.  it is the only way i have done it so beyond that i cant help.

                                       

                                      with an add-in like this, you can basically make it take over specific documents.

                                       

                                      so if it is for a report,  when the C2 field changes and its part number

                                      you can have it pre fill out everything else.

                                       

                                      add links to the file in enterprise to open , like right click  Part number cell and have a menu item say open file, location, ect

                                       

                                      i do this a lot to automate things internally here,  using a .net add in is the only way to do it in my opinion.

                                       

                                      one really cool thing is if you have office 365 at your company,  these can be deployed through the website to whomever you chose should get it.