VBA Application with SQL Database

Excel VBA based Application with SQL database

In this article, we have created a VBA Application with SQL Database. We have used ADODB connection to connect the database. In this application we have created Insert, Update and Delete functionality and have used VBA list box to display the data. Multiple user can work on this application.   Below is the code to create the Table in SQL database Create Table TBL_Customer( CustomerId int identity(1,1), CustomerName nvarchar(50), CustomerAddress
Bulk File Creator

Bulk File Creator in Excel VBA

In this article, we have created an automation to create the bulk files for Excel, Word and PDF. Here we have used a school mark sheet template. this automation can be used for bulk Invoice creation or any other bulk file creation. Put the folder path wherein you want to create the files- [caption id="attachment_20069" align="aligncenter" width="619"] Bulk File Creator Automation[/caption] Mark sheet template – [caption id="attachment_20070" align="aligncenter" width="872"] Mark
Export All Worksheet as PDF

Export all Excel Worksheets in separate PDF files

In this article, you will learn how to Export all Excel worksheets in separate PDF files. In this macro we have used FileDialog in VBA to get the folder path. Below is the macro code which you can copy to your personal macro- Option Explicit Sub ExportAsPDF() Dim Folder_Path As String With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select Folder path" If .Show = -1 Then Folder_Path = .SelectedItems(1) End With If Folder_Path
Goal Seek Using Macro

Goal Seek in Excel using VBA

In this article, we have used a Goal Seek to get required minimum sales to meet the Sales % target using VBA. You can use this method for other purpose also. Input your number in left table and yellow highlighted field and click on GetMinimum Sales button. Watch this step by step video to Goal Seek using VBA- Below is the macro code – Option Explicit Sub Goal_Seek() Dim sh
Pivot Table Tips
Excel Tips and Tricks

10 Useful Pivot Table Tips

Pivot Table is useful and power full feature available in Excel. Learn the below given 10 useful Pivot table tips in Excel- Fix Column Width Running Total Ranking Move Pivot Table Report Filter Pages Pivot in PPT Conditional Formatting in Pivot Table Filter Selected Items Tabular format Repeat Labels Watch the below given video to learn all above given tips: Click here to download practice file-  
3D Table in Excel
Charts and Visualization

Data Driven 3D Table in Excel

In this article, we have designed a data driven 3D table. We have used Linked picture and excel shapes to create this table. You can use this in your business presentation or business dashboard. We have used Conditional Formatting to display the icon set. Below is the snapshot of 3D Table- [caption id="attachment_19434" align="aligncenter" width="1365"] 3D Table in Excel[/caption] Click here to download this practice file.   Watch the step
Dynamic Conditional Formatting
Charts and Visualization

Dynamic Conditional Formatting in Excel

In this article, we have created Dynamic Conditional Formatting in Excel using Form Control Option Buttons. Conditional Formatting is changed in Heat Map (Color Scale), Data Bar and Traffic Lights according to the selection of Option button. While selecting the Heat Map option button- [caption id="attachment_19032" align="aligncenter" width="776"] Heat Map Conditional Formatting[/caption] While selecting the Data Bar option button- [caption id="attachment_19033" align="aligncenter" width="774"] Data Bar Conditional Formatting[/caption] While selecting the
Subtotal in Excel
Excel Tips and Tricks

Subtotal feature in Excel

In this article, you will learn about the Subtotal feature of Microsoft Excel. Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet. To use the Subtotal feature, make sure your data is properly sorted. Here we have taken the example of Student wise marks