0 Replies Latest reply on Mar 2, 2016 5:15 PM by Mike Smith

    Help: Change location of internal Design table Excel Links

    Mike Smith

      I am very new at programming so please excuse my ignorance. I have a bunch of part and assembly files that all have internal Design Tables. These design tables used to be external and they were all linked to a "Master" spreadsheet that the data would be entered into which in turn would update all of the other spreadsheets which in turn when I opened the parts and assemblies would be updated by the spreadsheets. This process takes way to long because for every SolidWorks part or assembly that was opened it would also have to open the corresponding spreadsheet every time it was opened or saved. I have since moved all of my spreadsheets to internal design tables but they all still need to link to the "Master" spreadsheet so that the data entered in the "Master" spreadsheet can update each design table as I open it and run a macro. The problem I am having is that since these parts are all in a folder along with the "Master" spreadsheet, when I copy the folder to another location and rename it the link between the internal design tables and the external "Master" spreadsheet becomes broken. This use to happen when they were all external as well so I would need to go to the "Data" tab in Excel and click on "Update Links"  so I could select the new location of the "Master" spreadsheet. I have tried to incorporate this into my macro but I keep getting a Run-timer error '91': Object variable or With block variable not set". Again I am very new at programming and can't seem to figure out how to fix this. any help would be greatly appreciated. here is the code I have so far.

       

      Option Explicit

       

       

      Dim swApp       As SldWorks.SldWorks

      Dim Part        As SldWorks.ModelDoc2

      Dim boolstatus  As Boolean

      Dim longstatus  As Long, longwarnings As Long

      Dim filePath    As String

      Dim swModel     As SldWorks.ModelDoc2

      Dim xlApp       As Excel.Application

      Dim xlWB        As Excel.Workbook

       

      Sub main()

      Set swApp = _

      Application.SldWorks

      Set swModel = swApp.ActiveDoc

       

      'Get the current working directory

      filePath = CurDir$

       

      Set Part = swApp.ActiveDoc

       

      'Open the design table to allow for updating

      boolstatus = Part.Extension.SelectByID2("Design Table", "DESIGNTABLE", 0, 0, 0, False, 0, Nothing, 0)

      Part.InsertFamilyTableEdit

      Set xlApp = New Excel.Application

      Set xlWB = ActiveWorkbook

       

      'Update Design Table link to external spreadsheet

      ActiveWorkbook.ChangeLink Name:= _

             filePath & "\Layout.xlsx", NewName:= _

             "Layout.xlsx", Type:=xlExcelLinks

      Dim myModelView As Object

      Part.CloseFamilyTable

      End Sub