AnsweredAssumed Answered

Excel VBA for pulling PDM BOMs

Question asked by Michael Kuhn on Nov 25, 2020
Latest reply on Dec 3, 2020 by Michael Kuhn

Like • Show 0 Likes0
Comment • 6

I have a spreadsheet tracking orders for over 20 individual BOMs at various engineering revisions. I am attempting to use the Solidworks API for PDM to pull the BOMs from SLDASM files and export updates to each excel sheet.

I've gotten started writing the VBA in excel to do this, but I've run into a roadblock as it seems there are aspects of the API that are not supported in VBA, but are in VB.NET.

Is it possible to use excel VBA by itself to pull BOM data from PDM, or is it a waste of time trying to make this happen without the use of VB.NET or some other script? If it is, could someone point me to an example of how to make it work?

thank you

Wayne Matus Nov 30, 2020 4:34 PM

Correct Answer

For that you can simply change this:

Dim vault1 As IEdmVault5 = New EdmVault5()
Dim vault As IEdmVault8 = DirectCast(vault1, IEdmVault8)

To:

Dim vault1 As IEdmVault5

Dim vault As IEdmVault8

Set vault1 = New EdmVault5

Set vault = vault1

There maybe some other tricks later on in the code to convert an array of objects into VBA recognized syntax.

See the reply in context

No one else had this question

Outcomes

Wayne Matus Nov 25, 2020 12:17 PM

There are a few things that cannot be done with VBA and several things require different ways. What specifically are you having trouble with?

Actions

Michael Kuhn @ Wayne Matus on Nov 30, 2020 1:19 PM

In the example vb.net code I'm following (here), it requires logging into the vault as EdmVault5, then casting to type IEdmVault7 for accessing ppoVersions. This is apparently needed as an argument to the GetComputedBOM function.

I haven't seen many other examples for pulling BOMs out of SLDASMs in PDM... if there's another method I can use I'd love to hear about it.

Actions

Wayne Matus @ Michael Kuhn on Nov 30, 2020 4:34 PM

For that you can simply change this:

Dim vault1 As IEdmVault5 = New EdmVault5()
Dim vault As IEdmVault8 = DirectCast(vault1, IEdmVault8)

To:

Dim vault1 As IEdmVault5

Dim vault As IEdmVault8

Set vault1 = New EdmVault5

Set vault = vault1

There maybe some other tricks later on in the code to convert an array of objects into VBA recognized syntax.

Actions

Michael Kuhn @ Wayne Matus on Dec 2, 2020 11:53 AM

That worked great, thanks!

The second issue I'm having is passing an array to the GetBomLayouts function. My code is as follows:

Set bomMgr = vault2.CreateUtility(EdmUtility.EdmUtil_BomMgr)
Dim ppoRetLayouts() As EdmBomLayout
Dim ppoRetLayout As EdmBomLayout

bomMgr.GetBomLayouts (ppoRetLayouts)

I get a "Type Mismatch: array or user-defined type expected" error... not sure if excel likes an uninitialized array and I'm not sure how to get around it

Actions

Wayne Matus @ Michael Kuhn on Dec 3, 2020 11:00 AM

In VBA you do not use parentheses if you do not have an equal sign. So use this:

bomMgr.GetBomLayouts ppoRetLayouts

Actions

Michael Kuhn @ Wayne Matus on Dec 3, 2020 3:38 PM

right again! thank you so much, I got it all working perfectly now

Actions

6 Replies

Wayne Matus Nov 25, 2020 12:17 PM
Mark Correct
Correct Answer
There are a few things that cannot be done with VBA and several things require different ways. What specifically are you having trouble with?
Like • Show 0 Likes0
Actions
- Michael Kuhn @ Wayne Matus on Nov 30, 2020 1:19 PM
  Mark Correct
  Correct Answer
  In the example vb.net code I'm following (here), it requires logging into the vault as EdmVault5, then casting to type IEdmVault7 for accessing ppoVersions. This is apparently needed as an argument to the GetComputedBOM function.
  
  I haven't seen many other examples for pulling BOMs out of SLDASMs in PDM... if there's another method I can use I'd love to hear about it.
  Like • Show 0 Likes0
  Actions
  - Wayne Matus @ Michael Kuhn on Nov 30, 2020 4:34 PM
    Unmark Correct
    Correct Answer
    For that you can simply change this:
    
    Dim vault1 As IEdmVault5 = New EdmVault5()
    Dim vault As IEdmVault8 = DirectCast(vault1, IEdmVault8)
    
    To:
    
    Dim vault1 As IEdmVault5
    Dim vault As IEdmVault8
    Set vault1 = New EdmVault5
    Set vault = vault1
    
    There maybe some other tricks later on in the code to convert an array of objects into VBA recognized syntax.
    Like • Show 0 Likes0
    Actions
    - Michael Kuhn @ Wayne Matus on Dec 2, 2020 11:53 AM
      Mark Correct
      Correct Answer
      That worked great, thanks!
      
      The second issue I'm having is passing an array to the GetBomLayouts function. My code is as follows:
      
      Set bomMgr = vault2.CreateUtility(EdmUtility.EdmUtil_BomMgr)
      Dim ppoRetLayouts() As EdmBomLayout
      Dim ppoRetLayout As EdmBomLayout
      
      bomMgr.GetBomLayouts (ppoRetLayouts)
      
      I get a "Type Mismatch: array or user-defined type expected" error... not sure if excel likes an uninitialized array and I'm not sure how to get around it
      Like • Show 0 Likes0
      Actions
      - Wayne Matus @ Michael Kuhn on Dec 3, 2020 11:00 AM
        Mark Correct
        Correct Answer
        In VBA you do not use parentheses if you do not have an equal sign. So use this:
        
        bomMgr.GetBomLayouts ppoRetLayouts
        Like • Show 0 Likes0
        Actions
        Michael Kuhn @ Wayne Matus on Dec 3, 2020 3:38 PM
        Mark Correct
        Correct Answer
        right again! thank you so much, I got it all working perfectly now
        Like • Show 0 Likes0
        Actions

Excel VBA for pulling PDM BOMs

Outcomes

Related Content

Recommended Content