DataWright Information Services
Consulting Update As I have now taken a full-time job I am no longer accepting new consulting work, but will continue to support my existing clients. Do you find yourself doing repetitive tasks that stop you doing your real job; copying, pasting, importing text from other documents but having to extensively reformat them before they […]
Using dependent combo boxes on continous forms
Dependent combo boxes on continuous forms Download the sample database (32,685 bytes) It’s not difficult to build two combo boxes where one shows a filtered list dependent on selections in the other combo box. There is an article on this site showing how to do it on a form which shows one record per screen. […]
Importing a large dataset into Excel — DataWright Information Services
Importing a large dataset into a single worksheet Sometimes you will need to import a large number of records from a database into Excel. If that dataset exceeds 65536 rows, you can’t import it into a single continuous table, so you need to try alternatives. They could include any of the following: Don’t import the […]
Filtering Excel data using ADO — DataWright Information Services
Using ADO to filter data in an Excel workbook The idea for this example came from a question on mrexcel.com, a very active Excel bulletin board. Download the sample file (19366 bytes) There are several ways to filter data in Excel. The obvious options are to use the inbuilt AutoFilter or Advanced Filter. They work […]
Clean up trailing zeros in dates when use TransferText
Cleaning up dates in text files You can use the TransferText command in Access to import and export text files. A common example is writing out CSV files to be imported into another database system. It’s quick, and you can build output file names dynamically, but there is one major drawback. If you export dates […]
Using dynamic ranges — DataWright Information Services
Using dynamic ranges in Excel Download the sample file (4746 bytes) Dynamic ranges are incredibly useful where the amount of data in your worksheets keeps changing, and you need ways to analyse all or part of that data in charts or pivot tables. Here are a few possible applications for dynamic ranges: Plotting series of […]
Selecting cells with Edit Go To Special — DataWright Information Services
Simplifying selections with the Special Cells dialog Often you will be faced with the need to select all formulas in a worksheet, or clear all numbers from a data entry area, or select all blanks and fill them with a value or formula. All of these jobs are straightforward if you use Excel’s Go To […]
VBA Code for updating a Word document with Excel data — DataWright Information Services
Code for Word Bookmark Article This code is provided as a single source for pasting into a code module behind a Word document. The descrption of the code can be found in this article. Sub RefreshAllTables() ”============================================================================== ”Purpose: To refresh the current table in a Word document with new data from ” the corresponding range […]
Creating sheet-level names in Excel
Using sheet-level names in Excel workbooks What are sheet-level names? A sheet-level name can refer to a single cell or range of cells, just like a standard (workbook-level) name. The difference is that the scope of the sheet-level name is limited to the sheet to which it belongs. This means that you can use the […]
Many to many data entry using a popup form — DataWright Information Services
Many-to-many data entry using a popup form The idea for this example was inspired by someone who wanted to build cascading combo boxes, with a twist. Although the second combo box was to show items based on the selection from the first, there were times when they needed to be able to select from a […]
