7 Replies Latest reply on Dec 2, 2014 8:02 PM by James Guilford

    Item Master Export

    James Guilford

      Hello,

      Exporting BOM or Item Master to a csv is a lengthy procedure, Is there an easier way of achieving a BOM or Item Master. (Instead of going to BOM tab then export to CSV and then save excel)

       

      Thanks,

        • Re: Item Master Export
          Brent Lewis

          I am most interested in this as well since that is my next project I was looking at. ( why you guys have seen me around here chatting for past week ).

          I myself am looking for an easier way to mimic the BomProc (insert number here )  stored procedure depending on bom builds.

          Currently Our SW frame designers are expected to export a bom from SW so that can be imported into a custom SQL background/Access front end interface system for costing. I do not like this at all, and I want to take full advantage of EPDM capabilities. I do think refining the EPDM Bom to our needs and then exporting that would be better. If done correctly anyone I give permission could and not cause extra work on the design team. I have not created a dispatch yet and I have studied the BomProc SP. That is a little beyond me at the moment because I only know a few tables to create my own SP- ( I have to baby step into a new database). Any advice/guidance here would be appreciated.

            • Re: Item Master Export
              Jeremiah Davis

              Hello Gentlemen,

               

              I have actually written a few different options to do this very thing.

               

              Option 1: Create a SOLIDWORKS Macro and convert it to a task. This is probably the easiest to find information on how-to but requires SOLIDWORKS fire up in order to create your export. I have done this to export the BOM and item master for assemblies as well as the item master for all part files.

               

              Option 2: Create a stand-alone program or add-in that can be used to generate this information. In my stand alone program (written for a customer), I generate a tab-delimited text document, but do so through Excel. Could just as easily convert it straight to Excel, or scrap the output format all together and generate raw text or database entries.

               

              Option 3: Review Dispatch and the Generate Parameter File functionality. From an implementation standpoint, this is going to by far be the easiest. It will not export the BOM, but will definitely be able to capture all other item master information.

                • Re: Item Master Export
                  Stephen Reed

                  Jeremiah,

                   

                  Could you elaborate on option 2?  I have a list of assemblies in one epdm project folder that I need to export the BOM out to a CSV file or Excel file and be able to do it as a batch. By that I mean select all the assemblies and have each bom exported to individual CSV files.  Any ideas on how to do that would be most appreciated.

                   

                  Thanks,

                  Steve R.

                    • Re: Item Master Export
                      Jeremiah Davis

                      Hello Stephen,

                       

                      The way I implemented option 2 was by writing a stand alone program (code to be pasted in below). This program is then called with Dispatch. If you are using this for multiple assembly files, I would consider using the "For All Documents" and "End For All Documents" with a shell execute in the middle.

                       

                      Let me also preface with a couple of other items regarding the code I am about to paste in here.

                       

                      1) I am not a programmer. I am a hack at best. So this is very crude and not always the best / most efficient code. But it works.

                      2) This particular program has a form with it that is used in the event that the assembly has multiple configurations. It populates those configurations into a drop list and prompts which one you would like to export. If there is only one configuration, there is no dialog that appears, it simply exports the one configuration.

                      3) I provide this as is with no implied or explicit support provided by SOLIDWORKS. It is intended as sample code.

                       

                      And the code, complete minus the form:

                       

                      Imports EdmLib

                      Imports System.IO

                      Imports Microsoft.Office.Interop

                       

                       

                      Public Class Main

                          Dim vault As IEdmVault12

                          Dim epdmfile As IEdmFile8

                          Dim epdmFolder As IEdmFolder7

                          Dim xlColumns As Array

                          Private Sub Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load

                       

                              Dim epdmList As IEdmStrLst5

                              Dim epdmpos As IEdmPos5

                              Dim argFileName As String = My.Application.CommandLineArgs.Item(0)

                              Dim CurConfig As String

                              Dim strFileName As String

                              Dim strLetters As String

                              strLetters = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"

                              xlColumns = Split(strLetters, ",")

                              If LCase(Microsoft.VisualBasic.Right(argFileName, 3)) <> "asm" Then

                                  MsgBox("The selected file is not an assembly!", MsgBoxStyle.Exclamation, "Not an Assembly")

                                  End

                              End If

                       

                              vault = New EdmVault5()

                       

                              Dim vVaultViews() As EdmViewInfo

                              Dim sVaultName As String = ""

                              Dim i As Integer

                       

                              vault.GetVaultViews(vVaultViews, True)

                       

                              If vVaultViews.Count <> 0 Then

                                  If vVaultViews.Count = 1 Then

                                      sVaultName = vVaultViews(0).mbsVaultName

                                  Else

                                      For i = LBound(vVaultViews) To UBound(vVaultViews)

                                          If InStr(argFileName, vVaultViews(i).mbsVaultName) > 0 And InStr(argFileName, vVaultViews(i).mbsVaultName) < 10 Then

                                              sVaultName = vVaultViews(i).mbsVaultName

                                          End If

                                      Next

                                      If sVaultName = "" Then

                                          MsgBox("The vault was not found, please log out of unnecessary vaults", vbCritical, "Vault Not Found")

                                          End

                                      End If

                                  End If

                              Else

                                  MsgBox("Not logged into any vaults!", vbCritical, "Login Error")

                                  End

                              End If

                              vault.LoginAuto(sVaultName, 0)

                       

                              epdmfile = vault.GetFileFromPath(argFileName, epdmFolder)

                       

                              epdmList = epdmfile.GetConfigurations

                              Me.cmbConfigs.Items.Clear()

                       

                              strFileName = Microsoft.VisualBasic.Left(epdmfile.Name, InStr(epdmfile.Name, ".") - 1)

                       

                              epdmpos = epdmList.GetHeadPosition

                              While Not epdmpos.IsNull

                                  CurConfig = epdmList.GetNext(epdmpos)

                                  If CurConfig <> "@" Then

                                      Me.cmbConfigs.Items.Add(CurConfig)

                                  End If

                       

                              End While

                       

                              If Me.cmbConfigs.Items.Count = 1 Then

                                  'MsgBox("Exporting information for " & Me.cmbConfigs.Items.Item(0), MsgBoxStyle.Information, "Exporting")

                                  ExportBOM(strFileName, Me.cmbConfigs.Items.Item(0))

                                  End

                              ElseIf Me.cmbConfigs.Items.Count = 0 Then

                                  MsgBox("No valid configurations exist", MsgBoxStyle.Exclamation, "Error Exporting")

                                  End

                              End If

                       

                          End Sub

                       

                          Private Sub ExportBOM(ByVal strFileName As String, ByVal strConfig As String)

                              Dim epdmBOM As IEdmBomView2

                              Dim epdmBOMRows As Array

                              Dim epdmBOMRow As IEdmBomCell

                              Dim epdmColumns As Array

                              Dim epdmcolumn As EdmBomColumn

                              Dim retbool As Boolean

                              Dim i As Integer = 1

                              Dim j As Integer = 2

                              Dim strValue As String

                              Dim strVarValue As String

                              Dim strOutConfig As String

                              '**********This is where the export directory is hard coded into the program

                              '**********Set to the preferred directory

                              Dim outdir As String = Environ("USERPROFILE") & "\Desktop\Output\"

                       

                              Dim xlCell As String

                       

                              epdmBOMRows = Array.CreateInstance(GetType(IEdmBomCell), 0)

                              epdmColumns = Array.CreateInstance(GetType(EdmBomColumn), 0)

                       

                              Try

                                  '********Use the name of the BOM that is being exported in place of the "BOM" below

                                  epdmBOM = epdmfile.GetComputedBOM("BOM", -1, strConfig, 1)

                                  epdmBOM.GetColumns(epdmColumns)

                              Catch ex As Exception

                                  MsgBox(ex.Message, MsgBoxStyle.Critical)

                              End Try

                       

                              Try

                                  epdmBOM.GetRows(epdmBOMRows)

                              Catch ex As Exception

                                  MsgBox(ex.Message, MsgBoxStyle.Critical)

                              End Try

                       

                              Dim ExcelApp As Excel.Application = Nothing

                              Dim xlWorkBooks As Excel.Workbooks = Nothing

                              Dim xlWorkBook As Excel.Workbook = Nothing

                              Dim xlSheet As Excel.Worksheet

                       

                              ExcelApp = New Excel.Application

                              xlWorkBooks = ExcelApp.Workbooks

                              xlWorkBook = xlWorkBooks.Add

                              xlSheet = xlWorkBook.Worksheets("Sheet1")

                              xlSheet.Range("A1").Value = "Level"

                       

                              For Each epdmcolumn In epdmColumns

                                  xlSheet.Range(xlColumns(i) & "1").Value = epdmcolumn.mbsCaption

                       

                                  i += 1

                              Next

                       

                              Dim lLevel As Long = 0

                              Dim strCurLevel As String = ""

                              Dim iNextLevel As Integer = 0

                              Dim LevelDifference As Long = 0

                       

                              i = 1

                              For Each epdmBOMRow In epdmBOMRows

                                  lLevel = epdmBOMRow.GetTreeLevel

                                  If lLevel = 1 Then

                                      If strCurLevel = "" Then

                                          strCurLevel = "1"

                                      Else

                                          If InStr(strCurLevel, ".") > 0 Then

                                              strCurLevel = Microsoft.VisualBasic.Left(strCurLevel, InStr(strCurLevel, "."))

                                          End If

                                          iNextLevel = CInt(strCurLevel) + 1

                                          strCurLevel = iNextLevel

                                      End If

                                  ElseIf lLevel > 1 Then

                                      LevelDifference = CharCount(strCurLevel, ".") + 1 - lLevel

                                      If LevelDifference = 0 Then

                                          iNextLevel = CInt(Microsoft.VisualBasic.Right(strCurLevel, Len(strCurLevel) - InStrRev(strCurLevel, "."))) + 1

                                          strCurLevel = Microsoft.VisualBasic.Left(strCurLevel, InStrRev(strCurLevel, ".")) & iNextLevel

                                      ElseIf LevelDifference < 0 Then

                                          strCurLevel &= ".1"

                                      ElseIf LevelDifference > 0 Then

                                          strCurLevel = Microsoft.VisualBasic.Left(strCurLevel, StrLocStart(strCurLevel, ".", lLevel) - 1)

                                          iNextLevel = CInt(Microsoft.VisualBasic.Right(strCurLevel, Len(strCurLevel) - InStrRev(strCurLevel, "."))) + 1

                                          strCurLevel = Microsoft.VisualBasic.Left(strCurLevel, InStrRev(strCurLevel, ".")) & iNextLevel

                                      End If

                       

                                  End If

                       

                                  xlSheet.Range("A" & j).NumberFormat = "@"

                                  xlSheet.Range("A" & j).Value = strCurLevel

                       

                                  For Each epdmcolumn In epdmColumns

                                      epdmBOMRow.GetVar(epdmcolumn.mlVariableID, epdmcolumn.meType, strValue, strVarValue, strOutConfig, retbool)

                                      xlCell = xlColumns(i) & j

                                      xlSheet.Range(xlCell).Value = strValue

                                      i += 1

                                  Next

                                  i = 1

                                  j += 1

                              Next

                              If File.Exists(outdir & strFileName & " - " & strConfig & ".txt") Then

                                  File.Delete(outdir & strFileName & " - " & strConfig & ".txt")

                              End If

                              Try

                                  xlSheet.SaveAs(outdir & strFileName & " - " & strConfig & ".txt", 42)

                              Catch ex As Exception

                                  MsgBox(ex.Message, MsgBoxStyle.Critical)

                              End Try

                       

                              xlWorkBook.Close(SaveChanges:=False)

                              ExcelApp.Quit()

                       

                       

                          End Sub

                       

                          Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click

                              End

                          End Sub

                       

                          Private Function CharCount(ByVal InputString As String, ByVal CompareString As String) As Integer

                              Dim i As Integer

                              CharCount = 0

                              If Len(CompareString) > 1 Then CompareString = Microsoft.VisualBasic.Left(CompareString, 1)

                       

                              For i = 1 To Len(InputString)

                                  If Microsoft.VisualBasic.Mid(InputString, i, 1) = CompareString Then CharCount += 1

                              Next

                       

                              Return CharCount

                          End Function

                       

                          Private Function StrLocStart(ByVal InputString As String, ByVal CompareString As String, ByVal Occurance As Integer) As Integer

                              Dim i As Integer

                              Dim InstanceNum As Integer = 0

                              StrLocStart = 0

                              If Len(CompareString) > 1 Then CompareString = Microsoft.VisualBasic.Left(CompareString, 1)

                       

                              For i = 1 To Len(InputString)

                                  If Microsoft.VisualBasic.Mid(InputString, i, 1) = CompareString Then InstanceNum += 1

                                  If InstanceNum = Occurance Then

                                      StrLocStart = i

                                      Return StrLocStart

                                      Occurance = -1

                                  End If

                       

                              Next

                       

                              Return StrLocStart

                          End Function

                       

                          Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

                              Dim strFileName As String

                       

                              strFileName = Microsoft.VisualBasic.Left(epdmfile.Name, InStr(epdmfile.Name, ".") - 1)

                              ExportBOM(strFileName, Me.cmbConfigs.SelectedItem.ToString)

                       

                              Me.Close()

                              End

                          End Sub

                      End Class

                      1 person found this helpful
                    • Re: Item Master Export
                      Brent Lewis

                      option 1, I would be doing if the process where to stay as is.

                      option 2, is still on the table for me when I get more familiar with the database structure. My thoughts would be a stored procedure that retrieves the data and inserts into the Costing SQL database from EPDM via linked servers. I am still plotting how I want this to work so I have time for this.

                       

                      Today I have been studying ( named boms ). this might being going in that direction regardless. The SW bill are not 100% complete anyway on our end and management doesn't want to burden them with including addition components that they are not use to dealing with. ( those are determined later down the line ) by other people.

                      So I am looking at possible pasting as a reference the additional components using named boms by other personnel in epdm. Then doing a quick transition for an auto XML export. ( might could dispatch that on check in).

                      Work is divided so no one single person is swamped/Bom should be accurate/ auto export and then auto import from static location to costing database.

                      This isn't 100% complete though, I have not shown management and have only tested pasting as reference/learning what a named bom was/ and just finished a working auto export to xml to a static location by transition.

                       

                      Thank You for your suggestions Jeremiah.