VBA to Load a Query to a new tab

[gheyman]

If I have a Query named PBoM1 is there a way to use Code to load it to a new tab? I only want the query loaded to a tab if the user needs it. I have 20 potential queries for the user. They may only want one or more and not all 20. So I dont want to create 20 tabs and load each of the 20 queries. Is this possible? Thanks

 

[gheyman]

I tried this, but I am getting a Runtime error 9 on line: If ws.ListObjects("PBoM1").Name = "PBoM1" Then

Sub Load_PBoM1()
'
' Load PBoM1
'
'***************************
'Check to see if the table already exists on any of the existing tabs.
Dim ws As Worksheet
Dim PBoM1 As String
Dim tbl As ListObject
 
For Each ws In ActiveWorkbook.Worksheets

If ws.ListObjects("PBoM1").Name = "PBoM1" Then
ws.ListObjects("PBoM1").QueryTable.Refresh BackgroundQuery:=False
Sheets("Task List").Range("H1").Value = 1
Exit For
End If
Next ws
'
'*******************************

'If the table does not currently exist on any of the tabs, then create a tab and load the table.
If Sheets("Task List").Range("H1").Value <> 1 Then
'On Error GoTo Skip
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=PBoM1;Extended Properties=""""" _
        , Destination:=Range("$B$15")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [PBoM1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "PBoM1"
        .Refresh BackgroundQuery:=False
    End With
'Skip:
'    On Error GoTo 0
Else
End If
   
   
End Sub

 

[smozgur]

The code needs to check each list object in each worksheet to verify if it exists or not.
I assumed the query name and list object name are the same and updated your code to make it work as I explained, also by using a variable to keep the query/list object name, so you can actually convert this sub to a function that takes the query name as the parameter and use it as a generic function for all queries that you would need to create query tables. In order to do that, you just need to change the Sub line with Function CreateListObject(strQuery) and remove the strQuery declaration and assignment lines from the existing code.

Sub Load_PBoM1()
Dim ws As Worksheet
Dim tbl As ListObject
Dim strQuery As String
 
strQuery = "PBoM1"
 
For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        If tbl.Name = strQuery Then Exit Sub
    Next tbl
Next ws
   
Application.CutCopyMode = False
Set ws = ActiveWorkbook.Worksheets.Add
With ws.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strQuery & ";Extended Properties=""""" _
        , Destination:=ws.Cells(1, 1)).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & strQuery &  "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = strQuery
        .Refresh BackgroundQuery:=False
End With
   
   
End Sub

 

[JGordon11]

Suat -

If you change the sub to a function named CreateListObject, wouldn't you need a final line where

CreateListObject = Something

I couldn't get it to work as a function and couldn't figure out what to set the function equal to

 

[gheyman]

Thanks, I though I was doing something wrong as well. I could not get it to work.

 

[RoryA]

You don't have to use the return value of a function (although if your function doesn't have a return value of any kind, there is little point to it being a function generally speaking). You could still call it with:

CreateListObject strQuery

whether it is a function or sub.

 

[smozgur]

Thanks, I though I was doing something wrong as well. I could not get it to work.

The code works either way. As it is or as function by calling it as shown below:

CreateListObject("PBoM1")

or as @RoryA explained (The parenthesis are not mandatory if you are not going to use/assign the function return value.):

CreateListObject "PBoM1"

Note that, if you have the list object exits already, then it will simply stop execution. Otherwise, if you receive an error, then please let us know.

About why I mentioned using Function instead of simply a Sub by adding a parameter - just because I got confused being in the PQ forum as I felt like need to talk about functions. It is perfectly fine (and better/easier) changing the sub as explained without changing Sub keyword to Function:

Sub CreateListObject(strQuery)
Dim ws As Worksheet
Dim tbl As ListObject
 
For Each ws In ActiveWorkbook.Worksheets
' the rest of the code...

 

[JGordon11]

I was trying to use the function in a worksheet - like typing =CreateListObject("Query1") in cell A1. I'm assuming that won't work and it has to be called from a VBA sub?

Is there a function that would load a query by typing =FunctionName("QueryName") in a cell in a workbook?

 

[smozgur]

I was trying to use the function in a worksheet - like typing =CreateListObject("Query1") in cell A1. I'm assuming that won't work and it has to be called from a VBA sub?

Sorry for the confusion, I was referring to a VBA method, not a User Define Function.

Is there a function that would load a query by typing =FunctionName("QueryName") in a cell in a workbook?

I don't think we can return a ListObject object as a user define function return type. Unfortunately VBA was never and will never be that good at being object-oriented.