SQL Searches in excel

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

SolidworksApi macros
Answers 16
JC
JC 2017-11-29 Best Answer

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

JC
JC 2017-11-29

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

US
US 2017-11-29

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

RM
RM 2017-11-29

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. 

RM
RM 2017-11-29

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

JC
JC 2017-11-29

i told you which ones to join above.

RM
RM 2017-11-29

Ah yes, seems I didn't pick "show earlier"... ugh...


Thanks.

MD
MD 2017-11-29

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

comment imagecomment image
RM
RM 2017-11-29

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.

JC
JC 2017-11-29

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.

DB
DB 2017-11-30

Hi Rodney,

Just a small piece of advice.

Always try to view replies from the main thread itself rather than just your inbox otherwise you could possibly miss attached pictures, links or attached zipped files that people may have sent you.

Dave.

US
US 2017-11-30

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

RM
RM 2017-11-30

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?

JC
JC 2017-11-30

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.

RM
RM 2017-11-30

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

JC
JC 2017-11-30

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.

comment imagecomment image