• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
smozgur

MoreQuery for Mac 2.1

No permission to download
Excel Version (Mac)
  1. 365

What is 'MoreQuery for Mac' add-in?​

Starting in early 2023, we finally have a more powerful Power Query on Mac. However, there are some missing Get Data options which are essential for almost everyday use. I personally don’t understand why the development team didn’t include these essential options initially, but I decided to complete these options by creating an add-in until they implement them.

The add-in has been developed and tested on MacOS Ventura. I am sharing it as an open-source project, so you can see how it is working, and even adapt it to your own use. Although it doesn't contain any harmful or hidden code fragments please examine the code as you wish and use the add-in at your own risk.

This add-in is built for Excel on Mac (365) and recommended for Mac only, but it is also functional if it is installed on Windows. In Excel on Windows, it simply ignores the Mac related routines and call the associated built-in commands on Windows.

MoreQuery for Mac add-in brings the following options in Power Query on Mac.
  • From Table/Range
  • From Folder (Optional only root or with subfolders)
  • From Web (JSON/XML)
  • Included in v2.0: From Existing Connection
1687117786163.png

MoreQuery add-in command button in the Data tab


These custom options help you to create queries without using M code manually as Bill Jelen and I previously explained in the Power Query Debuts for Excel for Mac but with Significant Gaps article. I recommend reading the article and also watching the linked video for behind-the-scenes information that encouraged me to build this add-in.

Due to some bugs and the nature of the Power Query connection that was not actually designed to use these options out of the box on Mac (yet), there are some basic rules to follow in order to use the add-in successfully.
  1. Do not change the auto-generated query name in the Power Query editor initially. First, transform data as you wish and Close & Load, then you can change the query name the next time you edit the query in the Power Query interface. Otherwise, the add-in won’t be able to find the auto-generated query during the initial load.
  2. Bill found a bug that the Folder.Files M function on Power Query Mac doesn’t work properly with the subfolders. The function only works successfully with the files in the selected folder. If there are nested folders then the function doesn’t return their path correctly which causes an error during the combining files step. You should either work with a folder without nested folders or filter files in nested folders before combining them.
    This problem has been solved in v2.1 by using Folder.Contents function with a custom recursive function.
  3. Even though it is possible to detect the loaded data structure (JSON or XML) in M, it was not the best way to create a complicated code to import web data. Instead, I decided to separate these source types, so all you need is to select the necessary type and provide a URL that returns data in the expected format.

Installation of the add-in on Mac​

  • Download the add-in.
  • Extract the morequery.xlam file from the download and save it somewhere you will remember.
  • Launch Excel, click the Tools menu item and Excel Add-ins button.
  • Click the Browse button to locate the add-in, and then click OK.
Note
You can also use an add-in by opening it in Excel (simply double-click on the file). You can then use it during that Excel session without installing it as an add-in.

Alternative Installation: Excel Startup folder: XLSTART

You can use the startup folder to load Excel add-ins.
  • Launch Excel, go to VBA (fn + option + F11), and open Debug / Immediate window (control + command + G). Copy and paste the following in the debug window and press Enter.

    print Application.StartupPath

    This will print the XLSTART folder path. The following is the folder path for Excel 365 on Mac:
    ~/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel

    Copy this path to be used in the next step and quit Excel.

  • Go to the XLSTART folder and copy and paste the add-in file (morequery.xlam) here.

Using the add-in​

Watch Video
You can watch a quick demo of the add-in here on YouTube.

The From Table/Range option works exactly the same as it works on Windows. The From Web options also work similarly to Windows. But the From Folder option is different due to some restrictions on Mac. Additionally, the add-in provides two options that allows user to import only the files in the root folder or including the subfolders.

In Windows, we can use the Application.FileDialog(msoFileDialogFolderPicker) VBA command to choose the folder. Unfortunately, this command is not available on Mac.

The Mac OS offers an AppleScript solution that we could call from VBA. The following is a sample code to choose a folder by using AppleScript in VBA.

VBA Code:
Dim strFolder As String
    On Error Resume Next
    strFolder = MacScript("POSIX path of (choose folder with prompt ""Select the folder"" default location alias (path to Desktop folder))")
    If strFolder = "" Then Exit Sub 'Cancel clicked
    On Error GoTo 0
    MsgBox "Selected folder:" & strFolder

This would allow the add-in to specify the folder, but it fails to call the code to grant access to the folder on Mac. The first time that you use “From Folder” using the AppleScript code above, everything would be fine. However, after you close the file, open the file, and refresh the query, the workbook will not have allowed access to the folder and the refresh will fail.

For those of you who want the technical reason why it works the first time
When you click on the Enable Macros button during the add-in load, the add-in is marked as a trusted source. When it uses the MacScript command to run the AppleScript code. in this scenario, the add-in is already able to access any folder without explicit permission. And surprisingly, the queries created by the add-in will also access any folders without any explicit permission. Therefore, the grant file access dialog window never pops up. However, this happens only during the initial creation of the query by the add-in. When you save the actual workbook and quit Excel, the next time you open the same workbook, it won't be able to access the selected folder. Therefore, I decided to use a simple input box to get the folder path.


select-folder.png

Copy and paste the folder path containing the files


If this folder is not already allowed, then the add-in will run a simple Dir(path) command in the background and you will see the Grant File Access dialog.

grant-file-access.png

Grant File Access dialog


Confirm the access, and Power Query will load the folder content successfully.

power-query-interface.png

Select folder content


After transforming data as you wish, click Close & Load, and you'll see the following Import Data options dialog. This is not the built-in dialog that is used on Windows since it doesn't exist on Mac, but a custom userform that has the same options to locate the final result as a Table, Pivot Table, or a Pivot Chart. You can also define the range that you'd like to place the object that you want to create, or even create the connection only without loading the data to be used in other queries.

import-data-dialog.png

Import Data options

Building the add-in​

If you are curious and have some time and familiar with the Excel object model, then explore the structure of a Power Query query object in a workbook by using VBA. You’ll notice a WorkbookQuery and also WorkbookConnection objects are involved. Therefore, what the add-in basically needs to do:
  1. Create a query with a formula that contains the M code as it will be generated in Power Query.
  2. Launch the Power Query interface to transform data after initial setup.
  3. Finally, create a new table that is connected to this query if the query still exists when the Power Query window is closed.
If we want to create a query that loads the data of a table named Table1, then we can use the following M code in Power Query.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]
 in
    Source

This is going to be the formula of the query that we will be creating, and the necessary VBA sub routine is simply the following:

VBA Code:
Sub createQueryInPowerQuery()
Dim wrk As Workbook
Dim qry As WorkbookQuery
    Set wrk = ActiveWorkbook
    Set qry = wrk.Queries.Add("Table1", "let " & Chr(10) & _
                 Chr(9) & "Source = Excel.CurrentWorkbook(){[Name = ""Table1""]}[Content] " & Chr(10) & _
                "in" & Chr(10) & _
                Chr(9) & "Source")
End Sub

We have the workbook query object now. It is time to launch Power Query.

Since we don’t have internal programmatic access to the Power Query interface in VBA, the easiest way to interact with the Power Query module is apparently using Excel itself. We can do this by using the Application.CommandBars.ExecuteMso command with the Power Query command button id. We can use the following command to launch Power Query:

VBA Code:
Application.CommandBars.ExecuteMso "PowerQueryGetTransformDataLaunchQueryEditor"

The Power Query interface is opened and the new query will be automatically added to the queries list in the Power Query interface. We can transform data however we like and close the Power Query window that will resume the VBA execution. At that point, the add-in needs to create a connection in the workbook and load the transformed table into a real table in a new worksheet.

Note
In the first version of the add-in, I used the list object's CommandText property to create the connection between the query and the worksheet table. In MoreQuery v2.0, I create a connection-only query and then connect it to the list object, which is a more elegant way and also explains how I create a connection-only query on Mac.

The following sub routine creates a table with the name of the query that was created in the previous step and connects it to the query by using the a connection object as the value of the Connection property of the list object . In the add-in, we call this routine only while the query still exists when the Power Query window is closed – basically not deleted by the user.
VBA Code:
Sub createConnectionAndListObject()
Dim wrk As Workbook
Dim sht As Worksheet
Dim rng As Range
Dim conn As WorkbookConnection
Dim lstObj As ListObject

    Set wrk = ActiveWorkbook
    Set sht = wrk.Worksheets.Add(after:=wrk.Worksheets(wrk.Worksheets.Count))
    Set rng = sht.Range("A1")

    Set conn = wrk.Connections.Add2("Query - Table1", _
        "Connection to the 'Table1' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table1"";Extended Properties=""""" _
        , "SELECT * FROM [Table1]", xlCmdSql)

    Set lstObj = sht.ListObjects.Add(SourceType:=xlSrcQuery, Source:=conn, Destination:=rng)
    With lstObj
        .Name = "Table_Table1"
        With .QueryTable
            .RefreshStyle = xlInsertDeleteCells
            .Refresh
        End With
    End With
End Sub

That’s it! The add-in created the workbook query object, launched Power Query to let user to transform data, created a new workbook connection, and finally loaded it in a new table (list object) connected to the query by using the connection!

Ribbon customization​

For readers who are not exactly familiar with creating a custom ribbon button and place into an existing ribbon tab right next to the built-in buttons, I decided to include this section. Therefore, this article will be more complete to guide readers who would like to build this or any add-in in Office 365.

An Excel file is actually a container consisting of multiple files in a certain structure. If you rename the add-in file by adding the zip extension, then you can simply extract this container into a separate folder as shown below.
Note
Although it is possible to do this on Mac since it requires some command line operations, I preferred to do that on Windows. On Mac, you need to use the “unzip” command to extract the zip file content instead of simply double click on the file as you would do for any other zip file. Then the rest of the procedure is exactly the same.

In fact, I also prefer using an extraction utility like 7zip that allows me to work without extracting the whole content but work in the utility container directly, but the following procedure doesn’t require any third-party tool. Therefore, I decided to explain this way.

Right-click on the renamed file and click Open, or simply double-click on the zip file.
open-zip-file.jpg

Open the Excel add-in file container


Windows will let you explore the file as a folder container and you will see the file content as shown below.
xlam-zip-container.png

The Excel add-in file container structure


The file that we are interested in is /customUI/customUI14.xml. You need to create this file if it doesn't exist.
customUI14.xml.png

Ribbon customization file /customUI/customUI14.xml


Copy this file out of the container folder since it will be read-only while it is in the zip container, and edit it in an XML editor like Notepad++ or Visual Studio Code. If there is no existing customization in the add-in file yet, create a new customUI14.xml.

The following file is the existing ribbon customization file in the MoreQuery add-in.
XML:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabData"> 
                <group id="BCGroupExtra" label="More Query" insertBeforeMso="GroupConnections">
                    <menu id="menu"
                        imageMso="DataFormAddRecord"
                        label="Get More Data"
                        size="large"
                        itemSize="large"
                        getEnabled="btnGetMoreData_OnGetEnabled" >
                        <button id="btnFromRange"
                            imageMso="QueryShowTable"
                            label="From Table/Range"
                            screentip="From Table/Range"
                            supertip="Create a new query from the selected table, named range, or array in this workbook.&#13;&#13;If the selected data isn't part of one of these already, it'll be converted into a table."
                            showLabel="true"
                            showImage="true"
                            onAction="btnFromRange_OnAction" />
                        <menu id="mnuFromFolder"
                            imageMso="CopyToFolder"
                            label="From Folder"
                            screentip="From Folder"
                            supertip="Import metadata and links about files in a folder"
                            itemSize="large"
                            showLabel="true"
                            showImage="true">
                            <button id="btnFromFolder"
                                imageMso="Folder"
                                label="From Folder"
                                screentip="From Folder"
                                supertip="Import metadata and links about files in a folder"
                                showLabel="true"
                                showImage="true"
                                onAction="btnFromFolder_OnAction" />  
                            <button id="btnFromFolderAll"
                                imageMso="CopyFolder"
                                label="From Folders and Subfolders"
                                screentip="From Folders and Subfolders"
                                supertip="Import metadata and links about files in a folder and subfolders"
                                showLabel="true"
                                showImage="true"
                                onAction="btnFromFolder_OnAction" />  
                        </menu>
                        <menu id="mnuFromWeb"
                            imageMso="GetExternalDataFromWeb"
                            label="From Web Service"
                            screentip="From Web Service"
                            supertip="Import data from a web service"
                            itemSize="large"
                            showLabel="true"
                            showImage="true">
                            <button id="btnFromWebJson"
                                imageMso="FieldCodes"
                                label="JSON"
                                screentip="From JSON Web Service"
                                supertip="Import data from a web service that returns JSON data"
                                showLabel="true"
                                showImage="true"
                                onAction="btnFromWebJson_OnAction" /> 
                            <button id="btnFromWebXml"
                                imageMso="XmlSource"
                                label="XML"
                                screentip="From XML Web Service"
                                supertip="Import data from a web service that returns XML data"
                                showLabel="true"
                                showImage="true"
                                onAction="btnFromWebXml_OnAction" />  
                        </menu>
                        <menuSeparator id="separator" />
                        <button id="btnFromExistingConnection"
                            imageMso="GetExternalDataExistingConnections"
                            label="From Existing Connection"
                            screentip="Get Data Using an Existing Connection"
                            supertip="Import data from common sources."
                            showLabel="true"
                            showImage="true"
                            onAction="btnFromExistingConnection_OnAction" />
                    </menu>
                </group>
            </tab>    
        </tabs>
    </ribbon>
</customUI>

In order to customize an existing tab, we use the tab element with the idMso attribute. The idMso attribute value should be the built-in tab id of the Data tab, which is TabData.

XML:
<ribbon>
    <tabs>
        <tab idMso="TabData">
        …
        </tab>
    </tabs>
</ribbon>

Although we can’t create new button group items, we can still create our custom groups in a built-in tab.

XML:
<ribbon>
    <tabs>
        <tab idMso="TabData">
            <group id="BCGroupExtra"
                    label="More Query"
                    insertBeforeMso="GroupConnections">
            …
            </group>
        </tab>
    </tabs>
</ribbon>

Note how we used the insertBeforeMso="GroupConnections" attribute to define where we exactly want to place the custom group, right before the built-in Connections group in the Data tab. It is also notable that we used the id attribute this time, not idMso since we are creating a custom item.

We want to create a drop-down button menu, therefore, we need to create a menu item as shown below.

XML:
<ribbon>
    <tabs>
        <tab idMso="TabData">
            <group id="BCGroupExtra"
                    label="More Query"
                    insertBeforeMso="GroupConnections">
                <menu id="menu"
                        imageMso="DataFormAddRecord"
                        label="Get More Data"
                        size="large"
                        itemSize="large"
                        getEnabled="btnGetMoreData_OnGetEnabled" >
                ...
                </menu>
            </group>
        </tab>
    </tabs>
</ribbon>

While the label, size, and itemSize attributes are self-descriptive, imageMso defines a built-in icon name to be used as the menu icon. Finally, the getEnabled attribute is a function name that we will use to let ribbon knows when this item should be enabled, for example, only when a workbook is active in Excel since we don’t want it working without an open workbook.

And finally, the buttons. I will demonstrate only one button below since all of them are basically created the same way.

XML:
<ribbon>
    <tabs>
        <tab idMso="TabData">
            <group id="BCGroupExtra" label="More Query" insertBeforeMso="GroupConnections">
                <menu id="menu"
                        imageMso="DataFormAddRecord"
                        label="Get More Data"
                        size="large"
                        itemSize="large"
                        getEnabled="btnGetMoreData_OnGetEnabled" >
                    <button id="btnFromRange"
                            imageMso="QueryShowTable"
                            label="From Table/Range"
                            screentip="From Table/Range"
                            supertip="Create a new query from the selected table, named range, or array in this workbook.&#13;&#13;If the selected data isn't part of one of these already, it'll be converted into a table."
                            showLabel="true"
                            showImage="true"
                            onAction="btnFromRange_OnAction" />
                            ...
                </menu>
            </group>
        </tab>
    </tabs>
</ribbon>

The button element’s attributes are also self-descriptive, except the onAction="btnFromRange_OnAction" attribute that defines the procedure name to be called when this button is clicked.

Save the XML file and drag it back to the zip container folder and close the folder. Finally, change the file name back by removing the zip extension, and it will be ready to be used. Double-click on the file to open it as a temporary add-in in Excel. You will see the custom ribbon button menu is placed exactly where we would like it to be.

The getEnabled and onAction attribute values are the associations with the corresponding VBA procedures. In the bcRibbon standard module below, you can see how those procedures are created with the matching names with the attribute values in the XML file above.

VBA Code:
Public Sub btnGetMoreData_OnGetEnabled(ByRef control As Office.IRibbonControl, ByRef ReturnValue As Variant)
   ReturnValue = Not ActiveWorkbook Is Nothing
End Sub

Public Sub btnFromRange_OnAction(ByRef control As Office.IRibbonControl)
    bcMain.getFromTableOrRange
End Sub

And finally, the entire add-in code completes some missing but essential Power Query functions in Mac Excel.

bcMain Standard Module: Power Query related procedures
VBA Code:
Option Explicit

' MoreQuery add-in for Mac v2.1
' © 2023, Suat M. Ozgur
' suat@mrexcel.com

Sub getFromTableOrRange()
#If Mac Then
Dim wrk As Workbook
Dim rng As Range
Dim strQueryName As String

    Set wrk = ActiveWorkbook
    Set rng = Selection.CurrentRegion
    If rng.ListObject Is Nothing Then
        Application.CommandBars.ExecuteMso "TableInsertExcel"
    End If
    If rng.ListObject Is Nothing Then
        Exit Sub
    End If
  
    strQueryName = getNextQueryName(rng.ListObject.Name)
  
    wrk.Queries.Add strQueryName, "let " & _
                                    vbLf & vbTab & "Source = Excel.CurrentWorkbook(){[Name = """ & rng.ListObject.Name & """]}[Content] " & _
                                    vbLf & " in" & _
                                    vbLf & vbTab & "Source"
  
    Application.CommandBars.ExecuteMso "PowerQueryGetTransformDataLaunchQueryEditor"
  
    createOutput wrk, strQueryName
  
#Else
    Application.CommandBars.ExecuteMso "GetPowerQueryExcelDataFromTable"
#End If
End Sub

Sub getFromFolder(Optional subFolders As Boolean)
#If Mac Then
Dim strFolder As String
Dim wrk As Workbook
Dim strQueryName As String
Dim arrPath As Variant
    strFolder = Trim(InputBox("Copy & paste the folder path", "Folder Path"))
    If strFolder = "" Then Exit Sub
    If Right(strFolder, 1) = Application.PathSeparator Then
        strFolder = Left(strFolder, Len(strFolder) - 1)
    End If
    arrPath = Split(strFolder, Application.PathSeparator)
    strQueryName = getNextQueryName(arrPath(UBound(arrPath)))
  
    Dir strFolder
  
    Set wrk = ActiveWorkbook
 
    If subFolders Then
        wrk.Queries.Add strQueryName, "let " & _
                                vbLf & vbTab & "fnRecursiveFolders = (Source as table) as table =>" & _
                                vbLf & String(2, vbTab) & "let" & _
                                vbLf & String(3, vbTab) & "FilesOnly = Table.SelectRows(Source, each Value.Is([Content], type binary))," & _
                                vbLf & String(3, vbTab) & "SubFolders = Table.SelectRows(Source, each Value.Is([Content], type table))," & _
                                vbLf & String(3, vbTab) & "SubItems = SubFolders[Name]," & _
                                vbLf & String(3, vbTab) & "Combine = List.Accumulate(" & _
                                vbLf & String(3, vbTab) & "SubItems," & _
                                vbLf & String(3, vbTab) & "[t = FilesOnly]," & _
                                vbLf & String(3, vbTab) & "(state, current) => [" & _
                                vbLf & String(3, vbTab) & vbTab & "t = Table.Combine({state[t], SubFolders{[Name = current]}[Content]})" & _
                                vbLf & String(3, vbTab) & vbTab & "]" & _
                                vbLf & String(3, vbTab) & ")[t]," & _
                                vbLf & String(3, vbTab) & "Result = if Table.RowCount(Table.SelectRows(Combine, each Value.Is([Content], type table))) > 0 then @fnRecursiveFolders(Combine) else Combine" & _
                                vbLf & String(2, vbTab) & "in" & _
                                vbLf & String(3, vbTab) & "Result," & _
                                vbLf & vbTab & "Source = Folder.Contents(""" & strFolder & """)," & _
                                vbLf & vbTab & "Result = fnRecursiveFolders(Source)" & _
                                vbLf & "in" & _
                                vbLf & vbTab & "Result"
    Else
        wrk.Queries.Add strQueryName, "let " & _
                                vbLf & vbTab & "fnSelectRows = (Source as table) as table  =>" & _
                                vbLf & String(2, vbTab) & "let" & _
                                vbLf & String(2, vbTab) & "AddTempColumn = Table.AddColumn(Table.SelectRows(Source, each Value.Is([Content], type binary)),""@temp@"", each true)," & _
                                vbLf & String(2, vbTab) & "Result = Table.RemoveColumns(AddTempColumn, ""@temp@"")" & _
                                vbLf & String(2, vbTab) & "in" & _
                                vbLf & String(3, vbTab) & "Result," & _
                                vbLf & vbTab & "Source = Folder.Contents(""" & strFolder & """)," & _
                                vbLf & vbTab & "Result = fnSelectRows(Source)" & _
                                vbLf & "in" & _
                                vbLf & vbTab & "Result"
    End If
  
    Application.CommandBars.ExecuteMso "PowerQueryGetTransformDataLaunchQueryEditor"
  
    createOutput wrk, strQueryName
  
#Else
    Application.CommandBars.ExecuteMso "GetPowerQueryDataFromFolder"
#End If
End Sub

Sub getFromWeb(Optional sourceType As String)
#If Mac Then
Dim strURL As String
Dim strQueryName As String
Dim arrPath As Variant
Dim wrk As Workbook
    strURL = Trim(InputBox("Copy & paste the URL", "Web Service URL"))
    If strURL = "" Then Exit Sub
    If Right(strURL, 1) = "/" Then
        strURL = Left(strURL, Len(strURL) - 1)
    End If
    arrPath = Split(strURL, "/")
    strQueryName = getNextQueryName(arrPath(UBound(arrPath)))
  
    Set wrk = ActiveWorkbook
 
    wrk.Queries.Add strQueryName, "let " & _
                                    vbLf & vbTab & "Source = " & sourceType & ".Document(Web.Contents(""" & strURL & """)) " & _
                                    vbLf & " in" & _
                                    vbLf & vbTab & "Source"
  
    Application.CommandBars.ExecuteMso "PowerQueryGetTransformDataLaunchQueryEditor"
  
    createOutput wrk, strQueryName
 
#Else
    Application.CommandBars.ExecuteMso "GetPowerQueryDataFromWeb"
#End If
End Sub

Sub getFromExisting(strConnName As String)
#If Mac Then
Dim wrk As Workbook
Dim conn As WorkbookConnection
Dim strOrigQueryName As String
Dim strQueryName As String
Dim i As Integer
Dim arrCommandText As Variant
Dim arrPairs As Variant

    Unload frmExistingConnections

    Set wrk = ActiveWorkbook
    Set conn = wrk.Connections(strConnName)
    arrCommandText = Split(conn.OLEDBConnection.Connection, ";")
  
    For i = 0 To UBound(arrCommandText)
        arrPairs = Split(arrCommandText(i), "=")
        If arrPairs(0) = "Location" And UBound(arrPairs) = 1 Then
            strOrigQueryName = Replace(arrPairs(1), """", "")
        End If
    Next i
  
    If strOrigQueryName = "" Then
        MsgBox "Connection not selected.", vbOKOnly + vbExclamation, "Error"
        Exit Sub
    End If
  
    strQueryName = getNextQueryName(strOrigQueryName)
  
    createOutput wrk, strQueryName, strOrigQueryName
#Else
    Application.CommandBars.ExecuteMso "GetTransformExistingConnections"
#End If
End Sub

Private Function getNextQueryName(strQuery) As String
Dim i As Long
Dim strTmp As String

    strQuery = Replace(Trim(strQuery), ".", "_")
    strTmp = strQuery
    i = 1
    Do While isQueryExisting(strTmp)
        i = i + 1
        strTmp = strQuery & " (" & i & ")"
    Loop

    getNextQueryName = strTmp

End Function

Private Function isQueryExisting(strQuery As String) As Boolean
Dim qry As WorkbookQuery
    For Each qry In ActiveWorkbook.Queries
        If qry.Name = strQuery Then
            isQueryExisting = True
            Exit For
        End If
    Next qry
End Function

Private Sub createOutput(wrk As Workbook, strQueryName As String, Optional strOrigQueryName As String)
Dim sht As Worksheet
Dim lstObj As ListObject
Dim strTableName As String
Dim conn As WorkbookConnection
Dim rng As Range
Dim pt As PivotTable
  
Retry:
    If strOrigQueryName <> "" Then
        frmImportData.Show
        wrk.Queries.Add strQueryName, wrk.Queries(strOrigQueryName).Formula
    End If
  
    If Not isQueryExisting(strQueryName) Then
        Exit Sub
    End If
  
    Set conn = wrk.Connections.Add2("Query - " & strQueryName, _
        "Connection to the '" & strQueryName & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & strQueryName & """;Extended Properties=""""" _
        , "SELECT * FROM [" & strQueryName & "]", xlCmdSql)
  
    With frmImportData
        If strOrigQueryName = "" Then
            .Show vbModal
        End If
        If Not .optConnectionOnly Then
            If .optExistingWorksheet Then
                On Error GoTo NewWorksheet
                Set rng = Range(.rfRange.Value)
                Set sht = rng.Parent
            Else
NewWorksheet:
                On Error GoTo 0
                Set sht = wrk.Worksheets.Add(after:=wrk.Worksheets(wrk.Worksheets.Count))
                Set rng = sht.Cells(1, 1)
            End If
            If rng.CurrentRegion.ListObject Is Nothing And rng.CurrentRegion.Cells.Count = 1 And IsEmpty(rng.Value) Then
            Else
                MsgBox "A table can't overlap another table or range with data.", vbOKOnly + vbExclamation, "Error"
                If strOrigQueryName <> "" Then
                    wrk.Queries(strQueryName).Delete
                End If
                conn.Delete
                GoTo Retry
            End If
        End If
    End With
  
    If frmImportData.optTable Then
        Set lstObj = sht.ListObjects.Add(sourceType:=xlSrcQuery, Source:= _
            conn _
            , Destination:=rng)
        With lstObj
            strTableName = "Table_" & Replace(Replace(Replace(Replace(Replace(strQueryName, " ", "_"), "=", "_"), "%", "_"), "(", "_"), ")", "")
            .Name = strTableName
            With .QueryTable
                .RefreshStyle = xlInsertDeleteCells
                On Error Resume Next
                .Refresh
                If Err Then MsgBox Err.Description, vbOKOnly + vbExclamation, "Error"
                On Error GoTo 0
            End With
        End With
    ElseIf frmImportData.optPivotTable Or frmImportData.optPivotChart Then
        Set pt = wrk.PivotCaches.Create(sourceType:=xlExternal, SourceData:=conn, Version:=8).CreatePivotTable(TableDestination:=rng)
        If frmImportData.optPivotChart Then
            sht.Shapes.AddChart2(201, xlColumnClustered).Chart.SetSourceData Source:=pt.TableRange1
        End If
        rng.Cells(1, 1).Select
    ElseIf strOrigQueryName <> "" Then
        Application.CommandBars.ExecuteMso "PowerQueryGetTransformDataLaunchQueryEditor"
    End If
    Unload frmImportData
End Sub

bcRibbon Standard Module: Ribbon actions
VBA Code:
Option Explicit
Option Private Module

Public Sub btnFromExistingConnection_OnAction(ByRef control As Office.IRibbonControl)
    frmExistingConnections.Show
End Sub

Public Sub btnFromRange_OnAction(ByRef control As Office.IRibbonControl)
    bcMain.getFromTableOrRange
End Sub

Public Sub btnFromFolder_OnAction(ByRef control As Office.IRibbonControl)
    bcMain.getFromFolder control.ID = "btnFromFolderAll"
End Sub

Public Sub btnFromWebJson_OnAction(ByRef control As Office.IRibbonControl)
    bcMain.getFromWeb "Json"
End Sub

Public Sub btnFromWebXml_OnAction(ByRef control As Office.IRibbonControl)
    bcMain.getFromWeb "Xml"
End Sub

Public Sub btnGetMoreData_OnGetEnabled(ByRef control As Office.IRibbonControl, ByRef ReturnValue As Variant)
   ReturnValue = Not ActiveWorkbook Is Nothing
End Sub

bcResizeOnMac Standard Module: To resize user forms properly on Mac
Note that I am using the Zoom property instead resizing all controls on a form.
VBA Code:
Sub scaleUserForm(frm As Object, Optional dblScale As Double = 3 / 2)
    With frm
        .Width = .Width * dblScale
        .Height = .Height * dblScale
        .Zoom = 100 * dblScale
    End With
End Sub

Note
Although existing userform objects cannot be edited on a Mac, their code is still available as class modules. The add-in should be opened in a Windows machine to edit userforms.


formImportData Userform module - Options to import data (A table, pivot table, pivot chart, or connection-only)
VBA Code:
Option Explicit

Private Sub UserForm_Initialize()
Dim conn As WorkbookConnection
    #If Mac Then
        scaleUserForm Me
    #End If
    If ActiveWorkbook.Connections.Count = 0 Then
        With Me.lstConnections
            .AddItem "<No connections found>"
            .Enabled = False
        End With
    Else
        For Each conn In ActiveWorkbook.Connections
            Me.lstConnections.AddItem conn.Name
        Next conn
    End If
End Sub

Private Sub lstConnections_Click()
    Me.cmdOpen.Enabled = Me.lstConnections.ListIndex <> -1
End Sub

Private Sub lstConnections_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.lstConnections.ListIndex <> -1 Then
        Call cmdOpen_Click
    End If
End Sub

Private Sub cmdOpen_Click()
    bcMain.getFromExisting Me.lstConnections.Value
End Sub

formExistingConnections Userform module - Displays existing connections
VBA Code:
Option Explicit

Private Sub cmdOpen_Click()
    bcMain.getFromExisting Me.lstConnections.Value
End Sub

Private Sub lstConnections_Click()
    Me.cmdOpen.Enabled = Me.lstConnections.ListIndex <> -1
End Sub

Private Sub lstConnections_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.lstConnections.ListIndex <> -1 Then
        Call cmdOpen_Click
    End If
End Sub

Private Sub UserForm_Initialize()
Dim conn As WorkbookConnection
    #If Mac Then
        scaleUserForm Me
    #End If
    If ActiveWorkbook.Connections.Count = 0 Then
        With Me.lstConnections
            .AddItem "<No connections found>"
            .Enabled = False
        End With
    Else
        For Each conn In ActiveWorkbook.Connections
            Me.lstConnections.AddItem conn.Name
        Next conn
    End If
End Sub

clsOptionButton Class module: I usually prefer using a class module to catch option button click events. This is a good way to avoid code repetition, and I believe it is a also good demonstration of how to create a simple class module for a userform control and catch its events.
VBA Code:
Option Explicit

Public WithEvents optionButton As MSForms.optionButton

Private Sub optionButton_Click()
    If Me.optionButton.GroupName = "grpImport" Then
        With Me.optionButton.Parent
            .rfRange.Enabled = Me.optionButton.Name <> "optConnectionOnly" And .optExistingWorksheet.Value
            .optExistingWorksheet.Enabled = Me.optionButton.Name <> "optConnectionOnly"
            .optNewWorksheet.Enabled = Me.optionButton.Name <> "optConnectionOnly"
        End With
    Else
        With Me.optionButton.Parent
            .rfRange.Enabled = Me.optionButton.Name = "optExistingWorksheet"
            If .rfRange.Enabled Then
                .rfRange.Value = ActiveCell.Address(True, True)
            End If
        End With
    End If
    With Me.optionButton.Parent.rfRange
        If .Enabled Then
            .SelStart = 0
            .SelLength = Len(.Value)
            .SetFocus
        End If
    End With
End Sub
Add-in Short Name
MoreQuery
Author
smozgur
Downloads
399
Views
7,928
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from smozgur

Latest updates

  1. Bug fixes

    Bug fixes: Removed the MISSING reference. Fixed typos in the code. Thanks to @Jon Peltier and...
  2. v2.1 - Import files from subfolders

    New Feature: It is now possible to import files from the subfolders. The Folder.Files function...
  3. v2.0 - More "missing" features and user interface update

    Updates: The list object's connection property is now being created by using an actual workbook...

Latest reviews

This is most useful article to me as a Mac Excel user. Detailed explanation is invaluable!

Thank you!!
smozgur
smozgur
You're welcome :) Glad to hear it helps and thanks for the feedback!

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top