Hello,

We are migrating our ERP system from AS400 to SAP.

The SAP system we are gonna use is already running in Italy with our mothercompany. So we have to use their article code in stead of our own old ones.

The biggest problem for now is that in our Weldment profiles all articlenumbers have to be replaced, or add the new SAP article code.

So to automate this action (lots of weldment profile) I created a macro. Everything works 'fine' except the part were I have to open the excel file with old and new codes, search the old code and copy the corresponding new code.

Option Explicit

Dim swApp        As SldWorks.SldWorks

Dim swModel      As Object

Dim sFileName    As String, Path As String

Dim swCustProp As CustomPropertyManager

Dim swModelDocExt As ModelDocExtension

Dim File As String

Dim boolstatus   As Boolean

Dim longstatus   As Long, longwarnings As Long

Dim swDocSpec As SldWorks.DocumentSpecification

Dim strSAPNR As String

Dim strArtnr As String

Dim retval As String

Dim val As String

Dim valout As String

Dim bool As Boolean

Dim xlApp As Excel.Application

Sub main()

    Set swApp = Application.SldWorks

    Path = BrowseFolder("Select a Path/Folder")

    Path = Path + "\"

    sFileName = Dir(Path & "*.*lfp") ' Library parts

    Do Until sFileName = ""

    Debug.Print sFileName

    File = Path + sFileName

    Debug.Print File

Set swDocSpec = swApp.GetOpenDocSpec(File)

Set swModel = swApp.OpenDoc6(File, 1, 0, "", longstatus, longwarnings)

swApp.ActivateDoc2 sFileName, False, 0

'get custom property Artikelnr

    Set swModelDocExt = swModel.Extension

    Set swCustProp = swModelDocExt.CustomPropertyManager("")

    bool = swCustProp.Get4("ARTIKELNR", False, strArtnr, valout)

Debug.Print strArtnr

' Open excel file

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True

    xlApp.Workbooks.Open "H:\solidworks\Macro\test 2012\Artikelnummern für PDM.xls", True, False

'**********Searching...

    strSAPNR = xlApp.WorksheetFunction.VLookup(strArtnr, Range("A:C"), 3, False) -> This won't work. In excel API it works, but here it doesn't.

'**********

' Close Excel file

    xlApp.Workbooks.Close

    xlApp.Visible = False

Debug.Print strSAPNR

'add custom property SAP NR

    retval = swModel.AddCustomInfo3("", "SAP-NR", swCustomInfoText, strSAPNR)

    swModel.EditRebuild3

    swModel.Save3 False

Set swModel = swApp.ActiveDoc

swApp.CloseDoc (swModel.GetPathName)

Loop

End Sub

My knowledge of vba is not big enough to fix this problem.