Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

How to list all Excel file’s properties in a sheet?

In this article, I introduce a VBA code to list all properties of the active workbook in a sheet.

1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below code to the blank module.

VBA: List all properties

Sub WBProperties()
'UpdatebyKutools20191031
    Dim xWB As Workbook
    Dim xF As Long
    Dim xStr As String
    Dim xWSh As Worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    xStr = "Workbook Properties"
    Set xWB = Application.ActiveWorkbook
    Set xWSh = xWB.Worksheets.Item(xStr)
    If Not xWSh Is Nothing Then
        xWSh.Delete
    End If
    Set xWSh = xWB.Worksheets.Add
    xWSh.Name = xStr
    xWSh.Range("A1").Value = "Property"
    xWSh.Range("B1").Value = "Value"
    With xWB
        For xF = 1 To .BuiltinDocumentProperties.Count
            xWSh.Range("A65536").End(xlUp).Offset(1, 0).Value = _
            .BuiltinDocumentProperties.Item(xF).Name
            xWSh.Range("A65536").End(xlUp).Offset(0, 1).Value = _
            .BuiltinDocumentProperties.Item(xF).Value
        Next xF
    End With
    Application.DisplayAlerts = True
End Sub

doc list all add in 1

3. Press F5 key to run the code, a new sheet named Workbook Properties has been created in the front of the active sheet, which lists all properties of the active workbook.
doc list all add in 1


Other Operations (Articles)

Disable add-ins at Excel startup
You may have installed some add-ins in your Microsoft Excel, but now you need to disable a specified add-in at Excel startup. How to solve this problem? Method in this article can help you.

Export and save each worksheet as separate new workbook in Excel
Let's say you want to export and save one or several worksheets as a new workbook, how will you deal with it? Normally you may copy each worksheet and paste to new workbook. But here we bring you some handy tricks to deal with it:

A VBA code to list all add ins in Excel
In Excel, you may add or insert some add ins for better dealing with data. As we know, we can go to the Options window to view all add ins, but is there any way to list all add ins in a sheet?



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maxxis · 6 months ago
    Hello,

    First off, I do not know VBA, but have determined after hours of searching, it is the only solution to my problem.

    Background:
    I have a workbook with a large number of Power Query-created "Merge"-type Queries and Queries that pull data from flat files; some, but not all are added to the Data Model/Power Pivot.

    The composition of queries may change over time.

    Within a subset of the queries, I have disabled refreshing with "Refresh All" to reduce the already lengthy time it takes to refresh all queries.

    There are a large number of worksheets that contain Pivots off the Data Model, whereas the others contain a subset of tables that are populated from the queries via "Load To"

    I am using O365 Excel

    The example code below is based on hours of research. I don't know if it's of value in achieving my objectives below, but I'm including it in the event it sparks a solution.

    Objectives:
    I would like an "index" worksheet created containing a dynamic table (or just listed out in cells that I can convert to a table manually) that keeps an up-to-date list of workbook "Query-" queries and their respective properties below.

    The dynamic (e.g., reflect added/changed/deleted queries) table should contain the following columns with the following query/connection properties as fields:
    -----------------------------------------------------
    "Name", "Description", "RefreshWithRefreshAll", "InModel", "Type"

    The following example is what I was able to cobble together from the interwebs which got me some of the way there, but it only allows me to see it in the VBA Immediate window, and I don't know how to get it into a table on the worksheet:

    Sub ListConnections()

    Dim cn As WorkbookConnection

    Debug.Print "Name", "|", "Description", "|", "|", "RefreshWithRefreshAll", "|", "InModel", "|", "Type"


    For Each cn In ThisWorkbook.Connections
    Debug.Print cn.Name, "|", cn.Description, "|", cn.RefreshWithRefreshAll, "|", cn.InModel, "|", cn.Type

    Next
    End Sub
    -----------------------------------------------------

    Currently, I can only get it to output in the Immediate Window:
    -----------------------------------------------------
    Name|Description||RefreshWithRefreshAll|InModel|Type
    ModelConnection_ExternalData_1|DataModel|True|True|7
    ModelConnection_ExternalData_11|DataModel|True|True|7
    Query-AllADMembers(CX)|Connectiontothe'AllADMembers(CX)'queryintheworkbook.|True|True|1
    Query-AllAzureADUsers-Disabled|Connectiontothe'AllAzureADUsers-Disabled'queryintheworkbook.|True|False|1
    Query-AllAzureADUsers-Enabled|Connectiontothe'AllAzureADUsers-Enabled'queryintheworkbook.|True|False|1
    Query-AllAzureADUsersOnly|Connectiontothe'AllAzureADUsersOnly'queryintheworkbook.|True|False|1
    ...

    Query-UsersinDA,NotinApp-BulkDEL*|Connectiontothe'UsersinDA,NotinApp-BulkDEL*'queryintheworkbook.|False|True|1
    ThisWorkbookDataModel|DataModel|True|True|7
    -----------------------------------------------------
    Note 1: I used the convention of adding an asterisk to the query name to visually see which queries I disabled refreshing with Refresh All.
    Note 2: Query names with (CNX) refer to the base queries to the flat files upon which the other merge queries are based.
    Note 3: The "Last Refresh" field in the table would be the Date/Time of the Last Refresh of the query. I was hoping it could be obtained as a property of the query, since this timestamp is shown when hovering over the query in the query pane, e.g.



    Based on my searches, it does not appear that this is obtainable for some reason, but if not, I found other references to adding it as a calculated value using timestamps of macro executed refreshes.

    Example: I found many references to adding a button to have a macro refresh one or refresh all queries. My thought is that one could create code to dynamically enumerate the names from the code above Query-[Query Name], then have the code execute a refresh of each (CNX) query above first, then execute the rest individually to capture the execution and/or finish timestamp. Since there is a natural sequence that Excel uses when doing a refresh all, executing queries manually may trigger the natural sequence multiple times making the overall refresh times unbearable. Looking for the most efficient solution here.

    Here is some code that I thought would be useful to tackle this, but again, it only shows output in the Immediate Window when executed.
    -----------------------------------------------------
    Dim TStart As Date
    Dim TEnd As Date
    Dim cn As WorkbookConnection

    For Each cn In ThisWorkbook.Connections

    If Left(cn, 13) = "Power Query -" Then
    Debug.Print cn
    TStart = Now
    cn.Refresh
    TEnd = Now
    Debug.Print CStr(DateDiff("s", TStart, TEnd)) + " Seconds"
    Debug.Print ""

    End If
    Next cn
    End Sub
    -----------------------------------------------------
    Thanks in advance!

    M