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

       

      MyErrorHandler:

          If Err.Number = 1004 Then

              MsgBox "ItemNumber Not Present in the table."

          End If

       

       

      End Sub