Cell Overwritten Check

I wrote a routine in .NET for SW09 SP4.1 for checking to see if cells of the BOM have been manually overwritten. The routine works wonderfully in SW09 and WinXP x64.

We are going to be upgrading to Win7x64 and SW2011 SP3.0 so I am upgrading and testing the addin that I created. Everything works great exscept for this cell checker. On a drawing in SW09 the routine takes about 6 seconds to complete. That same drawing in SW11, it takes 12MINUTES, same results after saving the drawing in 2011 format. The drawing itself has 2 configs on the BOM and 45 line items.

I think I have narrowed it down to the code that clears out the value to check the default (commented below in the code). Can anyone here see anything that I am missing? I really need to get this working properly.

I am using MS Visual Studio Express 2010

Public Sub subCellChecker(ByVal SW As SldWorks.SldWorks)

Const strApp As String = "Cell Checker v1.02"

Dim swApp As SldWorks.SldWorks

Dim swModel As SldWorks.ModelDoc2

Dim SelMgr As Object

Dim swTableAnnotation As SldWorks.TableAnnotation

Dim pb As SldWorks.UserProgressBar = Nothing

Dim pbPosition As Integer

Dim pbStatus As Boolean

Dim retVal As Boolean

Dim lRet As Long

Dim SelObjType As Long

Dim TableAnnotationType As Long

Dim nRowCount As Long

Dim nRCount As Long

Dim nColCount As Long

Dim nCCount As Long

Dim nDescriptionCol As Long

Dim nShtCol As Long

Dim sTargetPN As String = ""

Dim sTemp As String

Dim bChanged As Boolean = False

Dim bShtCol As Boolean = False

Dim appExcel As Object

Dim wbkReport As Object

Dim wksReport As Object

swModel = SW.ActiveDoc

SelMgr = swModel.SelectionManager

SelObjType = SelMgr.GetSelectedObjectType2(1)

If SelObjType <> 98 Then 'swSelANNOTATIONTABLES

MsgBox(Prompt:=

"You must select a BOM table in the drawing before running this macro.", Buttons:=MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Title:=strApp & " " & strAppVersion)

Exit Sub

End If

swTableAnnotation = SelMgr.GetSelectedObject5(1)

TableAnnotationType = swTableAnnotation.Type

If TableAnnotationType <> 2 Then 'swTableAnnotation_BillOfMaterials

MsgBox(Prompt:=

"You must select a BOM table in the drawing before running this macro.", Buttons:=MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Title:=strApp & " " & strAppVersion)

Exit Sub

End If

nRowCount = swTableAnnotation.RowCount

nColCount = swTableAnnotation.ColumnCount

retVal = SW.GetUserProgressBar(pb)

pbPosition = 0

pbStatus = pb.Start(pbPosition, nRowCount, strApp &

" Initializing...")

appExcel = CreateObject(

"Excel.Application")

wbkReport = appExcel.Workbooks.Add

wksReport = wbkReport.ActiveSheet

For nCCount = 0 To nColCount 'set format before hand so we dont lose the formatting

wksReport.Columns(nCCount + 1).EntireColumn.numberformat =

"@"

Next nCCount

For nRCount = 1 To nRowCount 'dont check top row

If nRCount > 2 Then

pb.UpdateTitle(strApp &

" Checking row " & nRCount - 2 & " of " & nRowCount - 2)

End If

For nCCount = 0 To nColCount - 1

sTemp = swTableAnnotation.Text(nRCount, nCCount)

If sTemp = "SHT" Then

nShtCol = nCCount

bShtCol =

True

End If

If bShtCol = True Then

If nCCount <> nShtCol Then

If nRCount > 1 Then swTableAnnotation.Text(nRCount, nCCount) = ""

End If

Else

'very slow on this line with large multi config BOMs

If nRCount > 1 Then swTableAnnotation.Text(nRCount, nCCount) = ""

End If

wksReport.Cells(nRCount, nCCount + 1) = sTemp

If sTemp <> swTableAnnotation.Text(nRCount, nCCount) And nRCount <> 1 Then

If bShtCol = True Then

If nCCount <> nShtCol Then 'dont highlight SHT col since it is always overwritten

bChanged =

True

wksReport.Cells(nRCount, nCCount + 1).Interior.ColorIndex = 6

swTableAnnotation.Text(nRCount, nCCount) = sTemp

End If

Else

bChanged =

True

wksReport.Cells(nRCount, nCCount + 1).Interior.ColorIndex = 6

swTableAnnotation.Text(nRCount, nCCount) = sTemp

End If

End If

Next nCCount

pbPosition = pbPosition + 1

lRet = pb.UpdateProgress(pbPosition)

Next nRCount

For nCCount = 0 To nColCount

sTemp = wksReport.Cells(1, nCCount + 1).value

wksReport.Columns(nCCount + 1).EntireColumn.AutoFit()

If UCase(sTemp) <> "DESCRIPTION" Then

wksReport.Columns(nCCount + 1).HorizontalAlignment = -4108

'literal value for center

Else

nDescriptionCol = nCCount + 1

End If

Next nCCount

wksReport.Cells(nRowCount + 2, nDescriptionCol - 1).Interior.ColorIndex = 6

wksReport.Cells(nRowCount + 2, nDescriptionCol).value =

"'= Denotes a SolidWorks B.O.M. table cell that has been overwritten from its original linked value."

wksReport.Cells(nRowCount + 3, nDescriptionCol).value =

"Exported: " & Now

pbPosition = 0

lRet = pb.UpdateProgress(pbPosition)

pb.UpdateTitle(strApp)

If bChanged = True Then 'only save if a cell has been overwritten

wbkReport.SaveAs(swModel.GetPathName &

"_CELL_CHECK_REPORT.xls")

appExcel.Visible =

True

Else

MsgBox(

"No cells in the B.O.M. have been overwritten.", MsgBoxStyle.OkOnly, strApp)

wbkReport.close(

False)

End If

pb.End()

pb =

Nothing

wbkReport =

Nothing

wksReport =

Nothing

System.Runtime.InteropServices.

Marshal.ReleaseComObject(appExcel)

appExcel =

Nothing

GC.Collect()

GC.WaitForPendingFinalizers()

swTableAnnotation =

Nothing

SelMgr =

Nothing

swModel =

Nothing

swApp =

Nothing

End Sub

SolidworksApi macros