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.
SolidworksApi macros