Excel Wth Vba
excel wth vbaVBA In Excel and Access
Purpose statement:
The purpose of this term’s work is to enable you to create Pivot Tables programmatically from an Access database using VBA with Excel/Access.
Introduction:
Excel is widely used for reporting, especially analysis of financial information etc. The tool mostly used is the Pivot Table/|PivotChart to summarize the information. RSC used the PivotTable quite extensively for their CTC (Cost to Company) analysis.
Microsoft also uses a programming language, called VBA, to program all of the Office applications. We are going to learn a bit about the VBA language and Pivot Tables/Charts in this semester.
Creating Pivot Tables/Charts automatically:
Firstly you will have to work through the Pivot Table and Chart tutorial that I have downloaded from the Internet – it will be on the usual drive. This will show you how to create a Pivot Table from data given in an Excel “database” using the wizards available. An Excel “database” is actually just a long list of repetitive data captured in Excel – you will see how the Pivot Table is used to summarize the data into useful information. The word pivot indicates that you can play around with the information – for example you can see the total sales per salesperson per quarter as well as the yearly total for that salesperson. By just moving one field, you can pivot the information to show you the quarterly sales for all the salespersons as well as the total sales for all the salespersons for that quarter. I will explain in more detail in class; ensure to take notes.
VBA:
The second part is to show you how to program Excel using VBA – very similar to using VB.NET; BUT the big difference is the components you work with. In VB.NET you work with forms, text boxes, radio buttons etc. In VBA for Excel you work with workbooks, worksheets, ranges and eventually with Pivot Tables.
Macro:
A macro is an application (program) written in VBA. You can
Purpose statement:
The purpose of this term’s work is to enable you to create Pivot Tables programmatically from an Access database using VBA with Excel/Access.
Introduction:
Excel is widely used for reporting, especially analysis of financial information etc. The tool mostly used is the Pivot Table/|PivotChart to summarize the information. RSC used the PivotTable quite extensively for their CTC (Cost to Company) analysis.
Microsoft also uses a programming language, called VBA, to program all of the Office applications. We are going to learn a bit about the VBA language and Pivot Tables/Charts in this semester.
Creating Pivot Tables/Charts automatically:
Firstly you will have to work through the Pivot Table and Chart tutorial that I have downloaded from the Internet – it will be on the usual drive. This will show you how to create a Pivot Table from data given in an Excel “database” using the wizards available. An Excel “database” is actually just a long list of repetitive data captured in Excel – you will see how the Pivot Table is used to summarize the data into useful information. The word pivot indicates that you can play around with the information – for example you can see the total sales per salesperson per quarter as well as the yearly total for that salesperson. By just moving one field, you can pivot the information to show you the quarterly sales for all the salespersons as well as the total sales for all the salespersons for that quarter. I will explain in more detail in class; ensure to take notes.
VBA:
The second part is to show you how to program Excel using VBA – very similar to using VB.NET; BUT the big difference is the components you work with. In VB.NET you work with forms, text boxes, radio buttons etc. In VBA for Excel you work with workbooks, worksheets, ranges and eventually with Pivot Tables.
Macro:
A macro is an application (program) written in VBA. You can