- Excel Version (Mac)
- 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.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
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.
- 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.
- Bill found a bug that the
Folder.FilesM 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.
- 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:" & strFolderThis 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.
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 dialog
Confirm the access, and Power Query will load the folder content successfully.
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 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:- Create a query with a formula that contains the M code as it will be generated in Power Query.
- Launch the Power Query interface to transform data after initial setup.
- Finally, create a new table that is connected to this query if the query still exists when the Power Query window is closed.
Table1, then we can use the following M code in Power Query.
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]
in
SourceThis 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 SubWe 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 SubThat’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.
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 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.
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.
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. 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. 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 SubAnd 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 SubbcRibbon 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 SubbcResizeOnMac 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 SubformExistingConnections 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 SubclsOptionButton 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
