I have a series of Power Queries/VBA that work as follows ...
Query1 extracts all the data from a weekly source file (ThisWorkbook.Connections("Query - Query1").Refresh)
I then use VBA to perform various "transformations" including setting ColumnB.Value for every record to MMM or NNN
Query2 extracts data from the Query1 transformed table if ColumnB.Value = MMM (ThisWorkbook.Connections("Query - Query2").Refresh)
Query3 extracts data from the Query1 transformed table if ColumnB.Value = NNN (ThisWorkbook.Connections("Query - Query3").Refresh)
(basically splitting the Query1 transformed table into two separate tables with every record with ColumnB.Value = MMM in TableX and every record with ColumnB.Value = NNN in TableY)
I then apply some more VBA to delete records from TableY if TableY/ColumnA.Value also exists in TableX/ColumnA
So far so good ...
Query4 is supposed to append TableX and TableY INCLUDING the final step above (ThisWorkbook.Connections("Query - Query4").Refresh) - but it ignores the deletions!
I've found a workround by also applying the deletion to the appended table, but is there another way eg can I embed the deletion VBA in Query3 - the code I'm using is ...
Any/all help appreciated ...
Thanks ...
Query1 extracts all the data from a weekly source file (ThisWorkbook.Connections("Query - Query1").Refresh)
I then use VBA to perform various "transformations" including setting ColumnB.Value for every record to MMM or NNN
Query2 extracts data from the Query1 transformed table if ColumnB.Value = MMM (ThisWorkbook.Connections("Query - Query2").Refresh)
Query3 extracts data from the Query1 transformed table if ColumnB.Value = NNN (ThisWorkbook.Connections("Query - Query3").Refresh)
(basically splitting the Query1 transformed table into two separate tables with every record with ColumnB.Value = MMM in TableX and every record with ColumnB.Value = NNN in TableY)
I then apply some more VBA to delete records from TableY if TableY/ColumnA.Value also exists in TableX/ColumnA
So far so good ...
Query4 is supposed to append TableX and TableY INCLUDING the final step above (ThisWorkbook.Connections("Query - Query4").Refresh) - but it ignores the deletions!
I've found a workround by also applying the deletion to the appended table, but is there another way eg can I embed the deletion VBA in Query3 - the code I'm using is ...
VBA Code:
Dim DelTableY As Range
Set DelTableY = Sheets("TableX").Range("A2:A999")
With Sheets("TableY")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For n = LastRow To 2 Step -1
If Application.CountIf(DelTableX, .Cells(n, "A").Value) > 0 Then
.Cells(n, "A").EntireRow.Delete
End If
Next n
End With
Application.ScreenUpdating = TrueAny/all help appreciated ...
Thanks ...
