3 Replies Latest reply on Apr 17, 2018 2:25 AM by Thomas Voetmann

    Trouble using Vlookup function in VBA referencing Excel

    Ben Van Der Linde

      Hi Soildworks Forum,


      Can someone please help me with this code.  The code:

      1. calls a custom property

      2. looks up the custom property value in a excel table with Vlookup

      3. Export custom properties (have not written this part of code yet)


      Unfortunately I cannot get past the Vlookup section and I don't understand why.  Can someone please help me understand why I keep getting a run-time error '438' Object doesn't support this property or method.


      Thank you in advanced for the help


      Option Explicit


      Dim swApp As SldWorks.SldWorks

      Dim excApp As Excel.Application

      Dim excWorkbook As Excel.Workbook

      Dim swModel As SldWorks.ModelDoc2

      Dim swCustPrpMgr As SldWorks.CustomPropertyManager


      Sub main()


      'Soildworks set

      Set swApp = Application.SldWorks

      Set swModel = swApp.ActiveDoc


      'stop screen updating

      'Application.ScreenUpdating = False


      Dim sDriverCode1 As String 'Driver code

      Dim rValOut As String 'Unsure

      Dim bool As Boolean 'unsure


      'current value of custom propery DRIVER

          Set swCustPrpMgr = swModel.Extension.CustomPropertyManager("")

          bool = swCustPrpMgr.Get3("Driver", True, sDriverCode1, rValOut)


          Debug.Print "Driver Code:              " & sDriverCode1

          Debug.Print "Evaluated value:          " & rValOut

          Debug.Print "Up-to-date data:          " & bool


      'excel set

      Set excApp = GetObject(, "Excel.Application")


      Dim sDriverPath As String

      Dim sDriverExc As String

      Dim sDriverFile As String

      Dim sDriverSheet As String

      Dim sDriverRange As String


      sDriverPath = "P:\Path\"

      sDriverFile = "Driver.xlsx"

      sDriverSheet = "Sheet1"

      sDriverRange = "A1:H1000"



      'open Driver Excel

      Set excWorkbook = excApp.Workbooks.Open(sDriverPath & sDriverFile)


      'On Error GoTo MyErrorHandler


      'Vlookup of file

      Dim sManfact As String


      sManfact = excApp.WorksheetFuction.Vlookup(sDriverCode1, excWorkbook.Sheets(sDriverSheet).Range(sDriverRange), 2, False)


      Debug.Print sManfact


      Exit Sub



          If Err.Number = 1004 Then

              MsgBox "ItemNumber Not Present in the table."

          End If



      End Sub