Перейти до основного матеріалу

Як перерахувати всі властивості файлу Excel на аркуші?

У цій статті я представляю код VBA, щоб перерахувати всі властивості активної книги на аркуші.

1. прес Alt + F11 клавіші, щоб увімкнути вікно Microsoft Visual Basic for Applications.

2. клацання Insert > Модуліта вставте код нижче в порожній модуль.

VBA: Перелічіть усі властивості

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

список документів, всі додані в 1

3. прес F5 клавіші для запуску коду, перед аркушем активного аркуша створено новий аркуш із назвою Workbook Properties (Властивості робочої книги), у якому перелічено всі властивості активної книги.
список документів, всі додані в 1


Інші операції (статті)

Вимкніть надбудови під час запуску Excel
Можливо, ви встановили деякі надбудови у своєму Microsoft Excel, але тепер вам потрібно вимкнути вказану надбудову під час запуску Excel. Як вирішити цю проблему? Метод у цій статті може вам допомогти.

Експортуйте та збережіть кожен аркуш як окрему нову книгу в Excel
Скажімо, ви хочете експортувати та зберегти один або кілька аркушів як нову книгу, як ви з цим справитесь? Зазвичай ви можете копіювати кожен аркуш і вставляти в нову книгу. Але тут ми пропонуємо вам кілька корисних прийомів, щоб вирішити це:

Код VBA для переліку всіх надбавок в Excel
В Excel ви можете додати або вставити деякі надбудови для кращої роботи з даними. Як ми знаємо, ми можемо перейти до вікна Параметри, щоб переглянути всі додані файли, але чи є спосіб перерахувати всі додані файли на аркуші?


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.

<br style="letter-spacing: 0.2px;">
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
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations