Queries not Detected in WorkbookQueries Collection

[Hashiru]

Hi all the below code could not detect queries on my workbook

Code from https://stackoverflow.com/questions...h-vba?newreg=fd32a0270b5149668f4768387e94fc23

Need this code to be able to copy queries and data from the master template to a new workbook

Public Sub FunctionToTest_ForStackOverflow()
                ' Doug.Long
                Dim wb As Workbook
                ' create empty workbook
                Set NewBook = Workbooks.Add
                Set wb = NewBook
                ' copy queries
                CopyPowerQueries ThisWorkbook, wb, True
            End Sub
            Public Sub CopyPowerQueries(wb1 As Workbook, wb2 As Workbook, Optional ByVal copySourceData As Boolean)
                ' Doug.Long
                ' copy power queries into new workbook
                Dim qry As WorkbookQuery
                For Each qry In wb1.Queries
                    ' copy source data
                    If copySourceData Then
                        CopySourceDataFromPowerQuery wb1, wb2, qry
                    End If
                    ' add query to workbook
                    wb2.Queries.Add qry.Name, qry.Formula, qry.Description
                Next
           End Sub
            Public Sub CopySourceDataFromPowerQuery(wb1 As Workbook, wb2 As Workbook, qry As WorkbookQuery)
                ' Doug.Long
                ' copy source data by pulling data out from workbook into other
                Dim qryStr As String
                Dim sourceStrCount As Integer
                Dim i As Integer
                Dim tbl As ListObject
                Dim sht As Worksheet
                sourceStrCount = (Len(qry.Formula) - Len(Replace$(qry.Formula, "Source = Excel.CurrentWorkbook()", ""))) / Len("Source = Excel.CurrentWorkbook()")
                For i = 1 To sourceStrCount
                    qryStr = Split(Split(qry.Formula, "Source = Excel.CurrentWorkbook(){[Name=""")(1), """]}")(0)
                    For Each sht In wb1.Worksheets
                        For Each tbl In sht.ListObjects
                            If tbl.Name = qryStr Then
                                If Not sheetExists(sht.Name) Then
                                    sht.Copy After:=wb2.Sheets(wb2.Sheets.Count)
                                End If
                            End If
                        Next tbl
                    Next sht
                Next i
                qryStr = qry.Formula
            End Sub
            Function sheetExists(sheetToFind As String) As Boolean
                'http://stackoverflow.com/questions/6040164/excel-vba-if-worksheetwsname-exists
                sheetExists = False
                For Each Sheet In Worksheets
                    If sheetToFind = Sheet.Name Then
                        sheetExists = True
                        Exit Function
                    End If
                Next Sheet
            End Function

 

[RoryA]

What kind of queries are they?

 

[Hashiru]

Thanks RoryA, they are Power Queries.

 

[RoryA]

Which version of Excel are you using?

 

[Hashiru]

Hi RoryA,

Home Excel 2016 64 bit on a 64 bit OS
Work Excel 2016 32 bit on a 64 bit OS

Thanks.