8 posts tagged with "vba"

In April 2024, I wrote a small blog post about a VBA script that will connect to a MS SQL database, run a SELECT query to retrieve data and put them in an Excel worksheet.

Let's rewrite this article in a full tutorial. We'll install run a SQL Server database using Docker, download MS SQL Server Management Studio, connect to our SQL Server, create a new database with dummy data and, finally, in Excel, retrieve the list of our customers.

In this article, we'll see how, very easily, we can load an Excel range into a ribbon and display it inside a dropdown.

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 application's (i.e. Excel 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 to obtain the data for this period (or anything else).

WinSCP is a free, open-source file manager for securely transferring files between your computer and remote servers using protocols like SFTP, FTP, SCP, and WebDAV.

Did you know that you can call WinSCP from ... VBA code?

See https://winscp.net/eng/docs/library_vb#using. There are also several posts in the forum: https://winscp.net/forum/search.php?mode=results

You may also need to select several emails from Microsoft Outlook and save them as PDF files 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 macro for Outlook.

Imagine you can execute a query like SELECT customer_id, first_name, last_name, email FROM customers ORDER BY last_name ASC; in your Excel sheet and that Excel will connect your Microsoft SQL Server database, run the query there, get the result and put the data directly in your sheet. Would be nice, no?

Imagine your sheet has already a nice layout with colors, titles having filters enabled and f.i. has a name (like rngMyCustomers). It would be nice if the updated data still keep all the layouts and just extends the name; no?

Stop imagining, it's just so easy!

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.

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 SOAP web service.

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

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.