AnsweredAssumed Answered

Get Info out excel in SW

Question asked by Jo Nijs on Jan 2, 2013
Latest reply on Jan 2, 2013 by Jo Nijs



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




    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.Visible = False


Debug.Print strSAPNR


'add custom property SAP NR

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




    swModel.Save3 False


Set swModel = swApp.ActiveDoc


swApp.CloseDoc (swModel.GetPathName)



End Sub






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