AnsweredAssumed Answered

Help: Change location of internal Design table Excel Links

Question asked by Mike Smith on Mar 2, 2016

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 = _


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)


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


End Sub