Vishesh's blog

Vishesh's picture

Export spreadsheet data to Access

Following macro can be used in Excel VBA to export Excel data into Access.

Sub TestMacro()

Call ExcelToAccessTransferSpreadsheet("G:\ExcelExperts\ExcelAccessTest.mdb", "DBTestTbl", "G:\ExcelExperts\Test.xlsm", "Sheet1", "A1:C8")

End Sub

Sub ExcelToAccessTransferSpreadsheet(strDBPath As String, strDBTableName As String, strExcelFilePath As String, strSheet As String, strRange As String, Optional blnClearTableBfrUpload As Boolean = True, Optional blnDropTableBfrUpload As Boolean = False)

'Should have access on the system
'Creates a new table in Access if not found

Vishesh's picture

Cell Flash on selection

Call the following code from Worksheet selection change event. This will highlight the cell for a second and then change back the color of the cell to its original.

Vishesh's picture

Fuel economy and savings comparison calculator

Using the attached Excel utility you can compare various fuel options before going for a particular vehicle.

FuelEconomy.png
Vishesh's picture

Solution to Multiline Text in Cell

Solution to the Forum Question in url
http://excelexperts.com/multiline-visible-text-cell

After much R&D and suggestions from the people on Forums through LinkedIn I have come up with something like in the attachment...

Vishesh's picture

Custom Formula for Final selling price

Download and see the formulas used in attached spreadsheet...

Answer to question asked in
http://excelexperts.com/custom-formula#comment-3695

Vishesh's picture

VBA Language Convertor

Here is a simple function to convert a text from one language to another. Call the function with the required parameters...1. text to convert, 2. source language, 3.
Vishesh's picture

Color chart series based on color pattern in a range

Download the attached utility to color the chart series/categories according to the specified color code. Comments have been added the the code.

Color Chart Series

Vishesh's picture

Extract Data from Web (URL)

Here is a small piece of code to extract data from a web page.
Vishesh's picture

Solve Linear Equation

Here is a small VBA code to get the result of an equation which have one variable.
Vishesh's picture

Linked List Hierarchy extraction

The following code filters a list of employee falling under the select employee in a hierarchy. Download the attached file and see how it works.

Employee Hierarchy

 

 

Vishesh's picture

Invoice Tracking Utility

Here is a small Invoice Tracking Utility that simply logs the invoice details from various invoice templates. In the attached Excel file there is a 'Control sheet' to setup the utility, 'Invoice #s' sheet where log is created and 3 sample invoice templates where you enter invoice details. As you save or press Ctrl+S in your Excel file it logs the invoice details in the Invoice sheet. In Control sheet there is also an option to enable putting date stamp in file name while saving by turning this option TRUE or FALSE.

Download the attached sample file and try it yourself.

Vishesh's picture

Pivot from CSV

Attached is small utility to create a Pivot table from a CSV file. There is a button provided on the click of which you are asked to choose the csv file and then the target cell/range where you want to place your pivot.

This is the macro code working behind the scene...


Sub CreatePivotTableFromCSV()

 

    Dim strFileName     As String

    Dim strFilePath     As String

    Dim rngTarget       As Range

    

Vishesh's picture

Sort By Color

Excel doesn't provide anything to sort on color. Here is a customised code snippet to achieve the same. Download the attached Excel file to to see how to implement color sorting in Excel range.

Custom Sort


 

 

Vishesh's picture

Solution to forum question

Solution to forum question on url:

http://excelexperts.com/help-me-please

Download attached file and see the formulas. I have added another column for sheet name.

Vishesh's picture

Date Stamped Report File

Simple utility to save the file with current date stamped in the file name.
Vishesh's picture

Scroll Large Data

Download the attached excel to see how you can scroll large amount of data in the visible area of the screeen.

Scroll Large Data

Vishesh's picture

Answer to Forum question

Refer attached Excel file for answer to forum question on
http://excelexperts.com/creating-2nd-list-dependent-1st-list

See data validation criteria conditions on Cell A2 and B2. Also see Named ranges created (dynamic named ranges can also be used here)

Vishesh's picture

Task Management Utility

Here is small Task Management Utility with facility to view Reports. There is control sheet from where you can set the mandatory fields and fields that you want to show up in Entry sheet and Report sheet.

Give it a try and give your feedback and suggestions.

Task Management

Vishesh's picture

UK Tax Calculator (Simple & Comprehensive)

Download the attached UK Tax Calculator Excel file having simple and comprehensive tax calculator.

Simple Tax Calculator:

Simple Tax Calc

Comprehensive Tax Calculator:

Comprehensive Tax Calc

Vishesh's picture

Leave System

This is a small Leave system utility. Download the attached zip file and extract excel and access file from it. This can be used in a multi user environment as well by keeping the access database at a commonly accessible location and distributing the excel file to all users. In the access database there is a table 'M_LeaveTypes' from where you can maintain the Leave Type and Max Carry over for each type of leave. In this system an user is identified by his Windows login id.

Vishesh's picture

Multiple Choice (Questionnaire/Survey) generator

Here is an utility to generate a simple multi choice questionnaire/survey (as you wish to use it). The survey response files can be collated using the collate responses button and selecting the created files

Multi Choice

Vishesh's picture

Small Shop Utility (for mid-sized shops)

This is a small utility to speed up the process of buying and selling between a Shopkeeper and a Customer. There are two sections (excel files) of it - Shopkeeper and Customer.

The Shopkeeper file as shown below remains with the shopkeeper where he can add/delete/modify items (Click Manage Items) and prices in the stock. 

The Update Customer copy button allows you to choose the other customer copy provided to be updated with the updated items and prices. This updated customer copy is then emailed/sent to the customer.

Vishesh's picture

Sorting on Custom Sort Order (VBA)

Following piece of code sort the data based on custom sort order. You can provide your own sort order in a separate table. For illustration, download the attached file.

Custom Sort

 


Sub TestCustomSort()

    Dim rngSortOrder    As Range

    Dim rngSortValues   As Range

    Dim arrSortOrder

 

Vishesh's picture

Sort on 'n' no. of Fields (VBA)

Copy-Paste the following code in a general module and run. Alternatively, download the attached file to see how it works.
Vishesh's picture

Filter records using arrays (VBA)

Put the following code in a general module and run. You can download the attachment as well to see how it works.


 

Sub TestIt()

    Dim rngTgt As Range

    Dim arr

 

'Target where you want to see filtered data

    Set rngTgt = Sheet1.Range("J2")

    rngTgt.CurrentRegion.Offset(1).ClearContents

    

'Calling function with parameters

Vishesh's picture

Cell Content Change History in Comments

Copy the following code in Thisworkbook module.

This will record any cell change in the cell comment. There is a constant at the beginning of the code module; you can set the number of records in comments (history) to be maintained. Specifying 0 means no record limit. This applies to the whole workbook.

Const gc_intMaxCmtHistory As Integer = 5 'Max Comments History allowed

                                         'Change it to 0 to allow n no.
Vishesh's picture

Simple Multiple Source Pivot

Follow these steps to create a pivot table from multiple sources (same of other file). See attached example file.

Steps :
1a In Excel 2003 open the PivotTable and PivotChart wizard by choosing Data -> PivotTable and PivotChart Report.
1b In Excel 2007, press Alt+DP, then type P to open the wizard as there is no equivalent menu option.
2 Choose Multiple Consolidation ranges
3 Open file B as well
4 Choose relevant page field option
5 Simply select and add the ranges (highlighted cells) from this and other file. You can choose to have as many files (A+B)
6 Select New Sheet & Finish

Vishesh's picture

Answer to Blog

This is the solution to blog on url
http://excelexperts.com/help

Please see attached xl file.

Vishesh's picture

Export to and Import from Tab delimited Text file

Following are two functions to Export to and Import from Tab delimited Text file.
Vishesh's picture

Text/Number Validation in Text Boxes

Copy the following code in a general module and call it from any of the textboxes' keypress event.
Syndicate content