# VBA & MS Office automation

A series of 10 article(s), in reading order.

<!--
  canonical-url: https://www.avonture.be/blog/vba-excel-ribbon
  published:     2023-12-10
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# MS Office - How to create a ribbon in Excel

> Step-by-step guide on creating a custom VBA Excel Ribbon. Use XML and VBA to add your own tabs, groups, buttons, and powerful features to your Excel workbooks.

---
![MS Office - How to create a ribbon in Excel](/img/v2/ribbon.webp)

**TL;DR**

This article walks through building a custom Excel ribbon using the free `CustomOfficeUIEditor` tool and hand-written XML (the `customUI14.xml` manifest): defining tabs, groups, and controls like buttons and edit boxes, wiring their `onAction`/`onChange` events to VBA callback subroutines, and finding standard `imageMso` icon IDs to use on buttons.

In this post, we'll learn how to create a ribbon (i.e. a toolbar) in Microsoft Excel.

We'll create our custom ribbon for an Excel file, save the ribbon in the file so our users will have a nice and intuitive interface to work with our worksheet.

*A ribbon is only useful if its buttons do something interesting. For a concrete example, [MS Excel - Connect to a SQL Server database, run a query and get the results](/blog/vba-excel-sql-server-part-2) gives you the VBA behind a "Refresh data" button.*

## What we're going to build

Here is the end result: our own tab, in Excel, with a button and an input field.

![Smiley\_and\_edit](./images/Smiley_and_edit.webp)

The button is wired to a VBA subroutine, so clicking it runs your code:

![Button clicked](./images/Button_clicked.webp)

The whole thing is about fifteen lines of XML stored inside the workbook. Anyone opening your `.xlsm` file gets the ribbon; there's nothing to install on their machine.

## Why it works

* An `.xlsx` / `.xlsm` file is not a binary blob: it's a ZIP archive with files and folders inside. You can open one with 7-Zip and look around.
* Adding a ribbon means adding one file to that archive: a manifest called `customUI14.xml`, which describes the tabs, groups and controls in XML.
* When MS Office opens the workbook, it reads that manifest, draws the ribbon and, on each click, calls the VBA subroutine named in the control's `onAction` attribute. Your macro and your interface are thus stored in the same single file.

## Download the editor for free

To be able to "easily" *(in a not WYSIWYG interface)* create a ribbon in MS Office, I'm using a very old tool called `CustomOfficeUIEditor`. *Until now, I've not found any other free tool for doing this.*

The tool can be downloaded from <https://bettersolutions.com/vba/ribbon/custom-ui-editor-download.htm>.

`CustomOfficeUIEditor` is an external tool (outside MS Office) which can open an Office file and add the needed files to create a ribbon. It's possible to do this without an editor, for instance by using a software like 7-Zip since a `.xlsx` / `.xlsm` file is an archive with files and folders.

For this exercise; please extract all files and folders to your `C:\tmp\ribbon` folder (create it).

## Create an empty file

As a support for this blog post, please create a new, empty, file in Excel called `Ribbon.xlsm`  in your `C:\tmp\ribbon` folder (or elsewhere).

![Excel Ribbon.xlsm](./images/Excel_empty_file.webp)

> **Just creates an empty file:**
>
> Right now, we don't need to do anything other than File - New - Save as - FileType - Excel Macro-Enabled Workbook.

## Add a ribbon in MS Office

The `CustomOfficeUIEditor` tool isn't a user-friendly tool: you'll need to type yourself the XML of the ribbon. That XML is also called *the manifest file*.

By default, a standard MS Office document doesn't contain any ribbon so, when you open such files with the `CustomOfficeUIEditor` tool, you'll get an empty document.

*Outlook is the exception: there, no XML is needed at all since the built-in `Customize the Ribbon` dialog can host a macro button, as done in [Microsoft Outlook - VBA - Save emails as PDF](/blog/outlook-vba-pdf).*

Please start `CustomOfficeUIEditor` and open your `Ribbon.xlsm` file:

![UI - Open Ribbon.xlsm](./images/UI_open.webp)

> **Make sure to close it first if still open in Excel:**
>
> When using the editor, the file itself can't be opened in Excel! Imagine the following situation: the workbook is open both in the editor and in Excel. You make a lot of changes in Excel, switch to the editor and change the manifest. By saving the file in the editor, you'll lose every change made in Excel so... be careful.

The first time, you'll need to add a new ribbon: click on the `Insert` menu and select `Office 2010 Custom UI Part`.

![Insert](./images/UI_Editor_Insert.webp)

You'll then obtain a screen like this:

![Office 2010 Custom UI Part](./images/UI_Editor_Insert_UI14.webp)

When `customUI14.xml` is selected (like illustrated above), click somewhere in the right pane and start to code your XML content.

Now, in the right pane, just copy/paste the XML source below:

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" insertAfterMso="TabView" label="Tab">
                <group id="customGroup" label="Group">
                    <button
                        id="customButton"
                        label="Button"
                        imageMso="HappyFace"
                        size="large"
                        onAction="OnButtonClicked" />
                    <editBox
                        id="customEditBox"
                        label="Edit Box"
                        onChange="OnEditBoxTextChanged" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
```

![Manifest added](./images/UI_Editor_Added_UI14.webp)

Before saving your file, be sure to click on the `Validate` button so a check is made to verify the quality of the content.

![Check](./images/UI_Editor_Check.webp)

Since the ribbon is correct, save the file and open it in Excel. If everything goes fine, you'll obtain this:

![Sample](./images/UI_Editor_Sample.webp)

> **Your ribbon has been created:**
>
> Congratulations, you've added a ribbon to your workbook. It was not too hard I think.

## Under the Hood — the manifest, tag by tag (skip this if you just want a ribbon)

Time to understand what has happened...

### The root customUI node and the namespace

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
```

The manifest is an XML content and should be valid. You'll need to define your content into a `<customUI>` node (mandatory) and you'll need to specify the `xmlns` (for `namespace`) attribute (mandatory).

The `namespace` file will define what attributes exist, are mandatory or not, for each node type. The referred URL is the `Document Type Definition` (aka *DTD*).

For instance, when the user will click on the button of your ribbon, you'll wish to be able to capture the click and start a subroutine that you've coded in VBA. The "on click event" should be defined in the `onAction` attribute as defined in the DTD. *Microsoft maintains the documentation here: <https://msdn.microsoft.com/en-us/library/dd909370(v=office.12).aspx>.*

So, if you know that the attribute is `onAction` you can then add your own subroutine by typing something like `onAction="OnButtonClicked"`.

The job of the DTD is to make sure that the syntax of your manifest is correct; therefore, the attribute `xmlns="http://schemas.microsoft.com/office/2009/07/customui"` is indeed mandatory.

### Define the ribbon

The ribbon should be defined inside the `<ribbon>` node but not immediately since a ribbon is, in fact, always defined in a tab.

Below we can see the standard ribbon of Excel and you can see a lot of tabs: `File`, `Insert`, `Page layout`, `Formulas`, `Data`, `Review`, `View`, ...

![Tabs](./images/Tabs.webp)

Each tab comes with features (buttons, checkboxes, ...). For the `Insert` tab, we've first a group of three buttons into a group called `Tables`:

![Insert - Group Tables](./images/Tab_Insert_Tables.webp)

So, a ribbon should be:

1. Define it in a tab (its own tab or an existing one)
2. Add features in one or more groups (own groups or existing ones)

Below, our manifest now, with the definition of the ribbon.

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <!-- highlight-next-line -->
    <ribbon>
        <!-- highlight-next-line -->
        <tabs>
            <!-- highlight-next-line -->
            <tab id="customTab" insertAfterMso="TabView" label="Tab">
                <!-- highlight-next-line -->
                <group id="customGroup" label="Group">
                    <!-- highlight-next-line -->
                    YOUR FEATURES HERE
                    <!-- highlight-next-line -->
                </group>
                <!-- highlight-next-line -->
            </tab>
            <!-- highlight-next-line -->
        </tabs>
        <!-- highlight-next-line -->
    </ribbon>
</customUI>
```

### Define the tab

You'll need to give an identifier to the tab (in the attribute called `id`): your own code for a new tab or the id of an existing tab.

The standard names are:

* `TabHome`
* `TabInsert`
* `TabPageLayoutExcel`
* `TabFormulas`
* `TabData`
* `TabReview`
* `TabView`
* `TabDeveloper`

So, if you want to add a button to the Home tab, just use `TabHome` as value for the `id`, f.i.,

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <!-- highlight-next-line -->
            <tab id="TabHome">
                <!-- highlight-next-line -->
                ADD A BUTTON TO TabHome
            <!-- highlight-next-line -->
            </tab>
        </tabs>
    </ribbon>
</customUI>
```

The XML below will, in one line,

1. Create a new tab since the id `customTab` is not an existing id,
2. Put the new tab after the existing `View` tab (use `insertBeforeMso` to add the tab before),
3. And give it `Tab` as caption.

**customUI14.xml**

```xml
<tab id="customTab" insertAfterMso="TabView" label="Tab">
```

Here is our added tab:

![Add\_new\_tab](./images/Add_new_tab.webp)

### Implement our ribbon

Inside the `<tab>` declaration, you need to define at least one `<group>`. And here too, you'll to define the identifier: an existing one for, f.i., adding a button in an existing group or a new one to create a new group.

The XML below will:

1. Create a new group since the id is a new one (`customGroup`),
2. And give it `Group` as name.

**customUI14.xml**

```xml
<group id="customGroup" label="Group">
```

Our tab with its group:

![Just a group defined](./images/Group_is_required.webp)

As you can see here above, just adding a group isn't enough, you'll need to define which features (buttons, checkboxes, ...) should be added in the group.

### Add features

The XML here below will add two things: a button and an edit box.

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" insertAfterMso="TabView" label="Tab">
                <group id="customGroup" label="Group">
                    <!-- highlight-next-line -->
                    <button
                    <!-- highlight-next-line -->
                    id="customButton"
                    <!-- highlight-next-line -->
                    label="Button"
                    <!-- highlight-next-line -->
                    imageMso="HappyFace"
                    <!-- highlight-next-line -->
                    size="large"
                    <!-- highlight-next-line -->
                    onAction="OnButtonClicked" />
                    <!-- highlight-next-line -->
                    <editBox
                    <!-- highlight-next-line -->
                    id="customEditBox"
                    <!-- highlight-next-line -->
                    label="Edit Box"
                    <!-- highlight-next-line -->
                    onChange="OnEditBoxTextChanged" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
```

The XML below will create a button

1. With an `id` initialized to `customButton`,
2. Its label (the text that is displayed under the button) set to `Button`,
3. Use the `HappyFace` standard image (`imageMso` is, indeed, the way to reuse a standard image while `image` allows you to define your own),
4. The size of the button will be `large` (a big button),
5. The action assigned will be the VBA function called `OnButtonClicked`.

**customUI14.xml**

```xml
<button
    id="customButton"
    label="Button"
    imageMso="HappyFace"
    size="large"
    onAction="OnButtonClicked" />
```

This will result in this:

![Smiley](./images/Smiley.webp)

`OnButtonClicked` is the VBA callback: to make it work, add a module in your Excel file, create a subroutine like this:

```vbnet
Public Sub OnButtonClicked(control As IRibbonControl)
    MsgBox "You've clicked on the button", vbInformation
End Sub
```

To do this, in MS Excel, please press `ALT`-`F11`, in the **Project explorer**, right-click on the **VBAProject (Ribbon.xlsm)** entry (1), then select **Insert** (2) and **Module** (3).

![Insert a module](./images/VBE_insert_module.webp)

A `Module1` will be added and, in the right pane, please copy/paste the code given here above and save the file.

![Add code](./images/VBE_add_code.webp)

Now, you can close the *Microsoft Visual Basic for Application* window, go back to the well-known Excel window and click on the smiley button: you'll get the message box shown in the second screenshot at the top of this article.

And we can add other features, like an edit box:

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" insertAfterMso="TabView" label="Tab">
                <group id="customGroup" label="Group">
                    <button
                        id="customButton"
                        label="Button"
                        imageMso="HappyFace"
                        size="large"
                        onAction="OnButtonClicked" />
                    <!-- highlight-next-line -->
                    <editBox
                    <!-- highlight-next-line -->
                    id="customEditBox"
                    <!-- highlight-next-line -->
                    label="Edit Box"
                    <!-- highlight-next-line -->
                    onChange="OnEditBoxTextChanged" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
```

Save, reopen the file, and your tab now looks like the very first screenshot of this article.

The list of properties depends on the type: for a button, we have an `onAction` attribute while it's an `onChange` for an editBox.

## List of objects

As exhaustively defined in the `ribbon's namespace`, here is the list of valid objects:

* `box`,
* `button`,
* `buttonGroup`,
* `checkBox`,
* `control`,
* `comboBox`,
* `dropDown`,
* `dynamicMenu`,
* `editBox`,
* `gallery`,
* `labelControl`,
* `menu`,
* `splitButton` and
* `toggleButton`

**Be careful**: XML is case sensitive, `editBox` is the only valid syntax, `editbox` or `EditBox` aren't.

Each object comes with its own attributes, some are mandatory while the others are optional.

## Find images

By adding a button f.i., you'll assign an image to it.

You'll most probably use an existing, standard, image. This can be done by using the `imageMso` attribute and telling Office which image to use; f.i.  `AddFolderToFavorites`:

![Favorites](./images/Favorites.webp)

The manifest is this one:

**customUI14.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabHome" visible="false" />
            <tab id="customTab" insertAfterMso="TabView" label="Tab">
                <group id="customGroup" label="Group">
                    <button
                        id="customButton"
                        label="Button"
                    <!-- highlight-next-line -->
                    imageMso="AddFolderToFavorites"
                    size="large"
                    onAction="OnButtonClicked" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
```

But ... **how to retrieve the list of images?**

Microsoft maintains Excel files with the list of existing IDs that can be used as icons in our ribbon. The Office Fluent User Interface Control Identifiers are maintained on the [OfficeDev GitHub repository](https://github.com/OfficeDev/office-fluent-ui-command-identifiers) (current versions for all Office releases). You'll get Excel files, one file per application (Access, Excel, Outlook, ...).

This will give the list of existing IDs in plain text but you'll not see the associated images.

You can download all images (as PNG) for MS Office from the [ImageMso GitHub repository](https://github.com/christianarielli/ImageMso) (8,899 icons with a built-in Excel Add-In to browse them).

## Assign callbacks

This term means: which code (VBA in this case) should be fired when an event is raised.

When the user clicks on a button of the ribbon which subroutine should be called?

The XML code below assigns the `OnButtonClicked` subroutine to the `onAction` event of the button.

**customUI14.xml**

```xml
<button
    id="customButton"
    label="Button"
    imageMso="AddFolderToFavorites"
    size="large"
    onAction="OnButtonClicked" />
```

So, if we wish to catch that event, we'll need to add a public subroutine in our Excel file, that subroutine can be placed in any module, should be public and should have `OnButtonClicked` as name.

But... depending on the callbacks (click, change, toggle state, change, ...), the definition of the subroutine is not the same.

For a button, the VBA should be something like:

```vbnet
Public Sub OnButtonClicked(control As IRibbonControl)
    ' YOUR CODE
End Sub
```

For an edit box and the `onChange` event, the callback is different:

**customUI14.xml**

```xml
<editBox
    id="customEditBox"
    label="Edit Box"
    onChange="OnEditBoxTextChanged" />
```

```vbnet
Public Sub OnEditBoxTextChanged(control As IRibbonControl, sText As String)
    ' YOUR CODE
End Sub
```

The declaration of callbacks can be found on the official site:
[How can I determine the correct signatures for each callback procedure?](https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa722523\(v=office.12\)#how-can-i-determine-the-correct-signatures-for-each-callback-procedure). Pay attention to the `Signatures` columns; you need to look for `VBA`.

## Conclusion

A workbook that used to be *"open the VBA editor, find the macro, run it"* now has a tab of its own with real buttons; and it's all stored in the `.xlsm` file, so it follows the workbook wherever it goes. The two things to remember: the manifest is `customUI14.xml` inside the archive, and each control points to a public VBA subroutine through `onAction` (or `onChange`).

Once your ribbon is in place, a common next step is to feed one of its controls (like a dropdown) with data coming straight from your worksheet — see [MS Office - Load dropdown from Excel's range](/blog/vba-excel-ribbon-load). And to give your buttons something worth clicking, [MS Excel - Connect to a SQL Server database](/blog/vba-excel-sql-server-part-2) is a good place to continue.


---

<!--
  canonical-url: https://www.avonture.be/blog/vba-excel-call-soap-webservice
  published:     2024-02-21
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# MS Excel - How to call a SOAP web service

> Master calling SOAP web services from MS Excel using VBA. Get the code and a step-by-step tutorial to validate VAT numbers with the VIES service.

---
![MS Excel - How to call a SOAP web service](/img/v2/excel.webp)

**TL;DR**

This article shows a VBA skeleton for calling a SOAP web service from Excel, using the EU's VIES VAT number validation service as a concrete example: building an XML request file with placeholders, sending it via `MSXML2.ServerXMLHTTP60`, and opening the XML response as a new workbook. A Linux `curl`-based equivalent is also included for comparison.

Imagine you had to make a call to a SOAP web service in Excel? For example, to validate the VAT number you have been given before carrying out some processing.

You will call the URL that corresponds to the web service you want, but you will also need to pass a number of parameters in XML format so that the service knows what you want to do.

We'll learn, in this blog post, how to validate a European VAT number using the [VIES VAT number validation](https://ec.europa.eu/taxation_customs/vies/#/vat-validation) SOAP web service.

The VBA code we will see in this article can be used as a skeleton for your future development.

*That same VIES service is used as the running example in [Using Postman to play with API](/blog/postman), where the XML response is asserted node by node — a good way to understand a SOAP answer before writing the VBA that parses it.*

So, when calling a SOAP service, we must always prepare an XML message first. To do this, we create an .xml file somewhere on our hard drive.

## Create the xml message

Please create the file `C:\temp\checkVat.xml` with this content. This message (content) is expected by the VIES VAT number web service. In the file, we foresee two placeholders for our variables: `%COUNTRY%` and `%VATNUMBER%`. We'll see this later on.

**C:\temp\checkVat.xml**

```xml
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <urn:checkVat xmlns:urn="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
            <urn:countryCode>%COUNTRY%</urn:countryCode>
            <urn:vatNumber>%VATNUMBER%</urn:vatNumber>
        </urn:checkVat>
    </soapenv:Body>
</soapenv:Envelope>
```

## Create the Excel workbook

Now, please,

1. Create an empty Excel workbook,
2. Press `ALT`-`F11` to get the Visual Basic Editor (aka `vbe`).
3. Create a new module:
   ![Create a new module](./images/insert_module.webp)
4. Copy/Paste the VBA code from here below to it.
5. Check the path for the `InputXmlFile` constant. Make sure it's the same as where you've saved your `.xml` file earlier.
6. Click on the `Tools` menu then select `References` and add a reference to `Microsoft XML, v6.0`
   ![Tools - References](./images/tools_references.webp)
   ![Microsoft XML, v6.0](./images/microsoft_xml6.webp)
7. Finally, put the cursor anywhere in the `run` subroutine and press `F5` to run it.
   ![Activate run subroutine](./images/sub_run.webp)
8. After a few seconds, a new workbook will be created with the answer from the SOAP web service:
   ![SOAP answer](./images/soap_answer.webp)

```vbnet
Option Explicit

' URL to call
Const URL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"

' XML to send to the web service method
Const InputXmlFile = "C:\temp\checkVat.xml"

' *************************************************************
'
' Entry point
'
'    - Call the web service checkVAT method
'    - Upload XML data (country and VAT number)
'    - Get XML response
'    - Open the response as a workbook
'
' *************************************************************
Sub run()

    Dim sData As String
    Dim sResponseFileName As String

    ' Get the input manifest
    sData = openCheckVatXml(InputXmlFile)

    ' Consume the web service and get a filename with the response
    If (sData = "") Then
        MsgBox "Failure, the " & InputXmlFile & " file didn't exist", vbExclamation + vbOKOnly
        Exit Sub
    End If

    sResponseFileName = consumeWebService(URL, sData)

    ' Open the response as a workbook
    Call Application.Workbooks.OpenXML(Filename:=sResponseFileName)

End Sub

' *************************************************************
'
' Open the checkVat.xml input and replace variables
'
' *************************************************************
Private Function openCheckVatXml(ByVal sFileName As String) As String

    Dim sData As String

    sData = readFile(sFileName)

    If (sData <> "") Then
        sData = Replace(sData, "%COUNTRY%", "BE")
        sData = Replace(sData, "%VATNUMBER%", "0403170701") ' ENGIE Electrabel Belgique
    End If

    openCheckVatXml = sData

End Function

' *************************************************************
'
' Generic file reader. Return the content of the text file
'
' *************************************************************
Private Function readFile(ByVal sFileName As String) As String

    Dim objFso As Object
    Dim objFile As Object
    Dim sContent As String

    Set objFso = CreateObject("Scripting.FileSystemObject")

    If Not (objFso.FileExists(sFileName)) Then
        ' The file didn't exist
        readFile = ""
        Exit Function
    End If

    Set objFile = objFso.OpenTextFile(sFileName, 1)

    sContent = objFile.readAll

    objFile.Close

    Set objFile = Nothing
    Set objFso = Nothing

    readFile = sContent

End Function

' *************************************************************
'
' Return a filename with the response of the web service method
'
' *************************************************************
Private Function consumeWebService(ByVal sURL As String, ByVal sData As String) As String

    Dim xmlhttp As Object
    Dim sResponseFileName As String

    Set xmlhttp = New MSXML2.ServerXMLHTTP60  ' Requires Microsoft XML, v6.0

    xmlhttp.Open "POST", sURL, True
    xmlhttp.send sData
    xmlhttp.waitForResponse

    sResponseFileName = createXmlTempFile(xmlhttp.responseText)

    Set xmlhttp = Nothing

    consumeWebService = sResponseFileName

End Function

' *************************************************************
'
' Create a temporary file in the TEMP folder and write in that
' file the XML response received by the web service.
'
' Return the temporary filename as result of this function
'
' *************************************************************
Private Function createXmlTempFile(ByVal sContent As String) As String

    Dim objFso As Object
    Dim objFile As Object
    Dim objFolder As Object
    Dim sFileName As String

    Set objFso = CreateObject("Scripting.FileSystemObject")

    ' 2 = temporary folder
    Set objFolder = objFso.GetSpecialFolder(2)
    sFileName = objFolder & "\"
    Set objFolder = Nothing

    sFileName = sFileName & objFso.GetTempName()
    sFileName = Replace(sFileName, ".tmp", ".xml")

    Set objFile = objFso.CreateTextFile(sFileName)

    objFile.Write sContent

    objFile.Close

    Set objFile = Nothing
    Set objFso = Nothing

    createXmlTempFile = sFileName

End Function
```

### About placeholders

In our `C:\temp\checkVat.xml`, we've thus foreseen two placeholders.

You can see in the `openCheckVatXml` VBA subroutine how we'll replace them by some, example, values. Just adjust them to yours.

## A Linux command-line version

For those who can be interested, here is another way to achieve the same result but for a Linux console (or DOS if you've curl installed):

```bash
curl --silent http://ec.europa.eu/taxation_customs/vies/services/checkVatService   \
  -w \\n                                          \
  -H 'SOAPAction: "checkVat"'                        \
  -H "Content-Type: text/xml;charset=UTF-8"          \
  -d '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <urn:checkVat xmlns:urn="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
            <urn:countryCode>BE</urn:countryCode>
            <urn:vatNumber>0403170701</urn:vatNumber>
        </urn:checkVat>
    </soapenv:Body>
</soapenv:Envelope>'
```

> **Add `xmlstarlet` for a nice output:**
>
> See my [The xmlstarlet utility for Linux](/blog/linux-xmlstarlet) article.
>
> Add `| xmlstarlet format --indent-spaces 4` at the end of the previous command to pipe the output to `xmlstarlet` (see [The xmlstarlet utility for Linux](/blog/linux-xmlstarlet))

The answer will be:

```xml
<?xml version="1.0"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
    <env:Header/>
    <env:Body>
        <ns2:checkVatResponse xmlns:ns2="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
            <ns2:countryCode>BE</ns2:countryCode>
            <ns2:vatNumber>0403170701</ns2:vatNumber>
            <ns2:requestDate>2024-01-07+01:00</ns2:requestDate>
            <ns2:valid>true</ns2:valid>
            <ns2:name>SA ELECTRABEL</ns2:name>
            <ns2:address>Boulevard Simon Bolivar 36\n1000 Bruxelles</ns2:address>
        </ns2:checkVatResponse>
    </env:Body>
</env:Envelope>
```

Now, it's up to you to modify the VBA code and tailor it to your needs.

If you're designing the API side of things rather than just consuming it, see [API REST - How to write good APIs](/blog/php-api-tips) for general API design context.


---

<!--
  canonical-url: https://www.avonture.be/blog/msaccess-optimize
  published:     2024-03-09
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# How to optimize an existing MS Access database

> Speed up your slow Microsoft Access database with this step-by-step optimization guide. Learn to improve table structure, use indexes, optimize queries, and compact your database for better performance.

---
![How to optimize an existing MS Access database](/img/v2/msaccess.webp)

**TL;DR**

This is a comprehensive checklist for speeding up an existing MS Access database without re-engineering it: adding primary keys and indexes, using appropriate field types/sizes, preferring native SQL (`Is Null`, `Iif()`, `BETWEEN`) over VBA functions (`IsNull()`, `NZ()`, `Year()`) which bypass indexes, filtering with `WHERE` before `HAVING`, converting macros to VBA modules, compiling code, and compacting the database.

Several years ago, I had to intervene on an MS Access database that had been created by a person of good will but with little affinity for computers and optimization.

The result: a cumbersome and exasperatingly slow database.

In this article, resurrected from my personal archive, I'm going to present the different stages I went through.

> **Disclaimer:**
>
> This guide deliberately concerns **existing databases**; for that reason, I won't talk about how to correctly design tables, keep the number of fields reasonable, or draw a relational schema, ... The objective is therefore to improve performance without deep programming (no re-engineering) and without [migrating to, for instance, SQL Server](/blog/docker-mssql-server).
>
> *To know **which** fields are oversized before shrinking them, run the audit script from [VBS - Retrieve the list of fields in a MS Access Database](/blog/vbs-msaccess-get-fields): it reports, for every field, its declared size next to the longest value actually stored.*

## MS Access - Analyze Performance

MS Access 2016 provides a tool for analyzing the current database.

![MS Access - Analyze Performance](./images/access_analyze_performance.webp)

You can run the tool only for a selection like, f.i., only for queries.

## Do it yourself

### Tables

#### Add a primary key in each table

Depending on your data model, try to add a Primary key in each table.

For instance, if you've a Customer table, you'll probably have one field that will define, without any doubt, a given customer; a field like `CustomerID` (whatever its name).

Examine all your tables and if you've such unique field, define that field as the Primary key.

If possible, a primary key should be

* only one field (you can indeed define several fields to be the key),
* a number, not a text field,
* as small as possible (if you really need to use a text field, prefer a field with 5 characters f.i. and not 255)

Right-click on the table name and select `Design`.

![Open the table in design mode](./images/design.webp)

Select the field then click on the `Primary key` button.

![Add a primary key](./images/primarykey_add.webp)

Be careful: be sure to correctly identify the unique field; don't add a primary key to f.i. a customer name (since more than one customer can have such a name). A primary key will increase speed and data coherence in your table but will also prevent having a second record with the same key (so, if you choose the wrong field, you can have side effects).

#### Use indexes

Take a look at your queries: do you often make a filter on a specific field? You could have a very strong performance improvement by just adding an index for that field.

To do it, open your table in design mode by just right-clicking on the name of the table

![Open the table in design mode](./images/design.webp)

Then click on the `Indexes` button to show a small window and, there, add indexes on important fields.

![Add indexes](./images/index_add.webp)

Note: don't abuse it, don't *over-index*. Add indexes only on fields often used in query criteria since maintaining indexes requires CPU and thus has a cost on insert/update/delete statements.

#### Create relationships between tables if possible

When you're using, in a query, two tables or more having a link between them, MS Access optimization techniques suggest creating this link, also, in the relationships window i.e. at a global level.

To do this, once your database is opened, click on the `Database Tools` menu and choose `Relationships`.

Draw your relation, select carefully the join type (which can be also adjusted in a query) and save the relations.

![Create relationships between tables](./images/relationships.webp)

Example: link between two tables on the `ID` from a first table and the associated field, `tblOneID` in the second table. The arrow is drawn from `table1` to `table2` (it's the `join type`) and this means: "take all records of `table1` and, if the value is also known in `table2`, then return values."

Tip: always try to use an index for the relation and not "any field".

![Example of a relation between two tables](./images/relationships_sample.webp)

#### Use the right data type and the right size

In your tables, try to correctly choose the data type:

* if you should store a True/False (or 1/0) information, choose a `Yes/No`
* if you need to store a small number (f.i. `Number of children`), don't choose Number - Long Integer when Number - Byte is enough,
* ...

And the right size: especially for text fields, don't use a 255 length (it's almost never needed) but try to think about the longest value (do you really have a customer with a family name of 255 characters? probably 50 will be enough).

### Queries

#### Use Between

When you need to write a `between` criteria, f.i. ID between 5 and 9, you can do this like this:

![Prefer BETWEEN](./images/query_between.webp)

* Twice the same column, one with `>= 5` and the second with `< 10`
* Or in the same column using a `Between 5 and 9`

The best way is the yellow one: only one field and thus only one criteria to evaluate.

#### Count the number of records

If you use the Count function to calculate the number of records returned by a query, use the syntax `Count(*)` instead of `Count([some_field])`.

`Count(*)` is faster since it doesn't have to check for Null values in the specified field and won't skip records that are null.

*By using `Count([some_field])` the number returned **can be lower** than the real number of records if the column `some_field` is sometime equal to the Null value.*

#### Only needed fields

If you're using a query in another query like illustrated below, if you just need three fields in the second query, perhaps the first query can be modified and return only these three fields.

![Query using another query](./images/query_on_query.webp)

If, in this example, the query called `qryFirst` is only used by this second query, then you can modify the first query to only return three columns.

The less data returned, the faster the query will be processed.

#### Use Is Null and not IsNull()

In a query, use `Is Null` which is native in the SQL standard while `IsNull()` is, in fact, a VBA feature.

Using `IsNull()` is therefore slower than `Is Null`.

So, avoid,

```SQL
SELECT ... FROM ... WHERE IsNull(fld)
```

And use,

```SQL
SELECT ... FROM ... WHERE (fld Is Null)
```

#### Use Iif() and not NZ()

NZ() is a VBA feature: if a field is equal to the Null value, NZ() lets you return something else.

Iif() is native SQL.

Simplified illustration: if the hiring date is null, assume it was January 1st, 2000.

```SQL
SELECT NZ([dteHiring], #2000-01-01#) As dteEntry
```

The same can be also written like:

```SQL
SELECT Iif([dteHiring] Is Null, #2000-01-01#, [dteHiring]) As dteEntry
```

Why is it bad? **NZ() will return a variant object which is less performant because the .JET engine of MS Access treats Variants as Text**. In the first example `NZ([dteHiring], #2000-01-01#) As dteEntry`, the data type of dteEntry is therefore a text field, not a date.

So, in the example below, we're not comparing a date versus another date but a date `dteEntry` with a text (the result of the `NZ()` formula). So .JET engine will convert `dteEntry` to a text too. The conversion is therefore done twice and this is inefficient.

![Using NZ() as a criteria](./images/nz_date.webp)

With `Iif([dteHiring] Is Null, #2000-01-01#, [dteHiring]) As dteEntry` the type remains a date field. So we can quickly solve the double conversion introduced by `NZ()` using this construction:

![Using Iif() as a criteria](./images/iif_date.webp)

Here, .JET won't complain anymore and will immediately compare a date with another date; faster.

#### Avoid VBA functions

We've already seen this. Use `Is Null` and not `IsNull()`, use `Iif` and not `NZ()`, and this applies to a lot of other formulas.

##### Year()

Year() is a VBA function. The query below requires every single date to be processed by Year() to determine the year before evaluating the criteria.

If there is an index on `dteHiring`, the index is ignored due to the VBA call.

```SQL
SELECT ... FROM ... WHERE (Year([dteHiring]) = 2018)
```

Prefer the native SQL `BETWEEN` verb:

```SQL
SELECT ... FROM ... WHERE ([dteHiring] between '1/1/2018 00:00:00' and '12/31/2018 23:59:59')
```

Here the index is well used, .JET also doesn't need to make a VBA call record by record but can work on a set of records at once.

##### DCount(), DSum()

Prefer to rewrite the query and use, f.i., sub-queries with a GROUP BY and the desired formula (Count(), Sum(), ...)

#### Sorting on columns not formula's

If possible, it's better to use a sort feature on columns (so probably use indexes).

In the following example, ORDER BY is done on a formula (a string concatenation); no indexes can be used here, so the time needed to sort records is longer.

```SQL
SELECT ClientID, Name + " " + FirstName As FullName
FROM tblClient
ORDER BY Name + " " + FirstName
```

In the example below, the result will be exactly the same except that if one or the two columns are indexed, the result will be faster.

```SQL
SELECT ClientID, Name + ", " + FirstName As FullName
FROM tblClient
ORDER BY Name, FirstName
```

#### Use the same (or compatible) data type in join fields

When you make a link between two tables (with a join), try to respect the data type: avoid making a link between f.i. a number and a text field.

Consider the following example:

![Relation](./images/sametype_relation.webp)

*The field `ID` in `tblCustomer` is a number*

![Customer](./images/sametype_customer.webp)

*The field `CustomerNumber` in `tblOrder` is a text (and a big one)*

![Orders](./images/sametype_orders.webp)

MS Access will be able to make the join and will make things work but, implicitly, will convert any fields so they have the same data type and this will cost CPU.

In this example, if possible, change the data type of  `CustomerNumber` in `tblOrder` to a `Long integer` so the fields will be compatible.

#### First filter by using WHERE before HAVING

Totals queries (those with a `GROUP BY` clause) can have both a `WHERE` clause and a `HAVING` clause.

* `WHERE` is executed first **before aggregation**
* `HAVING` is executed afterwards **when the totals have been calculated**

Below, there is no `WHERE` clause: the calculation "How many invoices by customer" is made for all customers (several thousand maybe) and, once all this calculation is done, you just ask for the figure for customer 99. Rather inefficient.

```SQL
SELECT ClientID, Count(InvoiceID) AS HowMany
FROM tblInvoice
GROUP BY ClientID
HAVING ClientID = 99;
```

Take advantage of the `WHERE` priority and obtain the same information, much faster, like this:

```SQL
SELECT ClientID, Count(InvoiceID) AS HowMany
FROM tblInvoice
WHERE ClientID = 99
GROUP BY ClientID;
```

Here, .JET only takes records for that customer before starting to count.

#### If you're group on the primary key

As soon as you're using a GROUP BY in your SQL, every "not" calculated field should be mentioned in the GROUP BY clause.

Because we're not using a formula like Count(), Max(), ... on the LastName, that field should be mentioned in the GROUP BY clause so:

```SQL
SELECT EmployeeID, LastName, Count(Illness)
FROM Employees
GROUP BY EmployeeID, LastName
```

But, here, grouping on LastName is unnecessary because EmployeeID is already unique: when using the primary key in the GROUP BY, any other fields are needless and, due to their presence, .JET will make grouping for nothing. Unneeded actions...

Because the primary key is in the GROUP BY clause, we can use such SQL:

```SQL
SELECT EmployeeID, First(LastName) AS LastName, Count(Illness) As IllDays
FROM Employees
GROUP BY EmployeeID
```

By using First() we allow .JET to only retain the first LastName for that Employee ID (and, of course, the employee has only one LastName).

This is therefore faster.

### Macros

#### Convert macros to modules

Converting a macro to Visual Basic for Applications (VBA) code can enable certain actions to run faster.

To convert a macro to VBA, select the macro (don't open it, just select it) and, in the `Database Tools` tab click on `Convert Macros to Visual Basic`.

Then, once done, you'll probably need to update your interface: if you've forms, you'll need to update f.i. buttons to fire the VBA module instead of the macros.

If everything goes fine, just delete the converted macros.

### Modules

#### Save modules in a compiled state

If the database contains modules, be sure in the production version, to compile the source code. You just need to open one module (no importance) and to click on the `Debug` menu then `Compile`.

![Compiled state](./images/compiled_state.webp)

#### Use Option Explicit statement

If you've modules or form's event coded in VBA, open every module / forms (press then `ALT-F11` to open the editor) and in the very first line of your code, type `Option Explicit` like f.i.:

```vbnet
Option Compare Database
Option Explicit

Sub DoSomething()
    ' your own code
End Sub
```

Option Explicit forces the VB Engine to check that variables exist before starting to run the code, and not every time a variable is accessed. This gives a small speed improvement.

> **Caution:**
>
> Option Explicit is above all an excellent coding practice: variables should be declared before being used.

#### Unload references

Take a look, in your code, if you're referencing too much external dependencies.

From within the VB Editor, click on the `Tools` menu then select `References` and pay attention to the first items in the list, the checked one. Do you really need them?

An easy way to answer this question is to uncheck them and click on the `OK` button.

Click on the `Debug` menu and choose `Compile`. If you get compilation errors (and no errors before), go back in the `References` window and check the library back.

![Unload unused references](./images/unload_references.webp)

Most of time, only two references are needed:

1. `Visual Basic For Applications` (always at the top)
2. `Microsoft Access 16.0 Object Library` (the second one) (Note: "16.0" is variable and depends on your MS Office installed version)

### Database

#### Compact the database

If you've temporary tables, remove them beforehand.

Compacting the database will free up space, remove unneeded objects and records, and update internal statistics; this will have a positive impact on performance.

#### Save the database as a .mde file

**!!! Be careful !!!**

A .mde file is smaller and faster than a normal MS Access (.accdb file) but you can no longer modify the structure of such a file.

With a .mde, you'll no longer be able to change the design of a table, add a field, modify your forms, ... but just use them.

![Save as - MDE](./images/save_as_mde.webp)

**If you wish to use a .mde, always take a backup of your .accdb (or .mdb) file and keep it in a secured folder. Once more: if you don't have anymore the .accdb file but only the .mde, you won't be able to change the structure of the application, you won't be able to modify a macro, a form, ...**

> **Scan all tables and export an exhaustive fields list in Excel:**
>
> Also see my post [VBS - Retrieve the list of fields in a MS Access Database](/blog/vbs-msaccess-get-fields).


---

<!--
  canonical-url: https://www.avonture.be/blog/vbs-msaccess-get-fields
  published:     2024-03-09
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# VBS - Retrieve the list of fields in a MS Access Database

> Use a VBScript to easily retrieve a detailed list of fields from your MS Access database. Analyze field size, type, and actual value length to optimize your database structure.

---
![VBS - Retrieve the list of fields in a MS Access Database](/img/v2/msaccess.webp)

**TL;DR**

This article shares a VBS script that scans every table in an MS Access database and exports a detailed field report to Excel — name, type, defined size, and the actual shortest/longest value found in the data — making it easy to spot oversized fields (e.g. a 255-char field that never holds more than 10 characters) worth shrinking for optimization.

Years ago, I created a .vbs script that scans a MS Access database, loops through each table, and, for each one, gets the list of fields.

For each field, a lot of information will be retrieved (non-exhaustive list): its name, size, type, ... and also the shortest and longest value size (for text and memo fields). For instance, if a text field is found, the script will retrieve its size (f.i. 255 chars max) and will examine all records in the table to find, for that field, the smallest size (f.i. 10) and the greatest one (f.i. 50). So, if the max size is 50 and the size has been set to 255, perhaps the MS Access developer can safely modify the max size from 255 to 50.

## The report you'll get

Double-click a `.cmd` file, wait a few seconds, and Excel opens by itself on this:

![MS Access Get fields list](./images/get_fields_list.webp)

One line per field, for every table of the database, with the two columns that matter for optimization side by side: `FieldSize` (what the table allows) and `LongestSize` (what the data actually uses).

## Understand the report

* Filename: The MS Access filename (absolute)
* TableName: The name of the table
* FieldName: The name of the field found in that table
* FieldType: The data type (integer, string, date, ...)
* FieldSize: The maximum size defined in the table (f.i. 255 means that this field can contain up to 255 characters)
* ShortestSize: When the table contains records, the ShortestSize info is "what is the smaller information stored in that field?" (example: if the field is a firstname, size 255 but the shortest firstname is `Paul`, then `ShortestSize` will be set to 4)
* LongestSize: When the table contains records, the `LongestSize` info is "what is the biggest information stored in that field?" (example: if the field is a firstname, size 255 but the longest firstname is `Christophe`, then `LongestSize` will be set to 10)
* Position: The position of that field in the structure of the table (is the first defined field, the second, ...)
* Occurrences: How many times that specific `FieldName` is found in the entire database. If you've a lot of tables, perhaps the field called `CustomerID` is used in the `Customers` table and in the `Orders` table too so `Occurrences` will be set to 2 in this case.

## How to reproduce it

1. Copy/paste the source code of the two scripts (see below) and save them to text files (with Notepad). The first file to create will be `access_get_fields_list.vbs`, the second will be `access_get_fields_list.cmd`,
2. Before saving the `access_get_fields_list.cmd` be sure to edit the file and mention the full filename of your database,
3. You're ready: from your File Explorer, just double-click on `access_get_fields_list.cmd`. The analysis script will run and Excel will open at the end.

*If everything goes fine, you'll see a DOS window and after a few seconds (depending on the size and complexity of the database), you'll have the report in Excel, automatically opened.*

### access\_get\_fields\_list.vbs

Get the source code from my GitHub page: <https://github.com/cavo789/vbs_access_get_fields_list/blob/master/access_get_fields_list.vbs>.

Copy the code in f.i. Notepad then save it on your disk, f.i. `access_get_fields_list.vbs` as filename.

### access\_get\_fields\_list.cmd

Proceed the same way for the <https://github.com/cavo789/vbs_access_get_fields_list/blob/master/access_get_fields_list.cmd> script. Copy/paste the line in Notepad and remember to change the database name (no more `C:\temp\my_db.accdb` but yours).

Save the file onto your hard disk and give `access_get_fields_list.cmd` as filename. You can then quit Notepad.

## What to do with the numbers

In a context of optimization (see also [How to optimize an existing MS Access database](/blog/msaccess-optimize)):

* Be sure not to use too large a field size. By default, MS Access suggests a size of 255 for text fields, but for a name or first name field, a size of 40 characters is enough.
* Check the `LongestSize` property: if you see f.i. a size of 4 this means that you're probably storing a code (a ZipCode f.i. is max 4 digits in Belgium). If the `FieldSize` is set to 50, you know you can reduce that size to 4.

## Conclusion

Two scripts, a double-click, and the structure of an inherited database is on screen as an Excel sheet you can sort. The gap between `FieldSize` and `LongestSize` is where the wasted space hides, and reading it takes a minute rather than opening thirty tables in design view.

Same "audit an existing database" spirit, different angle: [Export MS Access objects](/blog/vba-access-export) gets your forms, queries and modules out as plain text files, ready to diff.


---

<!--
  canonical-url: https://www.avonture.be/blog/vbs-auto-update
  published:     2024-03-19
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# VBS - Auto update script

> Automate updates for your VBS (Visual Basic Script) console scripts! Learn how to create a self-updating VBS file that connects to GitHub, checks for new versions, and overwrites itself.

---
![VBS - Auto update script](/img/v2/vbs.webp)

**TL;DR**

This article shows how to give a VBScript (VBS) file a self-update capability: on each run, the script downloads its own source from a public GitHub URL, compares it to the local copy, and overwrites itself if a newer version is found — letting distributed `.vbs` utilities stay current without manual redistribution.

Before switching to [WSL2](/blog/tags/wsl) and the Linux console, I wrote VBS scripts from time to time. It looks like VBA but for the DOS console.

A VBS script for DOS is a text file written in the Visual Basic Scripting Edition (VBScript) programming language that can be executed directly from the DOS command prompt. It allows you to automate tasks and perform repetitive operations on your computer.

It's just like [Linux Bash](/blog/tags/bash) scripts but for DOS.

*Three VBS utilities of mine that would benefit from exactly this self-update mechanism: [VBS - Retrieve the list of fields in a MS Access Database](/blog/vbs-msaccess-get-fields), [Export MS Access objects](/blog/vba-access-export) and [VBS - Get list of files and generate a CSV](/blog/vbs-files-csv).*

Do you think it would be possible to offer an auto-update function in such scripts? The answer is yes.

Imagine a script called `get_folder_size.vbs` you've publicly saved on GitHub (source <https://github.com/cavo789/vbs_utilities/blob/master/src/folders/get_folder_size/get_folder_size.vbs>).

Someone downloads it to their computer and enjoys using it.

By adding a new *auto-update* function to it, each time the script starts, a connection to GitHub is made first, the script is downloaded from there, and a check is made to see if the downloaded version is different; if so, the script overwrites itself.

Here is the content of such function:

**get\_folder\_size.vbs**

```vbs
' -----------------------------------------------------------------
'
' Connect to GitHub, get the raw version of a file and download it
'
' The script will check for newer version on GitHub and if
' there is one, the script will overwrite himself with
' that newer version.
'
' Based on a script of
' @author Michi Lehenauer §https://github.com/michiil)
' then modified by Christophe Avonture
'
' @src https://github.com/michiil/vbs_scrips/blob/master/WZV-Excel.vbs
' -----------------------------------------------------------------

' The content to download
url = "https://raw.githubusercontent.com/cavo789/vbs_utilities/master/src/folders/get_folder_size/get_folder_size.vbs"

Set req = CreateObject("Msxml2.ServerXMLHttp.6.0")

req.setTimeouts 500, 500, 500, 500

' If you're behind a firewall, uncomment the following line
' and mention the proxy address and port
'req.setProxy 2, "your.proxy.net:8080", ""

req.open "GET", url, False
req.send

If Err.Number = 0 Then
  If req.Status = 200 Then
    ' Get the content, just downloaded
    downloadedContent = req.responseText

    ' Get the original content, this script

    scriptName = wScript.ScriptFullName
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set textFile = objFSO.OpenTextFile(scriptName, 1)
    originalContent = textFile.ReadAll
    textFile.Close

    ' Compare if the two contents are different
    If (originalContent <> downloadedContent) Then
      ' If yes, for instance, rewrite this script by
      ' the new content ==> auto-update
      Set textFile = objFSO.OpenTextFile(scriptName, 2)

      textFile.Write (downloadedContent)
      textFile.Close

      'wScript.echo scriptName & " has been updated"
    End If

    Set textFile = Nothing
    Set objFSO = Nothing

  End If
Else
    ' Ok, in case of error, don't panic, just do nothing
    Err.Clear
End If
```


---

<!--
  canonical-url: https://www.avonture.be/blog/outlook-vba-pdf
  published:     2024-07-10
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# Microsoft Outlook - VBA - Save emails as PDF

> Learn how to implement a VBA macro in Microsoft Outlook to easily save selected emails as PDF files to your hard drive, complete with step-by-step installation and usage instructions.

---
![Microsoft Outlook - VBA - Save emails as PDF](/img/v2/outlook_vba.webp)

**TL;DR**

This article shares a VBA macro for the Outlook desktop client that saves one or more selected emails as PDF files, added to the Ribbon as a custom button via the VBA editor. When run, it prompts for a save folder, whether to delete the emails from Outlook after export, and whether to name each PDF manually or from the email subject.

You may also need to select several emails from Microsoft Outlook and save them as [PDF files](/blog/markitdown) on your hard drive.

In my case, it was when I was complementary self-employed. I had to keep track of the orders I received and the invoices I sent. Saving my orders as PDFs meant that I could keep them as archives, even if my mail server failed.

This post will explain to you how to create such a macro for Outlook.

## Result

Select one or more emails, click the button, and the macro walks you through three prompts:

![Five emails selected](./images/five_emails_selected.webp)

1. A confirmation to proceed with the selected emails.
2. A folder picker — where the PDFs should be saved.
3. Whether to remove the emails from Outlook afterward, and whether to name each PDF manually or reuse the email's subject line.

At the end, every selected email is saved on your hard disk as a PDF.

## Prerequisites

You should have Microsoft Office on your hard disk and you need to have Outlook and Word installed.

The macro will not work with Office online.

## Installation steps

**Installation steps**

1. Just start your Microsoft Outlook client (as software on your hard disk; not in your web browser),

2. Press <kbd>ALT</kbd>+<kbd>F11</kbd> to open the `Visual Basic Editor` (aka `VBE`) window,

3. Click on the `Insert` menu then `Module`,

4. Click on the link <a href="https://github.com/cavo789/vba_outlook_save_pdf/blob/master/module.bas"><https://github.com/cavo789/vba_outlook_save_pdf/blob/master/module.bas></a> to open my repository on GitHub and click on the `Copy raw file` button to copy the source code in the clipboard

5. Back to Outlook and press <kbd>CTRL</kbd>+<kbd>V</kbd> in the editor so you paste there the code,

6. Close the `Visual Basic Editor` and come back to Outlook,

7. Click anywhere on the Ribbon and select `Customize the Ribbon...`

8. In the new dialog, click on the `New Group` button

9. In the *Choose commands from*, select `Macros`, you should see the `SaveAsPDFFile` macro as illustrated below.

10. Drag and drop the macro to your new group.

11. Click on the `OK` button to close the dialog.

You should see [your new group](/blog/vba-excel-ribbon), in my case, I've created the new group in `Home` and at the first position left so I've this:

![The new group](./images/ribbon_macro.webp)

## The folder and naming dialogs

The confirmation, folder picker and naming prompts are described at the top of this article. The folder picker looks like this:

![Where emails should be saved?](./images/where_to_save.webp)


---

<!--
  canonical-url: https://www.avonture.be/blog/vbs-files-csv
  published:     2024-11-28
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# VBS - Get list of files and generate a CSV

> Need a file list in CSV format? Use this VBScript to scan a folder, including sub-folders, and output file details like size, date, and owner for easy analysis.

---
![VBS - Get list of files and generate a CSV](/img/v2/vbs.webp)

**TL;DR**

This article shares a VBScript (`files2csv.vbs`) that recursively scans a Windows folder and exports file details — path, name, creation/access/modified dates, size, type, and owner — to a CSV file, ready for further analysis in Excel or Python/Pandas.

Six years ago, I needed a DOS script that would allow me to find the list of every file in a folder on a Windows machine and generate a `.csv` file with that list.

Once the `.csv` file had been generated, I could then process it in MS Excel, for example, and sort/filter it or, why not, in Python using the [Pandas library](/blog/python-pandas-merge).

Here's what this script produces: a CSV file listing every file in a folder, with dates, size, type, and owner.

```csv
"FilePathAndName";"ParentFolder";"Name";"DateCreated";"DateLastAccessed";"DateLastModified";"Size";"Type";"Suffix";"Owner";
"C:\temp\test\test.csv";"C:\temp\test";"test.csv";"21-01-24 09:07:44";"21-01-24 09:09:08";"21-01-24 09:09:08";"472";"CSV Microsoft Excel File";"csv";"Christophe";
"C:\temp\test\test.vbs";"C:\temp\test";"test.vbs";"21-01-24 08:43:49";"21-01-24 09:09:03";"21-01-24 09:09:03";"3246";"VBScript File";"vbs";"Christophe";
```

Here's the script that generates it. Copy/paste the source below and save it to a file, let's say `c:\files2csv.vbs`. Start a DOS console, run `cd c:\` then `cscript files2csv.vbs`. When the job has been done, you'll get the listing in `files2csv.csv`.

**files2csv.vbs**

```vbs
'------------------------------------------------------------
'
' Get the list of files of the current folder + sub-folders and
' generate a .csv file with file information like path, size,
' extensions, author, ... making then easy to work with that list
' in Excel
'
' Based on a script of
' @author Peter Pinchao Liu (https://github.com/lpcclown)
' then modified by Christophe Avonture
'
' @Link : https://github.com/lpcclown/fileScan
'
'------------------------------------------------------------

Option Explicit

Dim objFSO, objCSVFile

' ------------------------------------
' Loop all file under one folder
' ------------------------------------
Function FilesTree(sPath)

Dim objFolder, objSubFolders, objSubFolder, objFiles, objFile
Dim objShell
Dim strFileName

  Set objFolder = objFSO.GetFolder(sPath)
  Set objSubFolders = objFolder.SubFolders
  Set objFiles = objFolder.Files

  For Each objFile In objFiles
    objCSVFile.Write chr(34) & objFile.Path & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.ParentFolder & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.Name & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.DateCreated & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.DateLastAccessed & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.DateLastModified & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.Size & chr(34) & ";"
    objCSVFile.Write chr(34) & objFile.Type & chr(34) & ";"
    objCSVFile.Write chr(34) & objFSO.getextensionname(objFile.Path) & chr(34) & ";"

    ' Get file owner
    Set objShell = CreateObject ("Shell.Application")
    Set objFolder = objShell.Namespace (sPath)

    For Each strFileName in objFolder.Items
      if objFolder.GetDetailsOf (strFileName, 0) = objFile.Name Then
        objCSVFile.Write chr(34) & objFolder.GetDetailsOf (strFileName, 10) & chr(34) & ";"
      End If
    Next

    objCSVFile.Writeline

  Next

  For Each objSubFolder In objSubFolders
    FilesTree(objSubFolder.Path) ' Recursion
  Next

  Set objFiles = Nothing
  Set objSubFolders = Nothing
  Set objFolder = Nothing

End Function

Dim sResultFileName, sFolderName
Dim wshShell

Const ForWriting = 2

  ' Create new CSV file : same name of this script but with
  ' .csv extension
  sResultFileName = wScript.ScriptFullName
  sResultFileName = replace(sResultFileName, ".vbs", ".csv")

  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set objCSVFile = objFSO.CreateTextFile(sResultFileName, ForWriting, True)

  ' Write comma delimited list of columns in new CSV file.
  objCSVFile.Write chr(34) & "FilePathAndName" & chr(34) & ";" & _
  chr(34) & "ParentFolder" & chr(34) & ";" & _
  chr(34) & "Name" & chr(34) & ";" & _
  chr(34) & "DateCreated" & chr(34) & ";" & _
  chr(34) & "DateLastAccessed" & chr(34) & ";" & _
  chr(34) & "DateLastModified" & chr(34) & ";" & _
  chr(34) & "Size" & chr(34) & ";" & _
  chr(34) & "Type" & chr(34) & ";" & _
  chr(34) & "Suffix" & chr(34) & ";" & _
  chr(34) & "Owner" & chr(34) & ";"

  objCSVFile.Writeline

  Set wshShell = CreateObject("WScript.Shell")
  sFolderName = wshShell.CurrentDirectory
  Set wshShell = Nothing

  wScript.echo "Scan the " & sFolderName & ", please wait..."
  wScript.echo ""

  FilesTree(sFolderName)

  wScript.echo "Done, file " & sResultFileName & " has been created"
```

> **Did you need tab delimited?:**
>
> If so, just make a search & replace to change `";"` to `vbTab` everywhere.


---

<!--
  canonical-url: https://www.avonture.be/blog/vba-excel-ribbon-load
  published:     2025-02-22
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# MS Office - Load dropdown from Excel's range

> Load an Excel range into your custom VBA Ribbon dropdown control. This step-by-step tutorial covers the VBA code, Custom UI XML, and named ranges needed for a dynamic solution.

---
![MS Office - Load dropdown from Excel's range](/img/v2/ribbon.webp)

**TL;DR**

Learn how to create a dynamic dropdown menu in a custom Excel ribbon that is populated with values from a worksheet range. This step-by-step guide walks you through the entire process: naming your data range in Excel, defining the custom ribbon layout with XML using the Custom UI Editor, and writing the necessary VBA callback functions to load the items, handle selections, and write the chosen value back to a specific cell. This technique allows for flexible and user-friendly interfaces in your VBA projects.

In this article, we'll see how, very easily, we can load an Excel range into a ribbon and display it inside a dropdown. *If you've never built a custom ribbon before, start with [MS Office - How to create a ribbon in Excel](/blog/vba-excel-ribbon).*

The idea is to provide a list of values in a ribbon but to not have to hardcode values in the list but, just, to link to a range, anywhere in your workbook.

I've used this technique in many of my Excel applications (i.e. Excel files having VBA code).

For this blog post, we'll create a list of periods (YYYYMM) in a worksheet and load that list in our custom ribbon.

In this way, we could offer a nicer user experience by proposing a list and executing, for example, [a query to a database](/blog/vba-excel-sql-server-part-2) to obtain the data for this period (or anything else).

Here's the result: the dropdown loaded straight from the worksheet range, and the selected value written back into a cell.

![Demo](./images/demo.webp)

## Why It Works

* The dropdown's items come from a **named range** (`_rngParamsPeriod`), not hardcoded values in the XML — update the range in the sheet and the ribbon list updates with it.
* A VBA callback (`getItemCount`, wired in the ribbon's XML) does the reading; selecting a value in the ribbon writes it straight into a named cell (`_Period`) elsewhere in the workbook.

## Let's play

First, create an empty workbook. Create then a new sheet called f.i. `Params` with a list of values. For this article, let's create a list of periods:

![The range](./images/range.webp)

Nothing difficult right now. To be flexible, please select the range and name it: `_rngParamsPeriod`. This is much better than hardcoding a range like `$A$2:$A$14`, isn't it?

The second thing to do is to foresee a cell in your sheet where the selected value will be written i.e. when the user selects a value from the list, we'll ask Excel to put the selected value there. To do this, just click on the cell where you wish to see the selected period and name that cell `_Period`. On the image below, I'll select cell `$C$2` on the same sheet but it can be elsewhere.

![The period range](./images/selected_period.webp)

Time to save your Excel file for the first time, let's say in `c:\temp\ribbon.xlsx`, then close the workbook.

## Adding a ribbon

Time to add our ribbon. To do this, just download this free tool: <https://bettersolutions.com/vba/ribbon/custom-ui-editor-download.htm>. You'll find an executable called `CustomUIEditor.exe`. Double-click on it to start the editor then open your `c:\temp\ribbon.xlsx` file:

![Custom UI editor](./images/editor.webp)

Click on the Insert menu then select `Office 2010 Custom UI Part`.

Paste the XML below in the editor window:

**customui.xml**

```xml
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab id="demoRibbon" label="demo Ribbon" insertBeforeMso="TabHome">
        <group id="grpParams" label="Parameters">
          <dropDown id="paramsCbxPeriod" label="Period" screentip="Select the period" supertip="Please select the period..." onAction="modToolbar_cbxPeriod.onAction" getSelectedItemID="modToolbar_cbxPeriod.getSelectedItemID" getItemLabel="modToolbar_cbxPeriod.getItemLabel" getItemID="modToolbar_cbxPeriod.getItemID" getItemCount="modToolbar_cbxPeriod.getItemCount"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
```

You'll then have this:

![Custom UI editor](./images/xml.webp)

Save your changes and quit the editor.

## Time to add our VBA code

From your explorer, double-click on your `c:\temp\ribbon.xlsx` file to start Excel and open the workbook again.

You'll get an error message and it's perfectly normal: we still need to add some VBA code so just press on **Ok**.

![Error](./images/missing_code.webp)

> **Information:**
>
> In our ribbon, we wrote, among other things, the following: `getItemCount="modToolbar_cbxPeriod.getItemCount"`. So, Excel is trying to run a function called `getItemCount` from a module called `modToolbar_cbxPeriod` and ... we don't have it yet.

Press `ALT`-`F11` to open the VBE editor

As illustrated below, right-click on the `VBEProject` project and insert a new module.

![Inserting a new module](./images/insert_module.webp)

This done, click on the added module and bottom left, you can give it a name, f.i. `modToolbar_cbxPeriod`.

In the right, main, part of the screen, please paste this code:

**module.bas**

```bas
Option Base 0
Option Compare Text

' Name of the range in the workbook, with the list of periods
Private Const cRangeName = "_rngParamsPeriod"

' When a period will be selected from the ribbon, a "name"
' will be created in the active workbook so, in a cell formula,
' it's possible to retrieve the selected value.
' Formula example :
'     = "Selected period is " & _Period
Private Const cName = "_Period"

Private sValue As String

' -------------------------------------------------------------
'
' Initialization, define default value : select the last value
' of the range
'
' -------------------------------------------------------------

Public Sub Initialize()

Dim wLastIndex As Byte

    ' Get the last value of the range
    wLastIndex = shParams.Range(cRangeName).Rows.Count
    sValue = shParams.Range(cRangeName).Cells(wLastIndex, 1).Value

    ' Create / Update the name
    Range(cName).Value = sValue

End Sub

' -------------------------------------------------------------
'
' Return the selected value to the calling code.
'
' For instance, from within a VBA module just call
'    Msgbox modToolbar_cbxPeriod.GetValue()
'
' -------------------------------------------------------------

Public Function GetValue() As String
    GetValue = sValue
End Function

' -------------------------------------------------------------
'
' Remember the selected value : the user has selected a value from
' the ribbon
'
' -------------------------------------------------------------

Sub onAction(control As IRibbonControl, id As String, index As Integer)
    sValue = id

    Range(cName).Value = sValue

    ' Optional, inform the user in the Excel's statusbar
    Application.StatusBar = "Set to [" & sValue & "]"
End Sub

' -------------------------------------------------------------
'
' Return the number of entries for the combobox
'
' -------------------------------------------------------------

Sub getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = shParams.Range(cRangeName).Rows.Count
End Sub

' -------------------------------------------------------------
'
' Set the ID for each entry (the ID can be different of the displayed caption)
'
' -------------------------------------------------------------

Public Sub getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = shParams.Range(cRangeName).Cells(index + 1, 1).Value
End Sub

' -------------------------------------------------------------
'
' Define the label (the caption) of each entry in the list
'
' -------------------------------------------------------------

Public Sub getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = shParams.Range(cRangeName).Cells(index + 1, 1).Value
End Sub

' -------------------------------------------------------------
'
' Get the default value for the combobox
'
' -------------------------------------------------------------

Public Sub getSelectedItemID(control As IRibbonControl, ByRef id)
    id = sValue
End Sub
```

We're almost done: we need to give a name to the sheet where the range is located. If you still remember the beginning of this blog post, we've added the range in a sheet called `Params` so, now in the VBE editor, just select the `Params` sheet as illustrated below (see 1.) and name the sheet `shParams` (see 2).

![Naming the sheet](./images/shParams.webp)

> **You should now use the .xlsm extension:**
>
> Save the Excel workbook but, this time, with the `.xlsm` extension since the workbook contains VBA code.

Time to test our feature: close the workbook and re-open it again and, this time, your list has been populated and by selecting a value from the list, the value will be injected in your worksheet; ready to be used — the same result already shown at the top of this article.

## Conclusion

A named range, a bit of Custom UI XML and one VBA callback module: that's the whole recipe for a ribbon dropdown that never needs its values hardcoded again — update the range, and every workbook using this ribbon follows. [Want to keep playing with ribbons? Check out my other articles on the subject.](/blog/vba-excel-ribbon)


---

<!--
  canonical-url: https://www.avonture.be/blog/vba-access-export
  published:     2025-06-27
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# Export MS Access objects

> Easily version control your MS Access database! Export all VBA code, forms, modules, queries, and reports to flat files using a simple VBS script for GitHub integration.

---
![Export MS Access objects](/img/v2/msaccess.webp)

**TL;DR**

This article introduces a VBS script for exporting all objects—forms, modules, queries, reports, and macros—from a Microsoft Access database into individual text-based files. This process allows developers to easily version control their Access application's source code using tools like Git and GitHub. The script automatically organizes the exported files into a structured `src` folder, creating a separate file for each object, making it simple to track changes and maintain a history of the database's design and logic.

A long time ago, in a previous life, I developed a lot of MS Access databases and unlike modern tools, it wasn't possible to version the code (modules, macros, queries, etc.) in a tool like Github.

These objects are an integral part of the database in the same way as the tables and their data. Everything is stored inside the `.mdb` format (or `.mda` or `.accdb`). If you want to version them, you must first extract them.

*Two other MS Access articles on this blog: [VBS - Retrieve the list of fields in a MS Access Database](/blog/vbs-msaccess-get-fields) to audit the structure, and [How to optimize an existing MS Access database](/blog/msaccess-optimize) to act on the results.*

Let's look at how to do this.

## Sample

By running `cscript vbs_access_export.vbs C:\Christophe\db1.mdb` in a DOS console, you'll get this:

```bash
Process database C:\Christophe\db1.mdb

Exporting sources to C:\Christophe\src\db1.mdb\

Export all forms
        Export form 1/2 - frmCustomers to Forms\frmCustomers.frm
        Export form 2/2 - frmInvoices to Forms\frmInvoices.frm

Export all macros
        Export macro 1/15 - mcrPrint to Macros\mcrPrint.txt
        [...]

Export all modules
        Export module 1/2 - Helper to Modules\Helper.bas
        Export module 2/2 - SQL_Server to Modules\SQL_Server.bas

Export all queries
        Export query 1/23 - qryCustomers to Queries\qryCustomers.sql
        Export query 2/23 - qryInvoices to Queries\qryInvoices.sql
        [...]

Export all reports
        Export report 1/8 - Invoice to Reports\Invoice.txt
        [...]
```

Once finished, you'll have a subfolder called `src` with one file by object so, indirectly, you've a backup of your code 👌.

## Description

My <https://github.com/cavo789/vbs_access_export> VBS script will export all code objects (forms, macros, modules, queries and reports) from a MS Access database / application to flat files on your disk.

This way, you'll get a quick backup of your code and you'll be able to synchronize your code on a versioning platform like GitHub.

The script will start MS Access (in hidden mode), open the specified database, process every code object and export them, one by one, in a `\src\your_database.mdb` folder.

The `src` folder will be automatically created if needed and you'll find a subfolder having the same name as your file (so you can have more than one exported file in the same `src` folder).

## Install

Just get a copy of the <https://github.com/cavo789/vbs_access_export> VBS script, perhaps the `.cmd` too (for your easiness) and save them in the same folder as your database.

## Usage

Just edit the `.cmd` file and you'll see how it works: you just need to run the `.vbs` with one parameter, the name of your database — the same command already shown at the top of this article.

## Conclusion

A single VBS script turns forms, modules, queries, reports and macros — everything that used to live only inside the `.mdb`/`.accdb` file — into flat files you can commit to GitHub, diff, and track like any other codebase. Working with VBA-heavy files in general? See also [MS Excel - Get the list of references used in your modules](/blog/vba-excel-list-references), [How to optimize an existing MS Access database](/blog/msaccess-optimize) and [VBS - Retrieve the list of fields in a MS Access Database](/blog/vbs-msaccess-get-fields).


---

<!--
  canonical-url: https://www.avonture.be/blog/vba-excel-list-references
  published:     2025-10-27
  This is a static plain-Markdown mirror generated at build time.
  Visit the canonical URL above for the fully rendered page, with images and interactive components.
-->

# MS Excel - Get the list of references used in your modules

> VBA developer? Stop manually checking references! Use this VBS script to automatically retrieve a list of all VBA references in multiple Excel .xlsm or .xlam files.

---
![MS Excel - Get the list of references used in your modules](/img/v2/vba_export_xlsm.webp)

**TL;DR**

This article presents a VBS script that automates the process of retrieving VBA references from multiple Excel `.xlsm` or `.xlam` files. Instead of manually checking each workbook, developers can run a simple `run.vbs` script in a target folder. The script will then open the files, disable macros, and output a list of all references found.

So you're a VBA developer and you've created a lot of Excel `.xlsm` (or `.xlam`) workbooks i.e. files having VBA modules in it — think of the ones built in [MS Office - How to create a ribbon in Excel](/blog/vba-excel-ribbon) or [MS Excel - Connect to a SQL Server database](/blog/vba-excel-sql-server-part-2).

How can you retrieve the list of references used by any of your files? For sure you can open the workbook, open the VBE editor, click on the `Tools` menu then `References` to get the dialog window with the list of references. Yes, you can.

Or you can use my <https://github.com/cavo789/vbs_xls_list_references> script to automate this — the same kind of small VBS automation script as [Export MS Access objects](/blog/vba-access-export).

Here's what you get, for every `.xlam` or `.xlsm` file in a folder:

```bash
Processing files in C:\Christophe

Get the list of references used in C:\Christophe\Application.xlsm

    Name My_AddIn
    Full Path: C:\Christophe\My_Addin\My_AddIn.xlam

    Name A_Second_AddIn
    Full Path: C:\Christophe\A_Second_AddIn\A_Second_AddIn.xlam

Get the list of references used in C:\Christophe\Invoices.xlam

    Name My_AddIn
    Full Path: C:\Christophe\My_Addin\My_AddIn.xlam
```

Locate a folder on your hard disk where you've one or more `.xlam` or `.xlsm` files.

Simply create there a file called `run.vbs` on your hard disk and copy/paste the source below in it:

**run.vbs**

```vbs

' -----------------------------------------------------------------
' Author: Christophe AVONTURE
' Date	: January 2019
'
' Retrieve the list of references used in an Excel file
' (.xlam files f.i.) and display them on the console.
' Loop and process all Excel files in the current folder

' @src https://github.com/cavo789/vbs_xls_list_references
' -----------------------------------------------------------------

Option Explicit

Class clsMSExcel

    Private oApplication
    Private sFileName
    Private bVerbose, bEnableEvents, bDisplayAlerts
    Private bAppHasBeenStarted
    Private objFSO

    Public Property Let verbose(bYesNo)
        bVerbose = bYesNo
    End Property

    Public Property Let EnableEvents(bYesNo)
        bEnableEvents = bYesNo

        If Not (oApplication Is Nothing) Then
            oApplication.EnableEvents = bYesNo
        End if
    End Property

    Public Property Let DisplayAlerts(bYesNo)
        bDisplayAlerts = bYesNo

        If Not (oApplication Is Nothing) Then
            oApplication.DisplayAlerts = bYesNo
        End if

    End Property

    Public Property Let FileName(ByVal sName)
        sFileName = sName
    End Property

    Public Property Get FileName
        FileName = sFileName
    End Property

    Private Sub Class_Initialize()

        bVerbose = False
        bAppHasBeenStarted = False
        bEnableEvents = False
        bDisplayAlerts = False

        Set oApplication = Nothing

        Set objFSO = CreateObject("Scripting.FileSystemObject")
    End Sub

    Private Sub Class_Terminate()

        oApplication.EnableEvents = True
        oApplication.DisplayAlerts = True

        Set oApplication = Nothing
        Set objFSO = Nothing

    End Sub

    ' --------------------------------------------------------
    ' Initialize the oApplication object variable : get a pointer
    ' to the current Excel.exe app if already in memory or start
    ' a new instance.
    '
    ' If a new instance has been started, initialize the variable
    ' bAppHasBeenStarted to True so the rest of the script knows
    ' that Excel should then be closed by the script.
    ' --------------------------------------------------------
    Public Function Instantiate()

        If (oApplication Is Nothing) Then

            On error Resume Next

            Set oApplication = GetObject(,"Excel.Application")

            If (Err.number <> 0) or (oApplication Is Nothing) Then
                Set oApplication = CreateObject("Excel.Application")
                ' Remember that Excel has been started by
                ' this script ==> should be released
                bAppHasBeenStarted = True
            End If

            oApplication.EnableEvents = bEnableEvents
            oApplication.DisplayAlerts = bDisplayAlerts

            Err.clear

            On error Goto 0

        End If

        ' Return True if the application was created right
        ' now
        Instantiate = bAppHasBeenStarted

    End Function

    Public Sub Quit()
        If not (oApplication Is Nothing) Then
            oApplication.Quit
        End If
    End Sub

    ' --------------------------------------------------------
    ' Open a standard Excel file and allow to specify if the
    ' file should be opened in a read-only mode or not
    ' --------------------------------------------------------
    Public Sub Open(bReadOnly)

        If not (oApplication Is nothing) Then

            If bVerbose Then
                wScript.echo "Open " & sFileName & _
                    " (clsMSExcel::Open)"
            End If

            ' False = UpdateLinks
            oApplication.Workbooks.Open sFileName, False, _
                bReadOnly

        End If

    End sub

    ' --------------------------------------------------------
    ' Close the active workbook
    ' --------------------------------------------------------
    Public Sub CloseFile()

        Dim wb
        Dim I
        Dim sBaseName

        If Not (oApplication Is Nothing) Then

            If bVerbose Then
                wScript.echo "Close " & FileName & " (clsMSExcel::CloseFile)"
            End if

            ' Only the basename and not the full path
            sBaseName = objFSO.GetFileName(FileName)

            On Error Resume Next
            Set wb = oApplication.Workbooks(sBaseName)
            If Not (err.number = 0) Then
                ' Not found, workbook not loaded
                Set wb = Nothing
            Else
                If bVerbose Then
                    wScript.echo "	Closing " & sBaseName & " (clsMSExcel::CloseFile)"
                End if

                ' Close without saving
                wb.Close False

            End if

            On Error Goto 0

        End If

    End Sub

    ' --------------------------------------------------------
    ' Display the list of references used by the file
    ' --------------------------------------------------------
    Public Sub References_ListAll()

        Dim wb, ref
        Dim bShow, bEmpty

        If Not (oApplication Is Nothing) Then

            Set wb = oApplication.Workbooks(objFSO.GetFileName(sFileName))
            bEmpty = True

            If Not (wb Is Nothing) Then

                For Each ref In wb.VBProject.References

                    bShow = (LCase(Right(ref.FullPath,5)) = ".xlam")

                    If bShow then

                        bEmpty = False

                        wScript.echo "    Name " & ref.Name
                        'wScript.echo "    Built In: " & ref.BuiltIn
                        wScript.echo "    Full Path: " & ref.FullPath
                        'wScript.echo "     Is Broken: " & ref.IsBroken
                        'wScript.echo "    Version: " & ref.Major & "." & ref.Minor
                        wScript.echo ""

                    End If
                Next
            End If

            If bEmpty Then
                wScript.echo "    The file didn't use references"
                wScript.echo ""
            End If

            Set wb = Nothing

        End If
    End sub

End Class

' ----------------------------------------------------------
' When the user double-click on a .vbs file (from Windows explorer f.i.)
' the running process will be WScript.exe while it's CScript.exe when
' the .vbs is started from the command prompt.
'
' This subroutine will check if the script has been started with cscript
' and if not, will run the script again with cscript and terminate the
' "wscript" version. This is useful when the script generate a lot of
' wScript.echo statements, easier to read in a command prompt.'
' ----------------------------------------------------------
Sub ForceCScriptExecution()

    Dim sArguments, Arg, sCommand

    If Not LCase(Right(WScript.FullName, 12)) = "\cscript.exe" Then

        ' Get command lines parameters'
        sArguments = ""
        For Each Arg In WScript.Arguments
            sArguments=sArguments & Chr(34) & Arg & Chr(34) & Space(1)
        Next

        sCommand = "cmd.exe cscript.exe //nologo " & Chr(34) & _
        WScript.ScriptFullName & Chr(34) & Space(1) & Chr(34) & sArguments & Chr(34)

        ' 1 to activate the window
        ' true to let the window opened
        Call CreateObject("Wscript.Shell").Run(sCommand, 1, true)

        ' This version of the script (started with WScript) can be terminated
        wScript.quit

    End If

End Sub

' ------------------------------------------------------
' Get the current folder
'
' Use the wScript.ScriptFullName and not the
' wshShell.CurrentDirectory property to make the folder dependant of
' where the script is located since the script can be fired with, f.i.,
' c:\temp > cscript i:\folder\app.vbs.
' We wish to get "i:\folder"
' ------------------------------------------------------
Public Function CurrentFolder

    Dim objFSO, objFile

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.GetFile(wScript.ScriptFullName)

    CurrentFolder = objFSO.GetParentFolderName(objFile)

    ' Be sure to have the final slash
    If Not (Right(CurrentFolder, 1) = "\") THen
        CurrentFolder = CurrentFolder & "\"
    End If

    Set objFile = Nothing
    Set objFSO = Nothing

End Function

Dim cMSExcel
Dim sCurrentFolder, sFiles, sFile, sExt
Dim arrFiles
Dim objFSO, objFolder, objFiles, objFile
Dim I

    Call ForceCScriptExecution

    sCurrentFolder = CurrentFolder()

    sFiles = ""

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' Get the list of files present in the current folder
    Set objFolder = objFSO.GetFolder(sCurrentFolder)
    Set objFiles = objFolder.Files

    ' Loop every files
    For Each objFile In objFiles

        ' Don't process files starting with ~ since Excel use such
        ' filename for files already opened
        If Not (Left(objFile.Name, 1) = "~") Then

            sExt = LCase(objFSO.getExtensionName(objFile.Name))

            ' Process only xlsx files
            If (sExt = "xlsm") or (sExt = "xlam") Then
                ' Just concatenate the filename with a delimiter
                sFiles = sFiles & objFile.Name & "#"
            End If

        End If

    Next

    If (Right(sFiles, 1) = "#") Then
        sFiles = Left(sFiles, Len(sFiles) -1)
    End If

    ' No more needed, release objects
    Set objFile = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFSO = Nothing

    ' sFiles is a string that contains the list of files we need to process
    ' Like f.i. "file1.xlsx#file2.xlsx#file3.xlsx"
    ' Explode the string into an array
    If (Not (sFiles = "")) Then

        ' We've at least one file to process
        wScript.echo "Processing files in " & sCurrentFolder
        wScript.echo " "

        arrFiles=Split(sFiles, "#")

        If (isArray(arrFiles)) Then

            Set cMSExcel = New clsMSExcel
            cMSExcel.Verbose = False
            cMSExcel.EnableEvents = False

            cMSExcel.Instantiate

            For I = 0 To UBound(arrFiles)

                sFile = sCurrentFolder & arrFiles(I)

                wScript.echo "Get the list of references used in " & sFile
                wScript.echo ""

                cMSExcel.FileName = sFile

                Call cMSExcel.Open(True)
                Call cMSExcel.References_ListAll()
                Call cMSExcel.CloseFile()

            Next

            cMSExcel.Quit
            Set cMSExcel = Nothing

        End If

    Else

        wScript.echo "The folder " & sCurrentFolder & " doesn't contains any Excel file"
        wScript.echo " "

    End If
```

Now, start a DOS or Powershell console, go to that folder and run `cscript run.vbs`. That's all. The script will retrieve any `.xlam` or `.xlsm` files and do automation to open Excel, open the file (by disabling the execution of macros) and retrieve the list of references — the same output already shown at the top of this article.


---