Excel macro for consolidating workbooks

Getting Started with Power Query Power Query is available as a free download for anyone who has Office 2010 or 2013 Professional Plus here.

Put simply, it improves on the functionality already present in Excel’s Data tab in two ways: Examples Let’s see an example of how Power Query can help you when you need to combine data from multiple Excel workbooks into a single table.

Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. More than 100 people have emailed various interesting (and often very difficult) problems.

This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.

This is just to double-check that you are familiar with all the topics covered previously.

Our courses run from 9.30 am to approximately 4.3opm.

This advanced Microsoft Excel training course will teach you to use the advanced features of Excel in any version to their full extent.

By the end of this course, you will be able to report on data in a list using Pivot Tables, edit and analyse complex data using advanced features such as Scenarios, Goal Seek and Solver, incorporate built-in functions into a worksheet, work with controls and record macros.

In certain circumstances you might want to differentiate between a Pivot Table and its Pivot Cache.You can imagine therefore what the difference might be between refreshing every cache in the Work Book, compared to refreshing every Pivot Table in the Work Book. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).Sub Update Connection() Dim Server Name As String Dim Server Name Raw As String Dim Cube Name As String Dim Cube Name Raw As String Dim Connection String As String Server Name Raw = Active Workbook. Visible Slicer Items List(1) Server Name = Replace(Split(Server Name Raw, "[")(3), "]", "") Cube Name Raw = Active Workbook. Visible Slicer Items List(1) Cube Name = Replace(Split(Cube Name Raw, "[")(3), "]", "") If Cube Name = "All" Or Server Name = "All" Then Msg Box "Please Select One Cube and Server Name", vb OKOnly, "Slicer Info" Else Connection String = Get Connection String(Server Name, Cube Name) Update All Query Table Connections Connection String, Cube Name End If End Sub Function Get Connection String(Server Name As String, Cube Name As String) Dim result As String result = "OLEDB; Provider=MSOLAP.5; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2" '"OLEDB; Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; Use Procedure for Prepare=1; Auto Translate=True; Packet Size=4096; Use Encryption for Data=False; Tag with column collation when possible=False" Get Connection String = result End Function Function Get Connection String(Server Name As String, Cube Name As String) Dim result As String result = "OLEDB; Provider=MSOLAP.5; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2" Get Connection String = result End Function Sub Update All Query Table Connections(Connection String As String, Cube Name As String) Dim cn As Workbook Connection Dim oledb Cn As OLEDBConnection Dim Count As Integer, i As Integer Dim DBName As String DBName = "Initial Catalog=" Cube Name Count = 0 For Each cn In This Workbook. Name = "This Workbook Data Model" Then Exit For End If o Tmp = Split(cn. Connection, ";") For i = 0 To UBound(o Tmp) - 1 If In Str(1, o Tmp(i), DBName, vb Text Compare) = 1 Then Set oledb Cn = cn. Would you like to answer one of these unanswered questions instead?Consolidate According to the Position in an Excel Worksheet Identify Categories to Consolidate Excel Data Use Formulas to Consolidate Excel Data Access the Pivot Table function Community Q&A Microsoft Office Excel comes with several features for customizing tables and charts full of important data.The program also offers efficient ways to combine and summarize data from multiple files, also known as worksheets.Click on the From File button and then From Excel: A dialog will appear where you can connect to the first workbook, January Sales.xlsx, and once you have done that the Power Query Navigation pane will appear on the right-hand side of the screen.

You must have an account to comment. Please register or login here!