CODEWITHSUNDEEP
vbaexcel
Erik From Work
Erik From Work

Reputation: 1

Excel VBA error - Workbook does not respond when code runs with other macro-enabled workbooks open

Background

I have a dilemma. There is a workbook I have been working on for about a month. I have many named ranges, three queries, and several tables. One of the tables, (TABLE_Syteline_JobBills) is constructed from another table which is a query from a database. I refresh this other table (TABLE_Syteline_SingleLevel), 3 times, each time appending the data to the JobBills table.

The Problem

The code only works if no other Macro-Enabled Workbooks are open. If a Macro-Enabled Workbook is open, I get the error

Runtime Error - Automation Error - The object invoked has disconnected from its clients.

It occurs on the line:

.ListRows.Add(AlwaysInsert = True).Range = Row.Value

The code works without any error until a macro-enabled workbook is opened. A regular workbook does not cause the code to error. There will be no error when this is the only macro-enabled workbook open. Depending on which macro-enabled workbook you have open, the error might not happen until the third time through the nested for loop.

The Code

Here it is...the sub that causes the issue

'Option Explicit

Sub UpdatingTableJobBills()
'--------------------------------------------------------------------------------
'Programmer's Notes
'--------------------------------------------------------------------------------
'Copies three sets of data one at a time to one big table refreshing each time
'--------------------------------------
'Variable Declarations
'--------------------------------------
Dim JobNumber As String 'Variable Job Number
Dim RowCount As Integer 'Number of rows in table
Dim BOMSuffixes As Variant
Dim BOMSuffix As Variant
Dim Row As Range 'Used to loop through the different rows in the SingleLevel table we are going to insert into the JobBill table

'--------------------------------------
'Variable Definitions
'--------------------------------------
BOMSuffixes = Array("-conv", "-devices", "-electrical")
JobNumber = ThisWorkbook.Worksheets("Cost Analysis").Range("JobNumber").Value 'job number - read from named cell

'--------------------------------------
'Delete any old data
'--------------------------------------
WipeOutBillData '---this function simply deletes 3 table databodyranges if they aren't empty already

'--------------------------------------
'Loop through each query and copy the query results to the JobBills table
'--------------------------------------
For Each BOMSuffix In BOMSuffixes
    ThisWorkbook.Worksheets("BOM Query").Range("PartNumber").Value = JobNumber & BOMSuffix 'Changes part number to prepare for refresh
    ThisWorkbook.Connections("Syteline_Query_BOM").Refresh 'Refreshes specific connection to syteline
    With ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills")
        If WorksheetFunction.CountA(ThisWorkbook.Worksheets("BOM Query").Range("TABLE_Syteline_SingleLevel")) <> 0 Then
            For Each Row In ThisWorkbook.Worksheets("BOM Query").ListObjects("TABLE_Syteline_SingleLevel").DataBodyRange.Rows
                .ListRows.Add(AlwaysInsert:=True).Range = Row.Value  '******** The row behind the trouble *********
            Next Row
        End If
    End With
Next BOMSuffix

Set Row = Nothing

With ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills")
    If IsEmpty(ThisWorkbook.Worksheets("Job Data").Range("TABLE_Syteline_JobBills[Child]")) Then
        Call MsgBox("No data has been returned for job number: " & JobNumber, vbSystemModal, "No Data Returned")
        Exit Sub
    End If
    .DataBodyRange.Cells(2, .ListColumns("Extended Cost").index).Value = "=[Qty]*[Cost]" 'Redefines the value of column "Extended Cost"
End With
    

'--------------------------------------
'Sorts the table JobBills by descending Ext Cost
'--------------------------------------
ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills"). _
    Sort.SortFields.Clear
ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills"). _
    Sort.SortFields.Add Key:=Range( _
"TABLE_Syteline_JobBills[[#All],[Extended Cost]]"), SortOn:=xlSortOnValues, _
    Order:=xlDescending, DataOption:=xlSortNormal
With ThisWorkbook.Worksheets("Job Data").ListObjects( _
    "TABLE_Syteline_JobBills").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'--------------------------------------
'Deletes rows with an Extended Cost of Zero
'--------------------------------------

Dim Description As String
Dim RowMover As Integer
Dim ExtRowCounter As Integer

ExtRowCounter = 2

Do While Not IsEmpty(ThisWorkbook.Worksheets("Job Data").Cells(ExtRowCounter, ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills").ListColumns("Extended Cost").index))
    If ThisWorkbook.Worksheets("Job Data").Cells(ExtRowCounter, ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills").ListColumns("Extended Cost").index).Value = 0 Then
        ThisWorkbook.Worksheets("Job Data").Cells(ExtRowCounter, ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills").ListColumns("Extended Cost").index).EntireRow.Delete
        ExtRowCounter = ExtRowCounter - 1
    End If
    ExtRowCounter = ExtRowCounter + 1
Loop
End Sub

If anyone can offer any possible explanation or solution to this error I am getting, it would be greatly appreciated. I just can't understand why the code wouldn't work with another macro-enabled workbook open. I made sure that I was specifically referencing "thisworkbook...". Perhaps there is a setting which I have selected within excel that causes the macro-enabled workbooks to work against each other.

Anyway - Thank you in advance for any advice!

Upvotes: 0

Views: 454

Answers (1)

M. Wicha
M. Wicha

Reputation: 1

This part seems fishy to me:

With ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills")
        If WorksheetFunction.CountA(ThisWorkbook.Worksheets("BOM Query").Range("TABLE_Syteline_SingleLevel")) <> 0 Then
            For Each Row In ThisWorkbook.Worksheets("BOM Query").ListObjects("TABLE_Syteline_SingleLevel").DataBodyRange.Rows
                .ListRows.Add(AlwaysInsert:=True).Range = Row.Value  '******** The row behind the trouble *********
            Next Row
        End If
    End With

Reduntant With block, try to use explicit object address? Same line, you access a Range object and assign a Range.Value to it.

Try:

 ThisWorkbook.Worksheets("Job Data").ListObjects("TABLE_Syteline_JobBills") _
.ListRows.Add(AlwaysInsert:=True).Range.Value = Row.Value

Another idea is to try using Workbooks("wbName") instead of ThisWorkbook to make sure the correct Workbook is processed.

Update: Try commenting out Set Row = Nothing

Upvotes: 0

Related Questions