الأكسل والأكسس

[Bishara]

Introduction to Access/Excel Integration

Most business users understand Excel; its power and practically universal acceptance make it a key application to learn. While Excel is a powerful tool on its own, you can do a lot more with it when you add the power of a relational database. Whether you store your data in a simple Access database or link an Access database to your corporate data warehouse, you'll be able to do a lot of things more easily. A simple query combined with an Excel workbook can supply many of the benefits of expensive reporting packages using the tools you already have on your desktop.

Consider the following scenario. Your company stores sales information in a database, and each sales record carries an identifier that tells who sold the item. You also have a table of salespeople that tells what region they are in and who supervises them. Senior management wants to find out how each salesperson, sales manager, and region performs on a daily basis. Since they want to see the reports so frequently, it will be necessary to automate these reports as much as possible. This book will show you how to gather the information and build the reports, charts, and supporting details that are necessary to meet these business objectives.

If you consider the other uses of corporate data, you will begin to understand how useful these skills can be. Here is a short list of fairly common uses of data:

Producing a monthly commission schedule

Reporting sales by product, region, sales manager, or salesperson

Doing financial reporting

Producing invoices

Performing analysis of data (average profit per sale, sales by month, etc.)

Producing trend information to aid corporate planning

Populating financial models and storing results

Graphing financial and sales information

Building systems that can simplify and automate these tasks can make complex projects much simpler. Fortunately, you likely already have the tools you need to do this on your computer and just need to assemble the parts correctly.

Communications Between Excel and Access

There are several ways to exchange data between Access and Excel. Automation (formerly called OLE Automation) is a method of communication that gives you access to another application's objects . Using Automation, you can actually take control of the other application and send and retrieve data, set properties, run methods, and perform many other tasks. This book will explore in depth how automation can be used to allow integration between Access and Excel.

One of the original ways to communicate between Windows programs was dynamic data exchange (DDE) . While this can be useful, I do not recommend it between Office applications. It is sometimes necessary when you are communicating with a program that does not have a very useful object model. However, the object models for all of the programs in the Office suite allow you so much flexibility that I cannot imagine a situation when DDE would be preferable to Automation with VBA.

One of the original ways to communicate between Windows programs was dynamic data exchange (DDE) . While this can be useful, I do not recommend it between Office applications. It is sometimes necessary when you are communicating with a program that does not have a very useful object model. However, the object models for all of the programs in the Office suite allow you so much flexibility that I cannot imagine a situation when DDE would be preferable to Automation with VBA.

The other methods of communication treat Access or Excel simply as a data source and allow query access. This is accomplished through ActiveX Data Objects (ADO) or Data Access Objects (DAO). In addition to these programming methods, both Access and Excel offer data access methods from the standard user interface that work well for simple tasks.

Automation Objects

If you are new to programming, the mention of objects might not make sense. Objects are programming items that make your life much easier. As an example, one of the main Excel objects is the Worksheet. The Worksheet object is a container for many other objects, such as cells, pivot tables, and charts. By using the Excel object model, you can perform many tasks with one line of code that would have taken hours if there were not another method available. Let's assume that you want to press a button on an Excel worksheet to print it. The following code prints the worksheet when you press the CommandButton1 button:

Private Sub CommandButton1_Click( )

Dim xlws as Worksheet

Set xlws = ActiveSheet

xlws.PrintOut

Set xlws = nothing

End Sub

In this very short procedure, you declare a variable that is an Excel Worksheet (if you were automating Excel from another application, you would declare this as Excel.Worksheet and declare another variable as Excel.Application, but while in Excel this step is not needed). Next, you set this variable equal to the active worksheet—ActiveSheet represents the current worksheet in the active workbook. Once there is a reference to the active worksheet, you can call any of the methods that are part of the object. In this example, you call the PrintOut method of the worksheet. There are several objects in Excel that have a PrintOut method; in each case, it simply prints the object. The final step sets the xlws variable to nothing, which tells Excel to no longer store a reference to the object. The xlws variable in this procedure still exists, even though you are no longer using it—if you were in a procedure that used several worksheets, you could set xlws to nothing and reuse that variable with any other worksheet.

This code may still look complicated, but if you did any programming in MS-DOS where you had to understand how each printer worked and how to send commands to it, you would see how simple this is by comparison.

In future chapters, you will see how to set a reference to each application and how the object model of each can be used to accomplish even the most demanding tasks. You can also get context-sensitive help while working with the VBA project, and when you are not sure how to tackle an Excel task with VBA but know how to do it with the user interface, you can always record a macro and then review the code. Please see Appendix A for a review of the most commonly used objects and their usage in Excel and Access.

ADO and DAO

As stated earlier, ADO and DAO are the two primary methods of data access. For the purposes of connecting to a data source and simply extracting data, the two may be used interchangeably. According to Microsoft, DAO was designed specifically for the Microsoft Jet database at the heart of Access, but it is still able to access other databases while taking a performance hit. There are also some differences in features when it comes to making changes to a data source (adding tables, fields, etc.) and performing more complex query functions, such as data shaping, turning the query result into XML, and using cursors. I generally use DAO when dealing with Access (Jet) databases and ADO when dealing with SQL Server or other databases.

If you have done any work in Microsoft Access, you are probably familiar with queries . When you build a query in the design mode in Access, you are really making a graphical representation of the SQL. To see how this works, you can change the query view in Access to SQL View and see what this looks like.

When you use ADO and DAO, you can reference queries and tables and simply open them. Eventually you will need to modify queries or write them from scratch. In those cases, you can get a head start by designing the query graphically in Access, changing the view to SQL view, and copying the text to your VBA project. You can then make any changes that you need to.

While you can simply copy the text of a query and use it in your code, you can also write SQL on the fly within VBA. This is useful when you want to give users the option to bring in certain fields from the database, change the field used to sort, modify the sort order, etc. Also, there are times when you want to place criteria for a query directly in the query instead of using parameters.

In both ADO and DAO, the primary objects that you will work with are queries, recordsets, fields, and parameters. When using DAO, you also have an object called a QueryDef that performs specific tasks in the book. The QueryDef object references a query. When you assign a variable declared as a QueryDef object and refer to a query, you can perform certain tasks, such as changing the SQL of the query, setting the parameter values, and opening the recordset.

There are some specific differences between ADO and DAO regarding how you set up the connection to the data source. You will see examples of each method throughout this book. When making a decision about which one to use, I suggest deciding based on ease of use. For example, if I am working in an Access database and writing VBA code to modify data structure, I find it much easier to use the DAO object model to accomplish those tasks rather than using ActiveX Data Objects Extensions for DDL and Security (ADOX). With ActiveX data objects, there are different object models for data manipulation, data definition and security, and Remote Data Services (RDS) and multidimensional data (ADOMD). In addition, you can download software development kits (SDKs) from Microsoft that explain both object models. Visit http://www.microsoft.com and search for MDAC (the short name of Microsoft's data access software).

Tackling Projects

This is probably an appropriate time to discuss how to tackle a project that would benefit from integrating Access and Excel. If this discussion doesn't make sense at first, go through the first couple of chapters and come back to it.

The very first step that you need to take, prior to starting a project that integrates Access and Excel, is to determine whether you need the power of both applications. I wouldn't suggest using both applications if you can accomplish the same task with one application and few compromises. If you decide that you do need both applications, the following model should help you perform the initial planning.

The first step in the actual project is to determine which application will serve as the primary application for the user interface. Generally, this decision should be driven by end user needs and preferences. Although there are some exceptions to this, during your initial planning, assume that the program the users see should be the one that they are most comfortable with.

The second step is to determine what information you will need from your end user. It is important to note that in some cases a project will support multiple end users with different needs. A good example is an application that has one end user who wants to input sales data and another end user who wants to create reports based on that sales data. In this example, the two users will share the same data source but will need completely different user interfaces.

Once you have determined those items, your next step is to determine how you will communicate with the other application. Several factors influence this decision. First, the layout of the data makes some types of communication impossible or, at a minimum, silly to try. For example, an Excel spreadsheet with five data points on two worksheets in multiple rows and columns that are not contiguous would not be a candidate for using ADO or DAO, since they expect tabular structures. Likewise, if you need to pull 500 records from an Access database into an Excel sheet that mimics a database table, you probably want to let DAO or ADO do most of the work. This choice is also driven by how much control you need over the other application and the amount of processing that you need to perform on the data.

The next step is to determine whether there will be an end product and what it will look like. In a project about sales data, the end product for a salesperson might be an Access report used as an invoice for the customer. The end product for a sales analyst might be a report in Excel with a pivot table and pivot chart. In cases when there is no end "product," you would want to define what actions you want to accomplish. Examples include accumulating data, updating data, and transmitting data.

Once you reach conclusions about which application will be automating the other, what information you need, and how it will be communicated, you are ready to take the first steps in designing the user interface. This might seem premature, but it is a good idea to prepare a prototype to ensure that you capture the necessary information. This prototype will be a work in progress and may change during the writing of the code (if you are using VBA).

This user interface is probably going to be an Access form, an Excel user form, or an Excel worksheet with protection enabled to only allow data entry into specific cells. If you don't do this, you will end up writing your VBA project twice—the first time to make sure your code works, and the second time to change the references to your user interface. You can do this if it makes you more comfortable, but it will cost you some programming time. Another option is to write your procedures to accept parameters, allowing you to test the procedures and then call them with your user interface. This also makes it easier to reuse code where it makes sense to do so.

When you have thought through your user interface, your next step is to write the code. It is very helpful if you know what the results should be for a few simple data points so that you can effectively test the application. As you write the code for your first couple applications, keep an eye out for recurring items. For example, if you find yourself writing multiple lines of code to set up an Excel reference from Access, you can save that code somewhere and copy and paste it into applications as you need it. Another thing to keep in mind as you write code is to watch out for what might change in the future. For example, if you have some code that builds a 35-line report in Excel with formulas and subtotals, you might note that it is likely that this report could expand or contract in the future. You can prepare for that now by creating a table that holds the necessary data and allows you to change the report without rewriting any code.

The example steps above are simplified, but regardless of the complexity of your project, these steps will need to take place at some point if you want your integration project to be successful.

Designing Applications

If you are writing code simply to make your own projects easier, thinking of them as applications might not be especially relevant. However, if you are building Microsoft Office applications that will be used by others, it is important to think about how the applications might change and how those changes can be dealt with. If you build an application that creates a set of reports and emails them to users, you could hardcode all kinds of information into the code. If you do that, though, any time the reports or recipients change, you will need to change the code.

I try to put elements that might change into tables that can be easily changed. To manage outgoing email, you could have a table that lists the reports and email addresses of the recipients. The code would open that table and send the reports based on the information in the table. This would allow the end user to make the changes necessary to email new or existing reports to new recipients.

While this might not seem very important, if you do not consider factors like this, you will spend more time modifying and maintaining applications than developing them. The same thoughts apply to connection strings to data sources, report formats, and other items where information can change over time. I once helped change an application that was written to produce a report of general ledger accounts with transactions over $1,000. Over time, the company grew and wanted to look at accounts over $50,000. As it turned out, the $1,000 parameter was hardcoded in the application code. Instead of just changing that code, I added a table that held parameters for the general ledger accounts to be queried, the dollar amount to review, and the tables holding the information (each type of transaction had a different table). Once I did this, changes to these criteria could be made without programming.

Some developers seem to build applications that always require developer assistance to make changes. This isn't a strategy I recommend, as it is dangerous for the end user. There are countless requests on the project boards online to modify applications that say that they cannot locate the original developer, or the original developer does not have time to work on it, or other similar reasons. It is also a good idea to document what each procedure does so that if you look at something you made two years ago, you can still follow what you were thinking.

Designing a graphical user interface (GUI) is not covered until much later in the book. As you try to solve a business problem, consider what the information flow will be and the best way to get that data from the user. In addition, it is also useful to consider the best way to display information when that is the purpose of the GUI. For example, is it better to have a large input screen with scrollbars, or is it better to use a tabbed dialog (like many Windows applications)? The other question that comes up when you integrate Access and Excel is which application is best suited for each task. Sometimes the answer is very clear, and at other times there is no clear-cut best product. As you work, the GUI should be in the back of your mind.

Next Steps

In the next chapter, I will introduce you to accessing data from the Excel user interface. This will be very useful for simple tasks for which you need a table of data from a database or another spreadsheet. You can also write database queries with Microsoft Query if you need more specific information than a table or prewritten query. These tasks are all managed from Excel's External Data toolbar. At the end of the next chapter, I will introduce PivotTables as a method for summarizing the data.

To give you a feel for what is to come, you will first learn data access from the Excel user interface, followed by using Excel VBA. Once this is accomplished, you will learn the Access user interface and Access VBA. Next comes an introduction to using these concepts with SQL Server and other Office applications. The final chapters in the book will cover more advanced topics on building applications that integrate Excel and Access. Where applicable, code samples will be available for download online at O'Reilly's web site.

As you go through the book, I suggest having sample Excel and Access files that you can use to apply the concepts discussed. If you don't have your own data, use the sample files that accompany the book. You will most likely get more out of the book if you type the code yourself and get a feel for how to use the VBA interface in Excel and Access. But you can certainly also use (or reuse) the code in the sample files without retyping it. However you decide to use the book, the concepts illustrated are focused around solving common problems that come up in a business environment.

[Financial]

Thanks for You

[sa2000]

مشكور على الجهد الرائع

[ziyad ashraf]

thanksssssssssssssssssssssssssssssssssss