In several previous “recipes” I wrote about how to connect to Microsoft SQL Server from Excel in order to retrieve data to a worksheet. These were all “innocent” operations, since you could only read the data from the database, not to change them. Sometimes it is necessary to create a program that performs more complex manipulation over the database by running appropriate SQL query, which will be discussed in the text that follows.
The VBA language can use two types of functions: those inherent to it and functions that already exist in Excel. Although VBA has a large number of functions in Excel there are even more of them. And since we don’t want, as the saying goes, to „invent the hot water“ it’s often better to use one of the existing functions to finish the job. This is accomplished using the Application.WorksheetFunction method that allows us to call any available Excel function.
Range and Cells objects are used to manipulate Excel ranges and cells. These objects have many useful properties and methods that can affect the content and formatting of individual cells or ranges within a worksheet, whether the active ones or those which we explicitly specify. VBA allows you to manipulate individual cells, ranges or named ranges …
Worksheets and Sheets are object collections that are identical with a few small exceptions. Their task is to manipulate objects within a worksheet, and which one we’ll use is often a matter of personal choice. In the text that follows you will become familiar with the most commonly used properties and methods that allow counting, adding, changing names and other tools for manipulating worksheets.
This collection allows you to work with a variety of subordinate objects, change properties, and run methods for manipulating workbooks. Further it allows you to add new, open, save, close the workbooks… It also enables the opening of external links, document protection and all other operations to control workbooks in order to improve the experience of working in Excel.