Adding table in sheet to Power Query VBA

[de3pster]

Hi,

I would like to add my current table in the active worksheet to power query and create an open connection. Right now I have taken reference to a code from online but this code looks at all the worksheets in the workbook and adds all to the power query but I am just looking to only add the active sheet if thats possible. Also wondering if I can do a step further and add it to my appended query as well. I say active sheet because I am importing a new sheet every week so I want it to be automated. Any help would be appreciated. Thank you.

This is what I have now

Dim wss As Worksheet
Set wss = ActiveSheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean

    Set wb = ActiveWorkbook

         For Each ws In ActiveWorkbook.Worksheets
         For Each lo In ws.ListObjects
        
         sName = lo.Name
         sFormula = "Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]"
       

          'Add query
          wb.Queries.Add Name:=sName, _
                         Formula:="let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
          'Add connection
          wb.Connections.Add2 Name:="Query - " & sName, _
                              Description:="Connection to the '" & sName & "' query in the workbook.", _
                              ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=""""", _
                              CommandText:="SELECT * FROM [" & sName & "]", _
                              lCmdtype:=2, _
                              CreateModelConnection:=False, _
                              ImportRelationships:=False
                              
Next lo
Next ws

 

[RoryA]

When you say "current table" do you mean specifically the currently selected table, or is it the only table on the current sheet? Essentially, you'll need to remove the two For Each .. Next loops and just use something like:

Set lo = Activesheet.Listobjects(1)

 

[de3pster]

When you say "current table" do you mean specifically the currently selected table, or is it the only table on the current sheet? Essentially, you'll need to remove the two For Each .. Next loops and just use something like:

Set lo = Activesheet.Listobjects(1)

Hi, thank you so much for your response. Yes it is the only table in the sheet.

Your code works!! Thanks so much. Can I just ask is it possible to now insert that query into an appended query. For example I have an appended query named Staging. Is it possible to add this directly into that in VBA?