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,
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,
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.
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.
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
Jeremiah,
I am not a programmer, nor even a hack; but I will give this a go and see what happens.
Thanks for your assistance,
Steve
Hi Jeremiah,
Thank you for your contribution on here. I'm sure so many including me could use your knowledge and appreciate your help.
Do you use shell command in Dispatch to call for the add-in ?
Thanks,
James
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.
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.