AnsweredAssumed Answered

Trouble using Vlookup function in VBA referencing Excel

Question asked by Ben Van Der Linde on Apr 16, 2018
Latest reply on Apr 17, 2018 by Thomas Voetmann

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

Outcomes