Calling excel from solidworks works 1 time out of 2

Question

This may sound a little bit dumb, but I have never experienced anything like this before with SolidWorks macro. I have written a SolidWorks macro that inserts a BOM table into an assembly saves it as excel, and adds needed formulas to an excel file. However it works 1 time out of 2- 1st time- all good, 2nd time I get an error- "Run-time error '1004' Method 'Rows' of object '_Global' Failed", 3rd time- all good, 4th time I get the same error and so on and so on. I'm really new to excel macro so I don't know if I'm missing something or just stupid?

Option Explicit

Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2
Dim swBOMAnnotation As SldWorks.BomTableAnnotation
Dim i As Integer
Dim nNumRow As Variant
Dim swTableAnn As SldWorks.TableAnnotation
Dim swAnn As SldWorks.Annotation
Dim swModelDocExt As SldWorks.ModelDocExtension
Dim template As String
Dim fType As String
Dim configuration As String

'excel variables
Dim x1App As Excel.Application
Dim xlWB As Excel.Workbook
Dim NextRow As Long

Sub main()
    
Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swModelDocExt = swModel.Extension
template = "C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS\lang\english\bom-all.sldbomtbt"

fType = swBomType_PartsOnly
configuration = "Default"
    
    Set swBOMAnnotation = swModelDocExt.InsertBomTable3(template, 770, 240, fType, configuration, False, 2, True)
    
    Dim path As String
    path = Left(swModel.GetPathName, InStrRev(swModel.GetPathName, "\"))
    Dim fpath As String
    fpath = Format(path & "BOM\")
    
    On Error Resume Next
    MkDir (fpath)
    On Error GoTo 0
    
    Dim fName As String
    fName = Format(fpath & "TEST.xls")
    swBOMAnnotation.SaveAsExcel fName, False, False

    Set swTableAnn = swBOMAnnotation
    Set swAnn = swTableAnn.GetAnnotation
    swAnn.Select3 False, Nothing
    swModel.EditDelete
    
        'Excel part
        Set x1App = New Excel.Application
        
        x1App.Visible = True
        
        Set xlWB = x1App.Workbooks.Open(fName)
        
        With Range("G3:G" & Cells(Rows.Count, "C").End(xlUp).Row)
            .Formula = "=C3*F3"
        End With
    
        NextRow = Range("G" & Rows.Count).End(xlUp).Row + 1

        Range("G" & NextRow).Formula = "=SUM(G2:G" & NextRow - 1 & ")"
   

End Sub

Answer 1

Not sure what's causing the behavior you're describing but here are a few thoughts that might point you in the right direction.

• Objects in macros are persistent, meaning swModel (and other objects) will still exist after the macro is run. This is why you need to set it to 'Nothing' before using it again.
• "Rows" is not defined anywhere so I'm surprised that code works at all. It must be late binding it to something... Rows is a method for an excel range but you're not using it that way. (range.Rows)

Try getting the row count explicitly in a double and using that instead. I suspect that will fix your issue.