Following macro can be used in Excel VBA to export Excel data into Access.
Call ExcelToAccessTransferSpreadsheet("G:\ExcelExperts\ExcelAccessTest.mdb", "DBTestTbl", "G:\ExcelExperts\Test.xlsm", "Sheet1", "A1:C8")
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
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.
Using the attached Excel utility you can compare various fuel options before going for a particular vehicle.
Solution to the Forum Question in url
After much R&D and suggestions from the people on Forums through LinkedIn I have come up with something like in the attachment...
Download and see the formulas used in attached spreadsheet...
Answer to question asked in
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.
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.
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...
Dim strFileName As String
Dim strFilePath As String
Dim rngTarget As Range
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.
Solution to forum question on url:
Download attached file and see the formulas. I have added another column for sheet name.
Download the attached excel to see how you can scroll large amount of data in the visible area of the screeen.
Refer attached Excel file for answer to forum question on
See data validation criteria conditions on Cell A2 and B2. Also see Named ranges created (dynamic named ranges can also be used here)
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.
Download the attached UK Tax Calculator Excel file having simple and comprehensive tax calculator.
Simple Tax Calculator:
Comprehensive Tax Calculator:
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.
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
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.
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.
Dim rngSortOrder As Range
Dim rngSortValues As Range
Put the following code in a general module and run. You can download the attachment as well to see how it works.
Dim rngTgt As Range
'Target where you want to see filtered data
Set rngTgt = Sheet1.Range("J2")
'Calling function with parameters
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.
Follow these steps to create a pivot table from multiple sources (same of other file). See attached example file.
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
This is the solution to blog on url
Please see attached xl file.