Check Access table(s) from Excel using Automation - Updated Code

Thursday, January 31st, 2008 24 views

Here is the revised and completed code that I posted yesterday. The DAO declarations are moved outside the Sub procedure and the 'Set' statements are outside the function loop. Also, the table & column headers are now passed as arguments to the function, making the code more compact. The speed ...

Posted in Access, Excel, VBA | No Comments »

Check Access table(s) from Excel using Automation

Wednesday, January 30th, 2008 44 views

Finally, with some help from the good folks over in the microsoft.public.access.modulesdaovba newsgroup, I was able to complete my code to search an Access database for some information stored in an Excel worksheet. This code will cycle through a list of numbers in column B, starting in cell B2, then check ...

Posted in Access, Excel, VBA | No Comments »

Counting Unique Occurrences in an Excel Spreadsheet

Monday, January 28th, 2008 26 views

This formula, entered as an array (Ctrl-Shift-Enter) in a single cell, will show you if there any duplicate entries in a given range (in this case, A1:C100). It is wrapped in an IF function to provide a friendly message; a kind of in-cell error handling, if you will. I usually ...

Posted in Excel, Formulas | No Comments »

Advanced Topics In Excel Training Class

Saturday, January 26th, 2008 31 views

My offer to teach Excel training classes at my office has been accepted. The class is tentatively titled "Advanced Topics in Excel" and will cover the following topics (among others TBD). I will also be including code and formulas from this site. Counting/Filtering: SUMPRODUCT, COUNTIF, SUMIF Working with dates: INT/MOD Text functions: RIGHT, LEFT, MID Misc functions: HYPERLINK And ...

Posted in Excel, Formulas, Tutorial, VBA | 1 Comment »

Formatting Zip+4 Codes

Friday, January 25th, 2008 5 views

If you have a column of zip+4, this macro will remove the suffix. Simply highlight the cells in question. It will skip any zip codes that don't have the suffix. It uses the Left$ string function which is more efficient than the standard Left() and always returns the first 5 ...

Posted in Excel, VBA | No Comments »