Main Components of PowerOpI SQL Tooling Framework

First, you should have SQL Server, SQL Server Management Studio (SSMS), MSOLEDBSQL, and Microsoft.ACE.OLEDB.XX.0 (ACE) installed and configured as described on page Excel-SQL Server on this site. Links are provided there for components to install. If possible, also set up Visual Studio and SQL Server Development Tools (VS-SSDT), also linked on that page.

A single PowerOpI SQL Tooling Framework file contains several functionalities:

• Source Data Tables. These are Structured Tables, or Named Ranges, or Worksheets, in Excel containing primary data defining the Project/Program/Product/Process represented. Data may also be imported from MS Project, using an exported Excel .xlsx file, for dates, or from SQL Server, or from a Text file or an MS Access database. Tables can be imported from another source by using database QUERY in order to add functionality to that table either by the QUERY or locally in Excel, and thence cycled again through subsequent Report QUERYs. Use of Excel Structured Tables is preferred because of characteristics of such tables, but they must be provided with a named Shadow Range. It may be necessary to use a full worksheet as a Source Data Table, if that is the only choice for export format (as, for example, from MS Project).
  
• Reports and Tables. Reports are typically Pivot Tables or Pivot Charts, driven from SQL QUERYs contained in the workbook containing the Reports. Pivot Table shown at left, and an imported Table is shown at right. An imported Table is represented in Excel as a Structured Table and may itself be provided with a named Shadow Range so that it can be used as Source Data for a subsequent QUERY.
  
• Connection Management, consisting of
  • Data Connection Configuration table: Connection definition, with Build/Update/Refresh automation.
    
  • Common Configuration Variables. These can be used in Excel to build location-sensitive values to build QUERYs to be used among multiple distributed users of Report workbooks.
    
  • Editor for Configured SQL. Build SQL statements using configuration variables for location, named Linked Servers, file paths for OPENROWSET or named Linked Servers. SQL lines in the figure below in blue typeface are Excel expressions incorporating Common Configuration Variables to provide filenames and paths, Linked Server name, names of related SQL Stored Procedures etc. Using these common variables is helpful to maintain functional integrity among related SQL QUERYs.
    
  • QUERY Export. Exports Configured SQL QUERYs to a designated directory, for use with SQL Server Management Studio (SSMS) and Visual Studio (VS-SSDT). Create configured SQL for CREATE/DROP of Databases, Tables, Procedures, Permissions etc. CREATE/DROP no longer be initiated from Excel, and must be done from SSMS or VS-SSDT. Configured definition of these exported QUERYs creates entities to be used with Excel-initiated configured QUERYs in Report workbooks using the same Common Configuration Variables. The string #VBTEXTJOIN: EXPORT; within a SQL comment indicates which SQL procedures to export, using button Execute tblSQL #Directives (shown in above figure).
    

All components described here are contained within a single Excel Framework File. As noted above, functionality generally consists of Source Data, Reporting, and SQL Management; and these in practice are commonly split from the Framework into separate workbook files for the three separate purposes listed above.

• Source Data Workbook(s): Source Data may be contained in one or more workbooks visible to SQL Server or MS Access, each containing data tables defining process and product structure (e.g., Allocation, Dates, Builds, Element Data, Supply Chain), maintaned by owning organizations. Each Source Data Workbook may contain connections to import data into tables (e.g., Date import from MS Project). Each Source Data workbook would contain a Data Connection Configuration tab if it needs to manage any Connections and QUERYs contained in that workbook.
• For use with SQL Server, Reports (Pivot tables) driven by SQL Server Connections cannot be refreshed from OPEN Source Data files, so Source Data must be in files SEPARATE from any files containing Reports driven from that Source Data. The Source Data files must be CLOSED at the time of Report refresh.
• A Source Data workbook may simply be a workbook containing data in Structured Tables, or it may itself be a copy of the Framework workbook if the Source Data requires Connection functionality - for example, pulling Date data into a Table in the workbook, to which computation might be added (such as key creation for subsequent JOINs) or that may provide data fields used in computation in other Tables in the Source Data workbook. Just make sure, at each level in such a hierarchy of workbooks, that Source Data files are closed for any workbook connections to be Refreshed.
• For use with MS Access, Reports and Source Data may reside within a common file.
• Report Workbook(s): Files provided as Report files to distributed users typically contain Report Pivot Tables etc. driven from contained Connections that use EXEC QUERYs calling Stored Procedures in the Database system, thereby protecting construction of the QUERYs and the file structure and database structure underlying them, and also centralizing and simplifying maintenance of QUERY construction. Some Report Workbook QUERYs could nevertheless contain embedded SQL. Report Workbooks contain Reports/Pivot tables prototyped in the Management Workbook, and may also contain locally-created Reports based on Stored Predures available to Users. Each Report workbook would contain a Data Connection Configuration tab, managing its Connections and QUERYs contained in that workbook.
• SQL Management Workbook: SQL Management capapability from the PowerOpI Framework is typically held in a workbook specific to management, and containing prototypes of Reports to be distributed (Pivot Tables, Pivot Charts, Tables), and SQL development for the configured SQL Stored Procedures to drive those Reports. The workbook contains QUERY definitions for CREATE/DROP to be exported to perform those actions in SSMS or VS-SSDT to create the Stored Procedures, Tables and Databases. The SQL Management workbook would contain a Data Connection Configuration tab, managing Connections and QUERYs contained in that workbook, and these connection configurations provide prototype Connection configuration to be replicated within distributed Report workbooks.

The PowerOpI Framework is not distributed here, but the mission of this website is to show how to use a Database Tool with Excel to address hard configuration problems. Because of changes made to Excel this is no longer a Low-Code proposition... So, critical sections of the VBA code required are shown in the following links, to be described below.

• vbaConnect.txt contains code for a common module to manage connections. (Requires distribution from Quantitative Management Consulting).
• vbaDataSources.txt contains code for the Data Sources tab, and for each tab containing a Source Data table. (Requires distribution from Quantitative Management Consulting).

Notice the command buttons at lower left of the Data Connection Configuration Table:

The top four buttons initiate Build or Update of connections to Source Data. Build means "create if no connection by that name exists", and set all parameters as described by the Table. Update also means "create if no connection exists by that name", but "Update" the existing database Connection String if one existed, and for new connections set the Connection String to the Template from the Table. All other parameters are set as described in the Table.

DATE tables get their own Build/Update buttons. They function the same as for DATA tables but are generally updated separately so that you can work on dates without affecting logistics until you're ready to; and then you should update DATES before updating DATA, so that the DATA will pick up the newest set of dates. DATA tables are defined by LinkType "PivotSource" (for Data Connection) or "webQuery" (for PowerQUERY). DATE tables are defined by LinkType "mppImport" (for Data Connection) or "mppQuery" (for PowerQUERY).

The same sequence applies to Build and Update, for DATA and for DATES; and also for both Connection and PowerQUERY QUERY definition records. The exact coding for QUERYs is a little different than for Connections but the overall sequence is the same:

• tblConnectionConfig is scanned line by line and each line is processed.
• All parameter values are taken from tblConnectionConfig. If blank, a default value of "" (null string) or "FALSE" is provided for Text and Boolean parameters respectively. Some pre-processing is provided to evaluate validity if a file path is provided; and to push parameter values into the Database Connection String (see below).
• If no Connection or QUERY exists by the name provided, a new one is created using provided or pre-processed parameters.
• Creation doesn't set all parameters listed, so the remaining parameters are set from values from tblConnectionConfig.
• With parameters all set, the Connection or QUERY is refreshed.

Once all entries in tblConnectionConfig have been processed (that is, created and/or updated), all connections contained in the Workbook are examined. Only Connections/QUERYs that have a definition record in tblConnectionConfig are kept; any Connection/QUERY not defined in the Table is deleted. Un-used connections/QUERYs not actively kept up-to-date only cause problems over time when refreshed with parameters that no longer make sense, so this is garbage collection.

The fifth command button initiates #Directive operation from tblSQL. Currently only Export is supported, and is described below.

Let's jump right in to the core creation/update of Connections and QUERYs. Here is a skeleton of VBA to create a new Excel Data Connection, or update an existing one. Then we'll flesh that out and wrap that core into a full-function management of Data Connections using SQL, for SQL Server, MS Access, and PowerQuery.

For this web page, all infrastructure and control logic implementing support functions, buttons, error handling, status updates etc. is NOT SHOWN. The full context for these constructions is contained in vbaConnect.txt which can, as a whole and as available, be placed into a functional Framework like the one described on this website. Please Contact for Details if interested.

If Not boolExists(strConnectionName, thisWorkbook.Connections) Then
    ' If connection(strConnectionName) NOT PRE-EXISTING,
    '   create it with parameters required for creation

    Set thisConnection = thisWorkbook.Connections.Add2( _
        strConnectionName, strDescription, _
        strConnectionString, strCommandText, _
        lCmdType)
                        
Else
    'If connection(strConnectionName) IS PRE-EXISTING, update its params required 
    '   for creation so that it's in state equivalent to a newly-created 
    '   connection

    Set thisConnection = thisWorkbook.Connections(strConnectionName)

    If thisConnection.OLEDBConnection.Refreshing Then _
            thisConnection.OLEDBConnection.CancelRefresh
        ' Turn off ongoing activity if any - it will refresh later

    thisConnection.Description = strDescription
    thisConnection.OLEDBConnection.Connection = strConnectionString
    thisConnection.OLEDBConnection.CommandText = strCommandText
    thisConnection.OLEDBConnection.CommandType = lCmdType
                                           
End If

Below we'll describe where the str and bool parameters come from; Connection String Templates; Error Handling; string replacement function strReplaceString2; further functionality to complete parameter updates, to refresh connections, and cleanup of un-used connections; and handling of SQL CREATE and DROP.

In the Prototype Framework to be described on this site, Connections in an Excel workbook are described in the Excel UI within a Structured Table, one Connection described in each table record. The Connection parameter values are contained in fields (columns) in each record, each named for the parameter specified. The table currently lists 26 values for each connection. Fortunately, templates are provided in the table that describe many styles of connection (ACE, MSOLEDBSQL; Data Connection PowerQuery; embedded SQL, Stored Procedure call; etc.) each showing template parameter settings for its connection type.

The table is named "tblConnectionConfig" and is visible to VBA as a ListObject by that name as is usual for a Structured Tables. (Clicking the image below will bring up an expanded image.)

When Connections are built or updated, as commanded by a button object shown at bottom left in the preceding figure, the table is processed record-by-record. As each record is chosen, its parameter values are read from the table and set into VBA variables. The values are either string or boolean type, as determined by the use of the parameter in VBA procedure calls to build or configure each Connection, as shown in the VBA code above as needed for the Microsoft procedures called (e.g., "Add2" or the Workbook.Connections.Connection object itself.).

Here's VBA code to take values from the table, or to set a default value if none is specified, and put the the value into a VBA variable. First, self-contained statements to take values directly from the table: an example of a parameter with a string value and another with a boolean value:

Set connectionSheet = thisWorkbook.Worksheets("DataSources")
Set tblConnectionConfig = connectionSheet.ListObjects("tblConnectionConfig")
Set rngHeaderRow = tblConnectionConfig.HeaderRowRange

If WorksheetFunction.Match("Alternate Path", rngHeaderRow, iMatchTypeExact) > 0 Then _
    strAlternatePath = thisListRow.Range.Cells(WorksheetFunction.Match("Alternate Path", _
    rngHeaderRow, iMatchTypeExact)) _
    Else strAlternatePath = ""

If WorksheetFunction.Match("BackgroundQuery", rngHeaderRow, iMatchTypeExact) > 0 Then _
    boolBackgroundQuery = CBool( _
        thisListRow.Range.Cells(WorksheetFunction.Match("BackgroundQuery", _
        rngHeaderRow, iMatchTypeExact))) _
    Else nameIndex = False

'   ...and repeat one of these two statements for each of 26 parameters
'       to be extracted from tblConnectionConfig as each Connection record is processed.

Each of those is a mouthful that will be repeated 26 times. So they can be simplified a bit by first associating a table column number with each column name, instead of using a MATCH function twice to do that job in each case:

' First build a header collection associating each header name with a column number:
' Clear out collection of header names prior to populating
For iCellIndex = cHeaderCollection.Count To 1 Step -1
    cHeaderCollection.Remove iCellIndex
        
Next iCellIndex
' Then populate the collection of header names
For iCellIndex = 1 To tblConnectionConfig.HeaderRowRange.Count
    cHeaderCollection.Add   Item:=iCellIndex, _
                            Key:=tblConnectionConfig.HeaderRowRange.Cells(iCellIndex)
        
Next iCellIndex


' Then use that header collection to fetch each parameter value into a VBA variable:
If boolExists("Alternate Path", cHeaderCollection) Then _
    strAlternatePath = thisListRow.Range.Cells(cHeaderCollection.Item("Alternate Path")) _
    Else strAlternatePath = ""

If boolExists("BackgroundQuery", cHeaderCollection) Then _
    boolBackgroundQuery = CBool(thisListRow.Range.Cells(cHeaderCollection.Item("BackgroundQuery"))) _
    Else boolBackgroundQuery = False

'   ...and repeat one of the last two statements for each of 26 parameters 
'       to be extracted from tblConnectionConfig as each Connection record is processed.

' When Connection defined in tblConnectionConfig exists, either pre-existing or just created, 
' update its params beyond creation-required params

' strRefreshEnable
thisConnection.RefreshWithRefreshAll = boolRefreshAll
If boolExists(thisConnection.Name, colPivotCache) Then  
' Checks if connection is used to drive a pivot. 

    If thisConnection.OLEDBConnection.Refreshing Then _
        thisConnection.OLEDBConnection.CancelRefresh  
        ' Turn off ongoing activity if any - it will refresh later
    If thisConnection.OLEDBConnection.EnableRefresh <> boolEnableRefresh Then _
        thisConnection.OLEDBConnection.EnableRefresh = boolEnableRefresh   
        ' May cause error, reason unknown
    thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
    thisConnection.OLEDBConnection.MaintainConnection = boolMaintainConnection
    thisConnection.OLEDBConnection.RefreshOnFileOpen = boolRefreshOnOpen
    thisConnection.OLEDBConnection.RefreshPeriod = intRefreshPeriod
                    
End If  ' Connection Exists
                
' Refresh Connection after build / update. strSkipBuildUpdate does NOT affect Refresh.
' For button RebuildRefreshPivotSourceWebQuery or RefreshPivotSourceWebQuery, refresh PivotSource
' For button RebuildRefreshMPPImportMPPQuery, refresh mppImport
If boolExists(strConnectionName, thisWorkbook.Connections) Then
    Set thisConnection = thisWorkbook.Connections(strConnectionName)
                        
    If Not thisConnection.OLEDBConnection.Refreshing Then 
        ' If it's already refreshing then skip it, else refresh depending on buttons

        thisConnection.OLEDBConnection.Refresh
                        
    End If
    DoEvents
                    
End If  ' connection exists
            

There are still some parameters remaining to configure Connections and PowerQuerys - those that must be included within the ConnectionString parameter.

Those Connection Strings are defined by templates of the form:
For Data Connections:
OLEDB; Provider=MSOLEDBSQL; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; for MSOLEDBSQL to a SQL database, or

OLEDB; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; for SQLOLEDB to a SQL database, or

OLEDB; Provider=Microsoft.ACE.OLEDB.12.0; data source=FFFFFF; Mode=MMMMMM; for ACE connection to a source File

For PowerQuerys:
let Source = OleDb.DataSource("provider=PPPPPP;initial catalog=DBDBDB;data source=SSSSSS", [Query="QQQQQQ"]) in Source for connection via OLEDB, or

let Source = Excel.Workbook(File.Contents("FFFFFF"),null,true), SheetImport=Source{[Name="TTTTTT"]}[Data], SheetImportPromoted=Table.PromoteHeaders(SheetImport, [PromoteAllScalars=true]) in SheetImportPromoted for connection to an Excel File worksheet, or

let Source = Sql.Database("SSSSSS", "DBDBDB", [Query="QQQQQQ"]) in Source for connection to a SQL Server database, or

let Source = Access.Database(File.Contents("FFFFFF"), [CreateNavigationProperties=true]), ImportQuery = Source{[Schema="",Item="TQTQTQ"]}[Data] in ImportQuery for connection to an MS Access database.

PowerQuery uses the Power Query M formula language to describe connection and data import.

Instead of fully parsing each string, we are going to find and replace sub-strings. For that, function strReplaceString2 (found in vbaConnect.txt) performs similarly to the VBA Replace function. The parameter values to be updated/replaced may occur within a sub-string terminated by a ";" character, or at the end of a string terminated by a Quote character in which case there is no terminating ";" character. So strReplaceString2 looks for termination by the earlier occurence of one of two possible termination characters.

A collection of core patterns for Connection-Strings and M-Strings is provided at this link.

Be aware that in any #Template, substitution will occur for the first match for a pattern subject to that #Template. This is great for Connection Strings, but a little more complicated for #Templates for M, including SQL inserted into an M string. Substitution does not parse syntax of Connections Strings, but will replace the first pattern match; so /* comments */ or //comments should occur AFTER any code in a SQL or M string that could be replaced.

And again, for this web page, all infrastructure and control logic implementing support functions, buttons, error handling, status updates etc. is NOT SHOWN. The full context for these constructions is contained in vbaConnect.txt which can, as a whole and as available, be placed into a functional Framework like the one described on this website. Please Contact for Details if interested.

Function calls to strReplaceString2 are chained to replace target sub-strings in Connections, like this:

' strConnectionString: Make replacements for Connection String
strConnectionString = strReplaceString2("Data Source=", ";", "*", strConnectionString, _
        strSourceTarget)
        ' Replace Data Source
strConnectionString = strReplaceString2("Workstation ID=", ";", "*", strConnectionString, _
        strWorkstation)
        ' Replace Workstation ID
strConnectionString = strReplaceString2("Initial Catalog=", ";", "*", strConnectionString, _
        strDatabase)
        ' Replace Initial Catalog
strConnectionString = strReplaceString2("Mode=", ";", "*", strConnectionString, strMode)
    'Replace ACE Read Write Mode

...and like this for PowerQuery:

Select Case LCase(strProvider)
    Case "powerqueryoledb"
        strConnectionString = strReplaceString2("provider=", ";", """", strConnectionString, _
                "MSOLEDBSQL")  ' Replace strProvider
        strConnectionString = strReplaceString2("initial catalog=", ";", """", strConnectionString,_
                strDatabase) ' Replace strDatabase
        strConnectionString = strReplaceString2("data source=", ";", """", strConnectionString, _
                strServerName)  ' Replace strServerName
        strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                strCommandText) ' Replace strCommandText
        strConnectionString = updateQueryTableName(strConnectionString, "_Table =", strTableName)
            ' If there's a Table name within strConnectionString, replace it 
            '   by the strTableName parameter.
                    
    Case "powerqueryexcel"
        strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                strConnectionString, strSourceTarget)
        strConnectionString = updateQueryTableName(strConnectionString, "=Source{", strTableName)
            ' If there's a Table name within strConnectionString, replace it 
            '   by the strTableName parameter.
                        
    Case "powerquerysqlserver"
        strConnectionString = strReplaceString2("Sql.Database(""", """,", "*", strConnectionString,_
                strServerName) ' Replace strServerName
        strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                strCommandText) ' Replace strCommandText
        iCurLeft = InStr(1, strConnectionString, ",", vbTextCompare)    
            ' Replacing the Database name is harder because it has no unique delimiter
        iCurLeft = InStr(iCurLeft, strConnectionString, """", vbTextCompare) + 1
        iCurRight = InStr(iCurLeft, strConnectionString, """", vbTextCompare) - 1
        strStringToReplace = Mid(strConnectionString, iCurLeft, iCurRight - iCurLeft + 1)
            ' Capture current-string to replaced within strConnectionString
        If InStr(1, strConnectionString, strStringToReplace, vbTextCompare) > 0 Then 
            ' Check if we found the string to replace
                strConnectionString = Replace(strConnectionString, strStringToReplace, _
                strDatabase, 1, -1, vbTextCompare) 
            ' Match current-string within strConnectionString and replace by make-string
                        
        End If
                        
    Case "powerquerymsaccess"
        strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                strConnectionString, strSourceTarget)
        strConnectionString = updateQueryName(strConnectionString, "Item=""", """", strTableName)   
            ' If there's a Table name within strConnectionString, 
            ' replace it by the strTableName parameter.
                        
End Select

If Not boolExists(strConnectionName, thisWorkbook.Queries) Then
    ' Here's the Build action
    Set thisWBQuery = thisWorkbook.Queries.Add(strConnectionName, strConnectionString, _
        strDescription)
                        
' If it pre-exists, update it
Else
    ' Update pre-existing QUERY Connection String and Command Text
    Set thisWBQuery = thisWorkbook.Queries(strConnectionName)
                        
    If strSkipBuildUpdate = "Update" Then
        strConnectionString = thisWBQuery.Formula
        ' If Update, start with pre-existing string in QUERY, else re-start from template
                                          
    End If

    ' Build the Connection String
    Select Case LCase(strProvider)
        Case "powerqueryoledb"
            strConnectionString = strReplaceString2("provider=", ";", """", strConnectionString, _
                    "MSOLEDBSQL")  ' Replace strProvider
            strConnectionString = strReplaceString2("initial catalog=", ";", """", _
                    strConnectionString, strDatabase) ' Replace strDatabase
            strConnectionString = strReplaceString2("data source=", ";", """", _
                    strConnectionString, strServerName)  ' Replace strServerName
            strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                    strCommandText) ' Replace strCommandText
            strConnectionString = updateQueryTableName(strConnectionString, "_Table =", _
                    strTableName)   
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
                        
        Case "powerqueryexcel"
            strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                    strConnectionString, strSourceTarget)
            strConnectionString = updateQueryTableName(strConnectionString, "=Source{", _
                    strTableName)   
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
                                
        Case "powerquerysqlserver"
            strConnectionString = strReplaceString2("Sql.Database(""", """,", "*", _
                    strConnectionString, strServerName) ' Replace strServerName
            strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                    strCommandText) 
                ' Replace strCommandText
            iCurLeft = InStr(1, strConnectionString, ",", vbTextCompare)    
                ' Replacing the Database name is harder because it has no unique delimiter
            iCurLeft = InStr(iCurLeft, strConnectionString, """", vbTextCompare) + 1
            iCurRight = InStr(iCurLeft, strConnectionString, """", vbTextCompare) - 1
            strStringToReplace = Mid(strConnectionString, iCurLeft, iCurRight - iCurLeft + 1) 
                ' Capture current-string to replaced within strConnectionString
            If InStr(1, strConnectionString, strStringToReplace, vbTextCompare) > 0 Then 
                ' Check if we found the string to replace
                strConnectionString = Replace(strConnectionString, strStringToReplace, _
                    strDatabase, 1, -1, vbTextCompare) 
                ' Match current-string within strConnectionString and replace by make-string
                                
            End If
                            
        Case "powerquerymsaccess"
            strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                    strConnectionString, strSourceTarget)
            strConnectionString = updateQueryName(strConnectionString, "Item=""", """", _
                    strTableName)   
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
                        
    End Select
                        
    thisWBQuery.Formula = strConnectionString
    thisWBQuery.Description = strDescription
                        
End If
                    
' Update common parameters of QUERY Connection
If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
    Set thisConnection = thisWorkbook.Connections("Query - " & thisWBQuery.Name)
    If thisConnection.OLEDBConnection.Refreshing Then _
            thisConnection.OLEDBConnection.CancelRefresh
    ' Cancel refresh if active
    thisConnection.RefreshWithRefreshAll = boolRefreshAll
    If (thisConnection.Type = xlConnectionTypeOLEDB And _
            strConnectionType = "xlConnectionTypeOLEDB") Then

        thisConnection.OLEDBConnection.EnableRefresh = boolEnableRefresh
        thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
        thisConnection.OLEDBConnection.MaintainConnection = boolMaintainConnection
        thisConnection.OLEDBConnection.RefreshOnFileOpen = boolRefreshOnOpen
        thisConnection.OLEDBConnection.RefreshPeriod = intRefreshPeriod
                            
    End If

End If

If boolExists(strConnectionName, thisWorkbook.Queries) Then
' Check if QUERY exists - created or pre-existing
    Set thisWBQuery = thisWorkbook.Queries(strConnectionName)
                            
        If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
            Set thisConnection = thisWorkbook.Connections("Query - " & thisWBQuery.Name)
            If Not thisConnection.OLEDBConnection.Refreshing Then
            ' If it's already refreshing let it go
                thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
                thisConnection.OLEDBConnection.Refresh
                                
            End If
            DoEvents
                            
        End If  ' Refresh Operation
                                                        
    End If  ' REFRESH Button check
                
End If  ' QUERY existence check

' Once defined connections are handled,
'  delete existing Connections not defined in tblConnectionConfig
For Each thisConnection In thisWorkbook.Connections
     
    ' Connections defined in tblConnectionConfig were saved into colConnectionList
    ' Delete connection if it's not in colConnectionList,
    '   as long as its name doesn't begin with "Query -"

    If Not boolExists(thisConnection.Name, colConnectionList) Then
        If Not (Left(thisConnection.Name, 8) = "Query - ") Then
            If thisConnection.OLEDBConnection.Refreshing Then _
                thisConnection.OLEDBConnection.CancelRefresh
            ' Stop refresh before deleting it

            thisConnection.Delete
                    
        End If
                
    End If
        
Next thisConnection
    
' Once defined QUERYs are handled, delete existing QUERYs not defined in tblConnectionConfig
For Each thisWBQuery In thisWorkbook.Queries

    ' Queries defined in tblConnectionConfig were saved into colWBQueryList
    If Not boolExists(thisWBQuery.Name, colWBQueryList) Then
                
        ' Delete the Connection
                
        thisWorkbook.Connections("Query - " & thisWBQuery.Name).OLEDBConnection.CancelRefresh
        ' Stop any ongoing refresh before deleting

        thisWorkbook.Connections("Query - " & thisWBQuery.Name).Delete
                
        ' Then delete the associated QUERY
        If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
            thisWBQuery.Delete
                
        End If
                
    End If
    
Next thisWBQuery

Most Connections will have SQL embedded, so it's convenient to build SQL strings within Excel. Even more than convenient, in Excel you can use the Common Configuration Variables specifying file names and paths, localized as needed; you can reference other Stored Procedures and Linked Servers by name, whose SQL strings for CREATion are themselves created within Excel (referencing local variables etc.), to EXEC those SPs from within SQL strings created subsequently. In short, Excel provides a computational infrastructure useful especially for creating SQL strings that are related to each other and which share Source Data entities and paths to their locations.

The Framework described on this website provides Common Configuration Variables that can provide some leverage, consistency, and robustness to solutions built on it. Those variables include Source Data filenames and paths; database location and instance names; Linked Server names Export location path; name of the Framework workbook, host processor executing the Framework file. Where useful, those variables provide a localized value which may be a function providing such information; and are provided as Named Ranges to be referenced by name. They could be overwritten as appropriate, although that complicates distribution of updates.

tblLocalizationConfig Common Configuration Variables are used with PowerQuery M primarily in setup of Connection Strings to data sources. For SQL with MS Access, SQL Server and PowerQuery, they are more extensively used, in Connection Strings and also to set up Linked Servers and OPENROWSET QUERYs.

The Configured SQL Editor part of the Framework is conceptually straightforward. Each SQL string is represented in a column within Structured Table tblSQL. The name of the column is most useful if it indicates the functionality of the SQL contained in that column. (The section on DROP and CREATE just below describes how SQL strings can be exported .sql files named for their defining column, for use in SSMS and VS-SSDT.) Each cell in the Table takes normal Excel content which can include expressions of any kind, or calls to VBA etc., and which frequently are used to concatenate, parse or lookup other variables to create functional SQL.

Common Configuration Variables are provided in templates including the PowerOpI Framework Environment, local Workstation and Workbook. Templates are provided for Data Sources as Servers, Files, and Linked Servers. A note on OPENROWSET: Although OPENROWSET pulls information from a File data source, it requires all the same information as is needed for a Linked Server even though it differs syntactically and logistically from a Linked Server. When configuring Common Configuration Variables for OPENROWSET, use the Linked Server template which besides the file name and path, will provide named Variables for the Excel Version, ACE Provider and Version for use in OPENROWSET QUERYs just as for Linked Server CREATE QUERYs. There are examples of this in the PowerOpI Template.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Using Common Configuration Variables in tblSQL and tblConnection

As shown in the image below, cells containing simple "simple text" are left un-formatted, and cells containing Excel expressions to concatenate etc. are formatted as bold-blue-italic. SQL Keywords are capitalized.

The Connection Configuration table tblConnectionConfig contains a field specifying SQLName, identifying the name of a column in tblSQL containing SQL to be embedded for each Connection. Using that, field CommandText fetches the SQL String which will be embedded into the Connection on a Build or Update command from one of the Buttons.

Field CommandText uses Excel function XLOOKUP to locate the column in tblSQL named by field SQLName, and uses Excel function TextJoin to concatenate all non-blank cells in that column, delimiting each by a space and line-feed (ignored by SQL) to create a string containing the SQL to be embedded into each Connection or to be exported to a .sql file.

Thus these SQL-Strings are invoked by name (at the top of each column in tblSQL), selected by a pull-down menu in tblConnectionConfig field named SQLName, for each Connection configured in tblConnectionConfig. The value of the SQL-String is then shown for each Connection, in tblConnectionConfig field CommandText.

It would be useful to have an editor to create, configure, and update Connection Strings, and PowerQuery "M" strings. The PowerOpI Framework workbook provides a Configured-String Editor for this. This Editor is fairly basic, but provides more functionality than would be provided by simply stating fixed string values as Connection-Templates and M-Templates.

For M, the PowerQuery Editor in Excel is much more funtional. However, it produces values in its Queries and Connections that are read-only in the User Interface except within the PowerQuery Editor; and that is a manual proposition. The intent of the PowerOpI Framework is to provide a way to create and manage access and use of Excel with database environments, in a way that handles numerous connections with automation so that it can be provided to a distributed set of users.

It is not fully developed and tested, but it is nevertheless intended that a user could go back and forth using the PowerQuery Editor, and the Configured-String Editor, together with the automation of the PowerOpI Framework - the PowerOpI Framework "Update" capability will ultimately provide this capability.

• Limitations to be aware of:
• Only one source-function statement (e.g., M functions Excel.Workbook, Sql.Database, Access.Database, OleDb.DataSource, Excel.CurrentWorkbook), or Connection String, is supported FOR SUBSTITUTION IN TEMPLATES in each M QUERY. String-substitution on connection Build makes a single pass through Provider strings in tblConnectionConfig, replacing QUERY variables in each as invoked by the Provider Template class, by corresponding values for each Provider from tblConnectionConfig. So only the first Source statement will get the replacements. You can still use Common Configutation Variables embedded in the QUERY definitions in tblSQL and tblConnection, to configure as many Source and other statements in each QUERY.
• Replacement is based on string-search and string-substitution, and the QUERY is not parsed, so //comments, /* comments */ and other statements can cause incorrect substitution. Therefore, for correct substitution, the target statement for replacement must be the first encountered in the string that matches the Template pattern. All other statement in the QUERY, including comments, must occur later in the QUERY. Substitutions will be correct or incorrect, depending on order of #template key words found in QUERY.
• Substitution in #Templates should be used for simple QUERYs and Connection Strings, typically a single statement invoking a Connection String or simple M statement. More complex M QUERYs, and M QUERYs with inserted SQL, should be sourced as M statements (and SQL statements) constructed using Common Configuration Variables for localization. These are then subject to M syntax (and SQL syntax) rules. The substitution engine does not parse Connection Strings for /* comments */ or //comments so if they are present in a connection string using a #Template, the comments should appear later in the string than any substring that may be substituted.

The Templates for both Connection-Strings and PowerQuery M-Strings discussed above, are provided via the Configured-String Editor. Connection strings could be added to the Template (ODBC comes to mind), and terms can be updated. Certainly M has more capability than the basic connectivity provided by the M-Template strings provided so there is lots of opportunity there.

First are shown Configuration-Templates in the Configured-String Editor each named as its Provider; followed by M-Templates named to indicate Data Source type.

As in the Configured-SQL Editor, each column in Configured-String Editor source tblConnection represents a String whose Name is the Title of that column. Within each column, each cell represents a term in the Connection- or M-String. Each cell can be created by simple text, or by concatenating values, such as references to Common Configuration Variables; or evaluating any other Excel expression that results in a string value; to specify files, paths, providers etc. Whole terms can be shown for inclusion or hidden for exclusion within the resulting string.

Construction of a data-access clause in an M statement, fetching data for a QUERY from a file specified using a Common Configuration Variable:

These Connection-Strings / M-Strings are invoked by name (at the top of each column in tblConnection), selected by a pull-down menu in the field named Provider, for each Connection configured in tblConnectionConfig. The value of the String selected is then shown for each Connection in tblConnectionConfig field ConnectionString.

Connections specified in tblConnectionConfig specify their Provider connection-string by a UniqueName or a #TemplateName; Provider connection-strings themselves are detailed in tblConnection. Provider #Templates are used to modify a Provider connection-string to use values from tblConnectionConfig when a Connection is Built or Updated. The connection-string for a Provider identified by a UniqueName alone will be passed directly into the Connection as it is Built or Updated. A UniqueName may invoke Template field-substitution by post-pending a #TemplateName, as UniqueName#TemplateName. Connection-Strings that can be used as Templates are named with a "#" as the first character in the name, as #TemplateName. Each #TemplateName determines a set of fields within the connection string that will be updated and overridden when a connection based on it is Built or Updated. Templates exist for the following for Excel Data Connections: #Microsoft.ACE.OLEDB.12.0, #Microsoft.ACE.OLEDB.16.0, #MSOLEDBSQL, and #SQLOLEDB; and for PowerQuery M strings: #OleDb_DataSource, #Excel_Workbook, #Sql_Database, #Access_Database, and #Table_Join following the naming convention for the functions used in M for these operations: OleDb.DataSource, Excel.Workbook, Sql.Database, Access.Database, and Table.Join.

In the figures above illustrating some of the templates, note the values highlighted in red in the Template strings. These values of these parameters will be overridden by values from fields in tblConnectionConfig at the time each Connection or M-String is built. The Connection builder uses the #TemplateName part of the Provider connection-string name, to determine to determine which parameters to update as determined by the Template. When Updated from pre-exisitng connection-strings, fields not found in an invoked #Templates would not be overidden, and would retain values placed into the M-String in the Configured-String Editor or by the PowerQuery Editor.

The Configured-String Editor concatenates each non-blank cell in a column, delimiting each cell entry by a space and linefeed. The result is a readable string in SSMS VS-SSDT or WordPad etc. You must stay within rules of "M" regarding line breaks . Also, note that indentation of text in a cell is a visual effect only, not affecting the cell value: if a space is needed syntactically, an actual space character must be inserted properly in the string value.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Again, the Configured-String Editor is pretty basic right now, but it provides infrastructure that may become more valuable as more functional use of M is explored with the PowerOpI Framework and its automation.

One of the recent changes to Excel eliminated capability to CREATE or DROP database entities such as Linked Servers, Stored Procedures, or Tables. That was useful for creating those entities for use with QUERYs interacting with Source Data and Reports in Excel, especially where those QUERYs are related to each other and can use Common Configuration Variables; obviously that function is needed somewhere.

That function is available in SQL Server Management Studio (SSMS) and in Visual Studio (VS-SSDT). Both of those can execute SQL stored in .sql files, so functionality is included in the Framework described on this website, to export SQL created in its Configured SQL Editor to such .sql files.

A Button is provided, Execute tblSQL #Directives, to do the export. The location to be exported to is defined in Common Configuration Variable ExportTo, and is commonly set to the directory from which SSMS and VS-SSDT can be directed to open .sql files in their Open File... dialogues.

The Framework creates a directory structure, if needed, in the SSMS/VS-SSDT/VSCode file directory specified as the ExportTo Variable, and exports .sql files there. The default ExportTo value is C:\Users\WorkstationLocalLogin\Documents\SQL Server Management Studio[\ExportToSubstring]\WorkbookCurrentDirectory\WorkbookName (without extension)\Variables Tab Profile. (optional fields in brackets). The result is a path specification like C:\Users\Owner\Documents\SQL Server Management Studio\Merge\TestOLEDB\ProfileLocal. The default structure resembles the directory structure to the PowerOpI Framework file exporting the SQL, appending the Variable profile specifying file locations. The default can of course be overridden in Excel tblLocalizationConfig. The exported files can then be used in SSMS/VS-SSDT/VSCode to manage LSs, SPs, and Tables. This structure supports multiple Framework instances on a computer, and multiple databases and projects within a SQL Server instance.

A separate .sql file is exported, overwritten if needed, to that directory, for each column in tblSQL containing a Directive string formatted as #VBTEXTJOIN: EXPORT; or #VBTEXTJOIN: EXPORTWITH domainName;. Note the hashtag and colon character , and closing semi-colon. It is recommended that a Directive String be placed within a SQL Comment so that SQL will ignore it, so usually a cell in the SQL string definition column contains /* #VBTEXTJOIN: EXPORT; */ or /* #VBTEXTJOIN: EXPORTWITH dbo; */ (or other domain name you specify) .

So files are exported that cannot be executed from Excel, and must be executed via SSMS or VS-SSDT - certainly DROP and CREATE. Those files when opened in SSMS/VS-SSDT must be linked to the intended database within the SQL Server instance. The SQL itself must conform to the GO batch construction of SSMS. The Framework shows an example in which a Stored Procedure configuring Microsoft.ACE.OLEDB.12.0 is EXECed prior to CREATEing a Linked Server; that EXEC must be separated within a batch step by GO. Note that a SQL string intended to be embedded into an Excel Connection rather than be executed by SSMS/VS-SSDT (e.g., to EXEC a Stored Procedure from Excel, that was created by SQL executed from a file exported for SSMS/VS-SSDT), would not contain the GO syntax.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Organizing Storage and Permissions by Project and Domain

• A server instance may include several domains each with different permissions among different databases in a SQL Server Instance, particularly in a development environment, and this affects naming and storage organization for Stored Procedures and SSMS procedures. This is supported using Common Configuration Variables domain and project. Domains, Roles, and Permissions in SQL Server are complex, and some good references and code are shown at Permissions and Membership on this website.

• Domain examples could include dbo (database owner) or guest; and others can be created to organize code and manage permissions within a database you might set up for a project. There is a Common Configuration Variable named domain that sets a domain value to be used when CREATING or EXECing a Stored Procedure in tblSQL to define a stored procedure specific to a domain, for example named dbo.spBuildPlanLS_Proto. Other procedures can also append the domain variable in SQL statements to refer to domain-specific procedures.

• A Domain would be specified within Excel tblSQL by concatenating a variable from tblLocalizationConfig defining domains used for each profile (domain, domain2,... or any you might add to the table). Such a domain specification is optional (just leave the variable value blank if unused). However, you don't want a superfluous "dot" connector added within a QUERY or file name if the domain is left blank. For this reason, a function is provided by the PowerOpI tool framework called strAddADotIfNotNull(string,connector) to be used to insert such an optional value. Here is an example showing how to use this to add an optional domain specification: ="CREATE PROCEDURE " & strAddADotIfNotNull(domain,".") & "spUnionGroup" & LinkedServer1DataSourceFIleRaw & " AS". There are numerous such examples provided in tblSQL. And you can avoid any complexity by just writing SQL in that table, without using the Common Configuration Variables, although those wouldn't re-configure by the automation provided by the Variables.

• A Linked Server is a Server Entity, but in a development environment a named LS may need to point to different files in different projects, so that could be reflected by creating linked servers with multi-part names such as LS_ProjectName_Proto, to differentiate and organize Linked Servers among several projects within a SQL Server Instance.

• We can export SQL files be executed on SSMS that are specific to a domain and project. A second directive is supported, called in an SP definition in tblSQL as /* #VBTEXTJOIN: EXPORTWITH domainName; */. For this Directive, the command is EXPORTWITH; and the string domainName specifies the domain intended. The result of using this directive is that an exported file will include the domain name in the active profile in Common Configuration Variables within the name of the exported file, such as EXEC_dbo.spBuildPlanLS_ToolSource.sql. Furthermore, files are exported to a sub-directory named for the project to which they apply.

• So an SP defined in a tblSQL column named EXEC_spUnionGroupToolSource and including directive /* #VBTEXTJOIN: EXPORTWITH dbo; */ would be exported to a file EXEC_dbo.spUnionGoupToolSource, including the domain name as shown. So that multiple domains can export and use SPs specific to each domain. The rule for using the EXPORTWITH domainName keyword is to include that keywork in any procedure (named as DROP_, EXEC_, CONFIG_, CREATE_ with included underscore character) if the name of the entity CREATEd includes the domain name variable in its name. Then the entity, any SPs referencing it, and SSMS directive files can include the domain name as shown, to point to the entity specific to the intended domain.

Naming, Saving, and Organization of Stored Procedures, Exported SQL, and Linked Servers

• SQL procedures exported via Directives button, are stored in the directory specified by the ExportTo Common Configuration Variable in each tab Variables profile. Typically that variable points to a directory from which SSMS/VS/VSCode will open files. It specifies a sub-directory, named after the PowerOpI file, in which the Directive will save the SQL procedures for that profile. SQL Procedures can include a domain name (Common Configuration Variable for each Variable profile) in the filename of the saved procedure to indicate its intended scope, by using the /* #VBTEXTJOIN: EXPORTWITHDOMAIN; */ construct. So there is a directory created for each specified project, containing exported SQL files for SSMS, each file named including the name of the domain to which it applies.

• SPs CREATEd in tblSQL, in procedures exported as above and executed in SSMS/VS/VSCode. SPs when CREATEd are saved within a database, to <databasename>\Programmabililty\Stored Procedures\ where <databasename> is selected manually in SSMS/VS/VSCode, or specified by a statement USE <databasename>; in the CREATion procedure, where <databasename> can be specified by a Common Configuration Variable such as Server1Database1 shown in tab Variables. SQL Procedures can concatenate a domain name (Common Configuration Variable for each Variable profile) at front of the SP name in the Stored Procedures\ directory, to indicate its intended scope.

• Linked Server organization and naming. Linked Servers (LS) when CREATEd are saved to Server Objects\Linked Servers\. LS are associated with files linked, and not directly associated by SQL Server with any particular database or domain. Within the Linked Servers directory, they can be organized by utilizing multi-part names, constructed using Common Configuration Variables to indicate scope such as filename linked, and possibly indicating function of data contained in the file.

The Power Query M Formula Language seems to be the real power behind Power Query. The Power Query UI basically creates M to perform tasks specified in the UI. Even more capabililty can be accessed by going to M directly, via the Advanced Editor button in the Power Query UI or by creating a Blank Query from the Excel UI or Power BI Desktop UI.

Power Query M can be developed, with grammar coloring and Intellisense, in Excel and in PowerBI Desktop; and Extensions are available for both Microsoft Visual Studio Code (VSCode) and Microsoft Visual Studio.

PowerQuery M is handled by the PowerOpI Framework, with M source code in the Configured-String Editor. M sourcecode in the Power OpI Framework in Excel can be created using the configuration variables available in the Framework, keeping things coordinated with other Queries, with SQL and embedded QUERYs, with Linked Servers, OPENROWSET, and source table names among many examples. M-Strings are chosen for use in Power Query Queries in the tblConnectionConfig Table, for LinkTypes webQuery and mppQuery, and select among Provider strings in the Configured-String Editor source table tblConnection. M-Strings are set into the WorkbookQuery.Formula property. In the Excel PowerQuery User Interface, these strings could be incorporated into QUERYs using the PowerQuery Advanced Editor.

A Query created in the Power Query UI can be updated from the Power OpI Framework, and vice-versa. Such a Query can be set to "Skip" in the Power OpI tblConnectionConfig to avoid updates.

Creating PowerQuery Query to JOIN using M Formula Language:
Excel Menu "Data" > Get Data > From Other Sources > Blank Query > Tab "Home" > Advanced Editor >
and then paste in an M "LET ... IN" statement from
MTemplatesMStrings.txt or
below in this MStrings section of this website
and edit the statment.
Then Save and Load... etc.

Reference and Training on M: Written documentation of M is comparatively scarce. Much available documentaion centers on the Power Query UI and DAX. There is quite a bit of material on YouTube though, although in usual fashion info on M is scattered across many of them.

• Microsoft specification for PowerQuery M Formula Language. Pay particular attention to the sections on Operators and Understanding Power Query M Functions for topics key to use of M, while other sections provide useful foundation and reference.

• YouTube Channel Sigma Coding. Okay here we go... 2019 and VSCode.
  • M Language Tutorial: Introduction, Data Types & Fundamentals, Working with Records, Working with Data Sources
  • Working with Connection Objects: Power Query Connections, Workbook Connections Collection, Excel OLEDB Connections

• Chris Webb's BI Blog site, and his YouTube video Power Query Beyond the Basics, illustrate deeper capabilities of M.

  • The site covers much more, but accessing Named Ranges from M, which may be a common need in addition to access to Structured Tables, is described in a post on Working With Excel Named Ranges In Power Query.

• Deepest book on Power Query I've found to date. Enumerates and describes data sources in some detail. Some description of M as a function language, a little deeper than available Microsoft documentation. Does NOT describe the object returned from each procedure though, which is needed to effectively program M - you still need to hack from the Power Query UI and examine in the Advanced Editor, to get a view of that. Detail in "hacking" note just below.

  Data Mashup with Microsoft Excel Using Power Query and M
  
  Copyright © 2020, Adam Aspin &
  Adam Aspin
  Published by APress

• MSDN Channel 9 TechEd video, Deep Dive into the Power Query Formula Language - dated 2014 but a good place to start.

• The BICCOUNTANT Blog lists several more Learning Resources.

• Research, Ask Questions, other Resources:
  • Microsoft PowerBI Community Forums.
  • Stack Overflow tag "M", tag "powerbi" and tag "powerquery".

• Hacking Finally, lacking concise documentation on M, I've sometimes used the Power Query Editor to partially implement some functionality, then used the Advanced Editor there to determine the code pattern generated. For example, I have nowhere found documentation leading to constructs Source{[Item="stringtablename",Kind="Table"]}[Data] and Source{[Name="stringtablename"]}[Content] found when using some of the M Data Access functions, each of which returns a Table. To shorten the hacking process, here is a collection of M patterns for some commonly-used functionality. (Note, the M code snippets in this list are preceded by some core patterns for database connection strings.) And as I find, or better understand, available documentation I'll incorporate that here.

• Handy reference here shows data access function calls, with table extraction associated with the returned object: Collection of M patterns for
  OleDb.Query,
  OleDb.DataSource,
  Access.Database,
  Sql.Database,
  Excel.Workbook, and
  Excel.CurrentWorkbook.
  
  Also, several M patterns using
  JOINs,
  Connection Strings,
  Add Columns,
  SQL Linked Servers,
  SQL OPENROWSETs, and
  Table.Combines.

M executes operations from the local computer Client, including Queries with JOINs. Query sourcecode is visible to the local Client computer which could be a disadvantage for sustaining and security as compared with Stored Procedures in other environments. Although the Query is initiated from the Client, Power Query delegates work to the server using a method called "Query Folding".

Described in the Learning references above, the M Language constructs use a "LET ... IN..." structure. In each example below, a LET clause performs a sequence of table sourcing operations to get access to the five example Tables. Then a series of JOINs of two Tables at a time, followed by adding Columns to calculate aggregated Quantity and Cost. The IN clause states which of the Variables is output by the M-String. The Source methods differ among these examples, but all use the same methods for JOINs, Columns and Output.

M-Strings are shown above in the Configured-String Editor section that push a SQL Query for execution by SQL Server or by MS Access, using either SQL embedded in the QUERY or invoking a Stored Procedure within the database environment itself, placed there by a CREATE operation as discussed just above. This is an easy introduction to using PowerQuery primarily as a source data access vehicle.

M is much more powerful than just EXEC'ing a SQL SP, and can create, traverse, JOIN, and much more, over complex data structures including the Table and Record structures illustrated in the examples below that are useful for structured data problems similar to the Project Management Logistics problem described on this website. Chris Webb's video Power Query Beyond the Basics illustrates broader capabilities.

---

JOIN Excel Tables from a separate Workbook, could of course be directed to Tables in the local Workbook. This M code replicates function the JOIN shown in several illustrations on this site, JOINing Tables within Excel using PowerQuery without use of a Database environment.

Table_Join

let
    Source=Excel.Workbook(File.Contents("C:\PowerOpI\Merge\ToolSource.xlsm"), null, true),

    xlAlloc=Source{[Item="tblAlloc",Kind="Table"]}[Data],
    xlBOM=Source{[Item="tblBOM",Kind="Table"]}[Data],
    xlDates=Source{[Item="tblDates",Kind="Table"]}[Data],
    xlBuilds=Source{[Item="tblBuilds",Kind="Table"]}[Data],
    xlElements=Source{[Item="tblElements",Kind="Table"]}[Data],

    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter),
    ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter),
    ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter),

    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]),
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost])

in
    ABDBE

---

JOIN Tables from Excel, picked up from a Linked Server in SQL Server. Linked Servers are a SQL Server concept so SQL is used to navigate them from PowerQuery. The JOIN is executed in PowerQuery, not in SQL Server. For comparison, this example again replicates the Query commonly used as an example on this website. Make sure Excel doesn't auto-correct the "..." construct by a single ellipsis character! Also note, line breaks have been inserted here to make the display fit on the website pagewidth; in practice, linefeeds can occur within SQL statements and Connection String but not within the M content.

PQJoin2#OleDb_DataSource

let
    xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngAlloc"]),
    xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngBOM"]),
    xlDates=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngDates"]),
    xlBuilds=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngBuilds"]),
    xlElements=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngElements"]),

    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter),
    ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter),
    ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter),

    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]),
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost])

in
    ABDBE

---

JOIN Tables from Excel, picked up using OPENROWSET in SQL Server. Again, OPENROWSET is a SQL Server concept so SQL is used to navigate it from PowerQuery. This example again replicates the Query commonly used as an example on this website, for comparison. Again note, line breaks have been inserted here to make the display fit on the website pagewidth; in practice, linefeeds can occur within SQL statements and Connection String but not within the M content.

PQJoinOPENROWSET

let 
    FileSource = "Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS",
 
    xlAlloc = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngAlloc)"),
    xlBOM = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngBOM)"),
    xlDates = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngDates)"),
    xlBuilds = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngBuilds)"),
    xlElements = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngElements)"),
 
    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), 
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), 
    ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), 
    ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), 
 
    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), 
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) 
 
in 
    ABDBE

Once a PowerQuery is created, embedding one of these M-Strings, it is assigned to drive a Report such as a PivotTable, using the method for PowerQuery in the following Section covering Driving Pivot Tables.

Notice that each of these QUERYs shows Table/Named Range names, and field names linking them, thereby exposing database structure; and one of the QUERYs shows fully-qualified file paths. Setup of a Linked Server would also expose paths. The text for each of these QUERYs, since it is executed in M at each Client endpoint, resides within each Client and can be made visible there.

For the distributed-user Use Case I generally work within, it is my preference to "hide" File Paths, Table/Range Names, and linking Field names as much as possible from Client visibility. That can be accomplished to varying degree by using Linked Servers within the shared database environment, or even more by putting QUERY logic within Shared Procedures in the database environment, and having each Client make EXEC calls to those rather than executing QUERY logic locally. This is especially true for use of OPENQUERY.

This improves security and system integrity by hiding structure and physical locations, and simultaneously simplifies maintenance - an update to a Shared Procedure in a common database environment is available immediately to all Clients wherever they may be, without making changes to each Client instance. Recall my suggestion that QUERYs, excepting Client EXEC QUERYs driving Client endpoint reports, be authored and managed in a "Control" instance of the PowerOpI Framework, used with one or more Data Source instances available to Source Data table owners, and with multiple distributed Client instances.

In my view, Client-local QUERY execution with full visibliity of QUERY structures within a Client is best-suited to individual-user/Data-Analyst cases, than to a Distributed multi-user Program Management or similar case. Individuals within the Multi-User case may be exceptions, for example Finance management or Dashboard upkeep may make use of specialized Client QUERY configurations.

You may need to pull data from an Excel Workbook that you do not control, and that does not provide Named Ranges or Shadow Ranges to expose the data to MS Access or to SQL Server. However, the Database Environment can see each full Worksheet within the Workbook, which it can interpret to provide Tables.

• For SQL Server or MS Access, using Microsoft.ACE.OLEDB.XX.0 to pick up a Worksheet from Excel, use a name formed as [Name$] for a Worksheet named Name.

• In most other situations you can directly use the form name to specify a Named Range, Shadow Range, or MS Access stored QUERY, to MS Access, SQL Server or PowerQuery M; and to specify an Excel Structured Table to PowerQuery M.

• Avoid duplicating any name, among a Structured Range or Structured Table, with a Worksheet. Excel won't let you duplicate a name between a Structured Range and Structured Table, even if it initially appears to work. But nothing prevents you from naming a Worksheet the same as a Structured Range, and Excel takes evasive action by silently assigning sequential names in the order they are created - and this is not visible in any way inside Excel.

  If a Named Range is named Name and after that you name a Worksheet also as Name it will show that as the name in all aspects of the Excel User Interface; but an internal "silent" name will be assigned such as Name1, numbered in the order created, by which the Worksheet is made visible externally to ACE to the database, even though both the Named Range and the Worksheet tab will display Name in the UI.

  Silent names will be assigned in such a situation sequentially as items are created or re-named to duplicate: Name, Name1, Name2, Name3 etc. Items retain these names even if one of them is deleted (such as, Worksheet Name2 is deleted but the other items retain their silent names as assigned when they were created. The net is that in the case of duplicate names you don't directly control the object name made visible to the database. The moral is: Don't duplicate names in Excel even where it lets you.

• A case of this that you cannot avoid is export to an Excel Workbook of Task Data from MS Project. MS Project creates a Named Range and a Worksheet, naming them with a duplicate name, which Excel modifies sequentially so that the Named Range is named Name, the Worksheet tab shows Name, but the Worksheet name visible to a Database via ACE is probably like Name1 or Name2. When you attempt to connect to the worksheet, you must find the sequential name of the Worksheet that is visible to ACE - see the next paragraph. It seems repeatable as Name2 - so when you've used the code on this website to export from MS Project, to access tab Task_Table1 externally use name Task_Table12.

  Here's the foolproof way to find the "silent name" you can use that contains the data of the full worksheet: These "silent names" don't show on any Excel User Interface elements - not on Tabs, nor in Excel "Formulas" Menu > Name Manager, nor on Tab identifiers in the VBA Development Environment object explorer. But you CAN find the name to use, via Excel "Data" Menu > Get & Transform Data > Get Data > From File > From Workbook > (choose Excel workbook from explorer presented) > Navigator. Available Names pointing to data are shown in the explorer at the left of the panel, and each can be previewed by selecting it. Obviously, you will want to specify the one that shows all the data you're intending to use, plus headers, in the Preview window.

• This is interpreted from observed behavior. I don't have inside knowledge of Excel's implementation but have encountered and investigated these situations. Don't duplicate names, be aware of the duplicate-naming anomaly on MS Project Task export, and be aware of the forms of that name you need to use in the situations described.

When a Connection or Query is created by VBA using tblConnectionConfig, it initally isn't connected to drive a PivotTable, PivotChart or Structured Table. The trick is to get it assigned to such a report, and in such a way that it can be subsequently updated by a Build or Update button operation.

• To assign a VBA-Created Data Connection to a first or subsequent PivotTable / Pivot Chart / Table:
  • Follow from Menu Data > Existing Connections Pane > and DOUBLE CLICK the intended Connection in this Workbook > Import Data Pane.
  • Click button to Properties... Pane and DISABLE (UN-CHECKMARK) both checkboxes: Enable background refresh and Refresh this connection on refresh all. Click OK.
  • Back at Import Data Pane, choose Table or Pivot Table Report or Pivot Chart.
  • Still on Import Data Pane, choose Existing Worksheet and location, or New worksheet. For this Connection-driven Report, DISABLE (UN-CHECKMARK) Add this data to the Data Model. Click OK.
  • 
• To assign a VBA-Created PowerQuery to a first or subsequent PivotTable / Pivot Chart / Table:
  • Follow from Menu Data > Queries & Connections
  • In Queries & Connections pane at right, Right-Click the intended Query > choose Load To..., complete the Import Data pane as above, then OK all the way out.
  • ...or in hover Pop-up Pane click "..." > Load To..., complete the Import Data pane as above, then OK all the way out.
  • 
• Another way to drive a subsequent Report from an already-assigned Connection or Query:
  • Right-Click the Tab, at the bottom of the workBOOK window, for a workSHEET containing an existing Pivot Table, PivotReport or Table that is driven by the Connection or Query you wish to re-use for a subsequent Report and choose Move or copy....
  • Checkmark Create Copy, select the desired location in the Workbook, and click OK.
  • Re-name the duplicated Tab appropriately, and edit the Report on that page.
  • 

The PowerOpI Framework file contains numerous examples that can be used as Templates to be applied to specific projects.

• Source Data Tables: Each Excel table used as Source Data should have the Shadow Range event handler VBA in the code page for the Worksheet Tab that the table is on, in the Source Data workbook. The Source Data workbook should also contain QUERYs and resulting Imported Date Tables, or other Imported Tables, to be used in preliminary calculations (PurchDate, PayDate, Cap/Expense etc.) and then in JOINs in Structured Procedures called from the Reports workbook to drive resulting Reports.
  • Allocation: Source Data table shows material usage by Organization, Purpose, System Name, Owner, and Configuration. Each item allocated also identifies the Purchase Request by which the item is acquired. Setup of complex keys to connect among these Tables, via database JOINs and Excel Lookups, is also illustrated.
  • Bill of Materials (BOM): Source Data table shows product structure representation including systems, assemblies, sub-assemblies, and elements; through build sequence, Capital/Expense determination, rollup of element Cost and Extended Cost, and setup of database keys for BOM explosion. tblBOM can use element aliasing to determine some item quantities.
  • Dates: Illustrates Table import, TaskMilestone key creation, association and use, Date JOINs and usage in other Source Data tables, calculations for OrderDate, PayDate, multi-level integration, calculations added to imported Table, and imported table SelectionChange event handler. tblDates can use element aliasing to determine some item dependent milestone dates.

    In a Program Management application, the Dates table presented to the database environment for JOINs is derived from a table created by import of Tasks data exported from Microsoft Project. Discussion of that export and import, and VBA Code for the export from MS Project, are provided elsewhere on this site.

    The operational flow consists of, first, creating and maintaining the project task structure within MS Project, with task sequencing determined by links reflecting dependencies and deliverables. Also within Project, a task Key field is populated, identifying tasks and milestones to be tied to logistics and reporting.

    Then, when its owner of the MS Project structure is satisfied to provide an update, the Task data is exported from MS Project to an intermediate .xlsx file. In the Power OpI Framework, the exported Task Data .xlsx file is imported into a Structured Table in the Source Data workbook using the Refresh or Update DATES buttons and associated QUERY(s). From there, Task Date data is extracted from the imported Structured Table, into the Dates Table via INDEX/MATCH, XLOOKUP or FILTER/LET constructs in that Table, using the Keys in the imported Table. This also implements Task Aliasing.

    Finally, once the Dates table has been updated to contain the current Task dates, it is JOINed to BOM items to quantify timing for item Logistics using the Update or Refresh DATA buttons the Report workbooks. These buttons are associated with QUERYs or Tables stored in the database environments or local to the Report workbooks. The Dates table is an Excel Structured Table so it is conveniently available for JOINs by Power Query. That table is also provided with a Shadow Range to make it visible for JOINS in SQL Server or MS Access database environments.

  • Builds: Source Data table shows another re-structured table representing Task dates, created from the imported Tasks table, de-normalized to support a special report structure.
  • Element Data: Source Data table illustrates useful element data: Supplier info, configuration data and references, accounting rule parameters, prototype cost escalation rule.
  • Purchase Requests: Each allocated item is acquired via a Supply Chain process, initiated by a Purchase Request resulting from PM project scheduling and logistics planning, sequenced through RFQ, Quote, Approvals, Purchase Order, ETA, Delivery, and Staging. Those steps are identified by various tracking numbers and dates in the sequence through suppliers and shippers. The Purchase Request Table reflects tracking of this process, for coordination of the purchasing process with realization of build, integration and distribution processes as items are received.
  • Normalization: Illustrates un-normalized Source Data table (familiar for human interaction across an organization) transformation to normalized (best for technical use in a database), for JOIN with other Source Data tables. Shows addition of calculated fields to transformed table, with dynamic fit to normalized table on refresh.
• SQL, and M, Structures for Connections, QUERYs, and SSMS/VS-SSDT: This SQL will reside in the Configured-SQL Editor table in Control Workbook if there is one or else in the Reports workbooks. SQL used to call Stored Procedures will reside in the Reports Workbook, to be incorporated into connections in that workbook that drive reports.
  • SQL for Connections and QUERYs to MS Access and SQL Server from Excel, SSMS, and VS-SSDT.
  • SQL to create and Use Linked Server from Excel Connection/QUERY and SSMS/VS-SSDT: Database use of Source Data contained in Excel files.
  • OPENROWSET: SQL to use of Source Data from Excel files, alternative to Linked Servers.
  • SQL to create Stored Procedures from Excel Connection/QUERY and SSMS/VS-SSDT. These create QUERYs with JOINs and other SQL, that will invoke Linked Servers or OPENROWSET to access Source Data from Excel tables. These may also call other Stored Procedures.
  • SQL to be embedded in Excel Connections or QUERYs, or stored in the database environment. This could be SQL to execute a QUERY, or to EXEC Stored Procedures.
  • UNION: SQL to aggregate from multiple Source Data tables into Excel using JOIN with UNION ALL, with Linked Servers or OPENROWSET - for example, to collect organizational input for operational use.
• Reports and Tables driven by Connections and QUERYs: These PivotReports, PivotCharts, and imported Structured Tables reside on Worksheet Tabs in the Reports workbook.
  • Framework shows Result from multiple SQL Structure alternatives.
  • Formatted as PivotTables and as imported Structured Tables.
  • UNION: Illustrating aggregation from several Source Data tables.
  • Complex QUERY Rollup Report: organized by Organization, System, Assembly, SubAssembly, Element, Rev, Expense/Capital, FiscalQtr; organizing Qty and ExtendedCost. This one table can have pivot order changed, could be duplicated and pivot order changed for multiple reports, fields deleted or new fields incorporated etc; for views of Capital and Expense plans by Year, Fiscal Year, by Quarter (which are driven from the mpp GANTT schedule/plan); Purchase and Build plans, Test plans, and many more at many levels of detail from summary to detailed operational instruction. And that's for a small but complex program from just one example QUERY.
  • .mpp / Schedule Date Table: Imported Date Table for joining with other Source Data tables for project use. This Table is typically imported into a Source Data workbook for preliminary calculations involving dates, and for inclusion in subsequent JOINs from the Reports workbook.

Public Sub TableUpdate(ByVal Target As Range)
    'Copyright (c) 2015 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.
    
    'Keeps a range object in sync with table definition.
    'Range object is updated each time the table is changed/selected.
    Dim tblTarget As ListObject, thisListObject As ListObject
    Dim cellChanged As Range, rngTarget As Range
    Dim strTableName As String, strRangeName As String, strTblRangePrefix As String
    
    If Target Is Nothing Then Exit Sub
    Set cellChanged = Target
    strTblRangePrefix = "rng"
    For Each thisListObject In cellChanged.Worksheet.ListObjects
        If Not Intersect(cellChanged, thisListObject.Range) Is Nothing Then
            Set tblTarget = thisListObject
            strTableName = tblTarget.Name
            strRangeName = strTableName
            If Left(strRangeName, 3) = "tbl" Then _
                strRangeName = Right(strRangeName, Len(strRangeName) - 3)
            strRangeName = strTblRangePrefix & strRangeName
            
            Set rngTarget = tblTarget.Range
            rngTarget.Name = strRangeName
            
            Exit For
        
        End If
        
    Next thisListObject
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Module1.TableUpdate Target:=Target
    
End Sub

The cleanest way to keep Dates and Logistics aligned, is to export dates from Microsoft Project, and to tie specific tasks and their dates, to Logistics data handled through PowerOpI database solutions and reports.

In the Framework described on this website, a field is added to the MS Project Tasks page, called TaskMilestone containing a Key to uniquely identify tasks with their associated dates contained within a task record. The form of the TaskMilestone Key is ElementName:ElementRevision:Task - for example "Europa:P0-A:Fabout" (with the colon characters but without the quotes). Or you could create your own identifier scheme if you prefer. So the dates and other data in that particular task associated with this Key value can be identified. You must ensure that that Key value identifies a unique Task record.

You would create a similar Key within Excel Source Data tables, so you can JOIN the task dates with material or expense logistics items.

Code to export the date file from MS Project is in Code Samples 01. The file exported is in an .xlsx file named for the MS Project file exported from, and contains a worksheet referred to as [Task_Table1$] when it is imported into Excel via SQL Server.

An example of such an import is included in the Framework Excel file. Connection Record templates are shown in the Framework file, for import via a Connection (LinkType "mppImport") and via PowerQuery (LinkType "mppQuery").

Build/Update and Refresh of Connection records of LinkTypes mppImport and mppQuery are initiated by buttons for Build+Update DATE Links and Refresh DATES. Function of those Buttons for DATES is identical to the function of the Build+Update DATA Links and Refresh DATA buttons. The Buttons for DATA and for DATES are separated so that Dates can be updated separately from Data.

First, work on Dates is frequently handled separately from work on Materials and Expenses. They will come together ultimately, but you can work on the schedule and Dates while the logistics files are in use, and update the dates and add new materials when the schedule sequences, durations, dependencies and lead times are understood. Material updates usually occur much more frequently than date updates. Second, you need to refresh Dates before you refresh Data for materials et al, so that the materials logistics unambiguously are based on the dates you intend. For that reason, Dates and Data must be refreshed sequentially, not simultaneously.

Dates are imported into Excel as a Table, to be used before JOIN operations. You import the mpp Worksheet as a whole, e.g. [Task_Table1$], so a Shadow Range for the mpp exported worksheet is not needed. Calculations using these dates may occur in other Source Data tables, such as to calculate PurchDate based on material lead time; PayDate based on purchase or delivery date, material considerations, and accounting rules; and dates are usually a factor in applying Capitalization accounting rules to BOM elements. Source data imported into Excel as a Structured Table, such as this date file, may have calculations added and they will dynamically expand or contract to fit to rows added or deleted on refresh if you place a "proto-record" row at the top of the table containing column formulas and blank or default data field values; of course provide a means to exclude that row from JOINs or Pivots - usually key field values calculated from blank data fields in this "proto-record" take care of this for you automatically. The PowerOpI Framework file shows an example.

You will most likely use the Imported table as the basis for a tblDates to do date aliasing, and then use tblDates in JOINs. If you do wish to directly use the imported table in database JOINs, place the Table Update "SelectionChange" event-handler shown, onto the code page of the Worksheet Tab containing the import Table, and re-name the imported Table appropriately. A Shadow Range will be created for the imported Table and keep itself updated as the Table is refreshed to import the worksheet exported from MS Project, so the imported table will be visible to be used for JOINS in the database. As with all Excel Data Source tables, you use Microsoft.ACE.OLEDB.XX.0 with OPENROWSET or a Linked Server to access the table in SQL Server; and would create a Linked Table to use it in MS Access.

Corporate Finance is often not clear enough on accounting rules for project material, and the Program Manager needs to resolve several issues that depend on dates. There's often more clarity for production material than for development material with its more-situational dependencies. Resolution of these items will be handled in the tblDates and tblBOM tables discussed below.

• Usually a large portion of material needed must be purchased in a quarter prior to its need, to account for lead time, especially if need is near that start of a quarter. For planning, does Finance want the date of the Purchase Order, or of material commit (i.e., un-returnable), or of payment?
• PurchDate (PO Date) is earlier than fabrication start date by quoted Lead Time for material (plus fab and setup time). PayDate is dictated by purchase terms; for use of standard material (e.g. electronic components) may be ARO, typically 30 days after delivery. However, vendor fab using custom material (e.g. mechanical materials like metal, plastic, ceramic) usually requires payment concurrent with the Purchase Order. Finally, material used in executed fab steps is Committed, ultimately requiring payment even if the order for the item using it is canceled - so the Commit date may be a proper planning date.
• Which of those dates are needed for PO approval by Finance? How is a the program Budget vs. Actual metric determined and tracked by Finance? The PM must determine the dates, and have agreement with Finance on which date is used for which purpose in project financial planning, execution, and reporting. The tblDates table is where the PM calculates these dates.
• Similarly, rules for capitalization vs. expense accounting are usually not quite as clear as intended, so the PM needs to identify cases and get agreement with Finance. These also have some date dependencies.
• First, consider fabricated material for which the program is directly responsible for design and fab planning and execution: Early prototype units are almost certainly Expense items. Later in development, Pre-Prototype or Pilot builds may provide equipment such as SQA systems for Software release testing and later regression test, both prior to release and then well into production. If that equipment is close to production, it could be Capitalized even though it is not yet formally released to production. Even then, though, fab material near release that is used in compliance systems under stress, is probably Expensed.
• Second, consider purchased items. Such items over a capital threshold, maybe $1,500, and with an expected lifetime, are usually Capitalized. But normally-Cap items used in stressed systems, say servers in an environmental-test system, would be expensed. Are capitalize-able elements bought for early prototype lab use capitalized or expensed? And items below the cap threshold, bought in bulk for system integration, such as cables that may aggregate significant expense above cap limits - is capitalization determined individually, or in bulk? Is the whole cost of an integrated system capitalized, or are bulk items excluded?
• These Capitalization rules must be clarified with Finance for planning and execution between Program Management and Finance, and represented in the Program Plan, probably using the Tables mentioned below, for Program execution. The result is going to show up as Capital and Expense financial amounts, tied to dates and important to management of the Program and to its participating organizations.
• An article on how to use element aliasing in Source Data Tables managing dates and quantities in this tool will be provided soon.

Without a doubt, the project GANTT plan in Microsoft Project is planning and tracking all the major project items - elements in development including design work, process steps, integration; and tasks among many groups - Engineering, OS, Management, Diags, Supply Chain, Operations, CM, Marketing, Support... But there are probably many, many more items needed by the project - purchased items. Where a development project may have a dozen or two key items that it is concentrating effort on, there are probably dozens if not hundreds of items needed for use with those items to build, test, integrate, certify, validate... This could include items like cables (!! - nemesis of many efforts), power supplies, racks, power strips, NICs, Storage disks and Flash, power cords, rack brackets... including some items that may be used internally but never become part of the released end-product.

Need-by dates for these items are usually tied to start of tasks like "Build", "Integrate" or "Test" steps. Each item may have many part number variants (cables! length, connector, speed) or capacity, pins, frequency; and all have different lead times... It's inconvenient to put so many of these items into a GANTT, where they add little value. You're going to work backwards from Need Date to determine PurchDate so material availability won't become a gating item in the GANTT. The few items that are, you can put in the GANTT; most of them won't so work backward from Need Date: PurchDate is Need Date minus Lead Time; and plan them in Excel. Also, we want to limit editing of the GANTT to Program Management, but to allow Supply Chain personnel to update Excel Source Data tables to cover purchased material. So a mechanism is described here which allows purchased items to be scheduled in the Excel tool where logistics are managed and where those dates are really useful.

We're already importing the MS Project date export, which provides the need-by task dates. Purchase lead time and payment terms can be captured for each item into the tblElement table containing such info for each item built or used in the project - quotes usually include purchase price, purchase lead time, and payment terms like net 30 ARO, or Payment at time of PO. With that information we can calculate item PurchDates and PayDates. These would be determined for purchased items, and may be applied similarly to key material for items planned in the GANTT (like new silicon parts on limited allocation; crystals, connectors), and also PayDates for both purchased and GANTT-planned items.

The project GANTT plan is imported in the Framework example, into Structured Table tblP0001_Rollup, and includes tasks with their Start_Date and Finish_Dates. Tasks that are going to be JOINed with Logistics data in other tables, have had Keys assigned that are like Europa:P2-B:AtEng and those Keys are included in the imported tblP0001_Rollup.

A new table, tblDates, is created to act as a "shell" to fetch dates from tblP0001_Rollup and to create dates in Excel that are not included in the GANTT. This allows us to create records to contain dates for tasks not in the GANTT, and for items not scheduled in the GANTT at all, tying those created dates to relevant dates that ARE included in the GANTT. This is called "aliasing". Here's an piece of a tblDates:

Aliasing Dates in tblDates.

The red boxes illustrate the fields used to create the Key values. The purple boxes illustrate how date aliasing is accomplished. Each record identifies an item daElement, its Revision daRev and a task daTask which together are associated with dates daStart_Date and daFinish_Date along with fields that can be calculated or looked up from them, determining Fiscal Year daFY, Fiscal Quarter daFQ, Lead Time daLeadTime and parameters determining Expense and Capitalization. From the fields identifying daElement, daRev and daTask, Key fields daSchedItemRevKey and daSchedItemRevTaskKey are created, to allow JOINing of these Date records with Logistics records from other tables tblAlloc, tblBOM, and tblElements. The Keys allow JOINing of all date records element-rev by element-rev, or retrieval of dates for individual tasks.

The dates themselves are looked up from tblP0001_Rollup like this:
=INT(INDEX(tblP0001_Rollup,
MATCH([@daUseItem] & ":" & [@daRev] & ":" & [@daUseMilestone],tblP0001_Rollup[TaskMilestone],0),
MATCH("Start_Date",tblP0001_Rollup[#Headers],0)
))

Notice that this expression uses fields daUseItem, daRev and daUseMilestone instead of daElement, daRev and daTask upon which the lookup Keys are based. This allows us to alias in dates from a different Element:Rev:Task than the Keys address, and in particular to substitute dates or to create dates for items or tasks that are not available in tblP0001_Rollup. (In this example we assume we are aliasing dates from the same Rev of the element; otherwise we could have created a field "daUseRev" to further specify a aliasing from a different Rev.)

For items and tasks that are available in tblP0001_Rollup and that we want to use as scheduled in that table, the values of the "Use" fields are set to specify date fetch without aliasing:
daUseItem =[@daElement],
=[@daRev] is used without aliasing; otherwise could have created and used =[@daUseRev] and
daUseMilestone =[@daTask]
(or text values could be used duplicating those fields instead of expressions).

But in records for which we want to return PayDate or PurchDate (or any other task we wish to alias, or to create a Task that isn't available in tblP0001_Rollup), the values of the "Use" fields are set to specify the Element, Rev and Task for which dates should be returned. This shown by the purple boxes in the image.

The next image shows an example for which we created tasks and dates for a purchased item, Cable-QSFP28-3M, that is not scheduled in the GANTT at all. The boxes show that the logic is identical, but field values control where the dates are taken from.

In this case, dates of "PurchDate" and "PayDate" are aliased to fields specifying Europa:P2-A:AtEng. The "daStart_Date" field also subtracts the value of field "daLeadTime", picked up from tblElements, to allow for material purchase Lead Time. Note that the value of "daStart_Date" is about a month earlier than the date for "AtEng", consistent with subtracting the 28-day "daLeadTime":

Picking up StartDate, as Need Date for an item, from aliased task and, in red, subtracting LeadTime, from tblElements, to determine PurchDate:
=INT(INDEX(tblP0001_Rollup,
MATCH([@daUseItem] & ":" & [@daRev] & ":" & [@daUseMilestone],tblP0001_Rollup[TaskMilestone],0),
MATCH("Start_Date",tblP0001_Rollup[#Headers],0)
))
-IF([@daTask]="MatlPurch",[@daLeadTime],0)

where field daLeadTime is retrieved from tblElements as:
=INDEX(tblElements,
MATCH([@daElement],tblElements[emElement],0),
MATCH("emLeadTime",tblElements[#Headers],0)
)

A Path to a file used in a QUERY is a fully-qualified path, including the Server's fileshare name (like \\DBSOURCE\ or \\LOCALHOST\) if it's accessed over the local fileshare network, or the directory root (like C:\) if it's local to the server running the QUERY. Following the network root or directory root, the path through the filesystem, then followed by the data source filename. For example:
C:\Level1\Level2\filename.accdb or
\\DBSOURCE\users\username\Documents\Level1\Level2\filename.accdb or
\\DBSOURCE\Documents\Level1\Level2\filename.accdb (depending on what directory is shared to the network).

A file accessed over the network must be in a directory on the fileshare server, that has been shared such that the QUERY can access it - by user name if using Windows credentials, or by group membership, or by loging name and password. Log in to the source server if needed, before executing the QUERY.

Client and server firewalls must enable Windows File Sharing (SMB). If SQL Server is in the path, firewalls must be configured to enable that: Configure the Windows Firewall to Allow SQL Server Access on docs.microsoft.com.

The data source Server may have several directories shared. To further the above examples, directories Level1 and Level2 could have been shared.

In that case, the target data source file could be accessed as
\\DBSOURCE\Level2\filename.accdb, or as
\\DBSOURCE\Level1\Level2\filename.accdb .
Notice that the levels of the server's directory path below the directory actually shared are excluded when specifying the path to the shared directory.

Whether one of those paths in the example will work, is up to SMB filesharing and might not work consistently. I suggest trying the target address in the Windows File Explorer to verify its functionality. Paste the target address into the address box near the top of the File Explorer pane, and Enter. If the path entered contains the path but not the target filename, the directory will open for view in the File Explorer pane if the path entered is functional. If the path includes the target filename, then that file will open if the path is functional.

You may have to try several path constructions to determine which will work; then the Common Configuration Variables can be adjusted to use that path, the Connections can be re-built and you can try the connection.

---

SQL Server From Remote Clients

Notes on setup of SQL Server configuration and security to allow QUERYs from a client located on a computer separate from the computer hosting the SQL Server instance can be found at SQL Server Database Security.