森の株

Introduction When using VBA macros to output a new file to the same folder as the current workbook or to log the location of an open file, you need to retrieve the workbook's "Full Path" (the complete path combining the file location and...

Environment Edition: Windows 10 Pro Version: 20H2 Background When I tried to close a file using VBA, the following message appeared: "There is a large amount of information on the Clipboard. Do you want to be able to paste this informati...

Overview Excel allows you to specify wildcards (* or ?) in AutoFilter to extract only rows that contain or do not contain specific strings. In this article, I will carefully explain the basics and applications of wildcard specifications ...

Overview In workbooks that utilize Power Query, unnecessary queries and connection information often remain from the trial-and-error process. To save you the trouble of manually deleting unnecessary objects, I will introduce a macro that...

There is a very common business need to batch read data from multiple Excel files stored in a specific folder. In this article, I will introduce a VBA macro that automatically aggregates data from all Excel workbooks in a specified folde...

Background In daily work, I often wanted to "automatically record the date in column A when a value is entered in column B." I thought this would be useful for managing work dates or input dates, so I learned how to automate this recordi...

Introduction When using UserForms created in VBA with default settings, they may appear in unexpected locations. To improve tool usability, it is important to display the form in an easy-to-understand position, such as the center of the ...

Introduction When you declare an array in VBA like Dim myArray(1 To 5), its size is fixed. However, in many cases, you do not know how many elements you need to store until you run the program. For example, you might want to reserve arra...

Introduction When automating with Excel VBA, there is often a need to "automatically execute a process when a value is entered into a cell." In my case, I wanted to automatically enter a date when text was entered in column B, so I resea...

Background When I tried to execute an external VBScript file (.vbs) from VBA, I encountered an error message saying "Unknown runtime error," and the script would not run. At first, I thought there was a mistake in my script code. However...

There are many situations where you want to read external text files (.txt), such as CSV files or system logs, into Excel to use as data. By using the FileSystemObject (FSO), you can easily open text files and read their contents with VB...

When opening another workbook using VBA, if that workbook has a Workbook_Open event set, the macro will execute immediately upon opening. This can be problematic if you only want to read or write data but end up triggering message boxes ...

I will explain the warning message often seen when saving files: "Be careful, as your document may contain personal information that cannot be removed by the Document Inspector." Many people see this and wonder what causes it or if they ...

Background When creating tools in Excel, I often want to simplify user input and operations. Therefore, I use UserForms in my programs. I wanted to be able to open a completed UserForm by clicking a button on the Excel sheet, so I learne...

Using Excel VBA, you can display a countdown timer directly inside a shape on your worksheet. In this article, we will create a system that displays "Remaining Time: 00:00:00" inside a shape and updates it in real-time every second. Goal...

Background When creating tools in Excel, you often want to simplify user input and operations. While UserForms are excellent for this, you need a way for the user to launch them easily from the worksheet. This article explains how to lin...

Using dropdown lists in Excel is a common way to simplify data entry. However, you may encounter issues where the list behavior changes or feels restricted after a selection is made. This article explains how to use VBA (Visual Basic for...

When automating printing tasks with VBA, you may need to switch the output destination depending on the situation—such as choosing between multiple office printers or outputting as a PDF. VBA allows you to either "let the user select a p...

Background When creating a UserForm with VBA in Excel, you often encounter situations where you want to "keep a record of what was entered in the form." For example, when someone registers data using a form, it is useful to automatically...

Introduction When handling files in VBA, you frequently encounter situations where you need to remove the extension (e.g., .xlsx) from a full filename (e.g., Report.xlsx) to get just the base name (Report). This is common when creating n...

Introduction XML files are frequently used for data integration between systems and for configuration files. There are often cases where you need to read this XML data in Excel and list it on a sheet. In this article, I will introduce ho...

Introduction When you want users to select only one item from multiple choices on an Excel sheet, "Option Buttons" (Form Controls) are the ideal tool. They are perfect for questionnaires or settings sheets where choices must be mutually ...

Introduction To connect to and manipulate an Access database (.accdb) directly from Excel VBA, it is common to use DAO (Data Access Objects). This method establishes the connection required to reference tables/queries or write data to Ac...

Excel VBA allows you to easily automate the process of randomly extracting items from a specified name list and placing them into cells. This article introduces the procedure and VBA code for extracting random names without duplicates an...

Introduction In tasks like creating monthly reports, you often need to duplicate a "Template" sheet that already contains the correct formatting and formulas. While copying a sheet manually is simple, using VBA allows you to completely a...

Environment OS: Windows 10 Pro Software: Microsoft Outlook, Excel VBA Background While automating email distribution using VBA is efficient, there are many cases where you might want to double-check the "To" or "CC" fields—especially whe...

When managing data in Excel, you often need to save only a specific range as a CSV file. Manually selecting the range, copying it to a new workbook, and saving it as a CSV every time is a tedious process. This article explains how to use...

Standard VBA arrays manage data using numeric indices, such as myArray(0). However, there are many situations where you want to handle data using strings as keys—for example, retrieving a "Price" using a "Product Name" as the key. This m...

When sharing Excel files with macros, you may want to prevent others from viewing or accidentally editing your source code. To achieve this, you can lock the VBA project with a password. By setting this protection, the code becomes inacc...

When creating reports, you often need to transfer charts and tables from Excel to PowerPoint. Doing this manually for multiple slides is time-consuming. This article provides a VBA macro that automates the process of opening PowerPoint, ...