Recursive Creativity LLC

RECURSIVE CREATIVITY LLC

Microsoft EXCEL

Content listed are those items I have developed, use, or have permission to use...all related to Microsoft Excel.

PLEASE NOTE:
A lot of the links to add-ins below are dead links. They will be unavailable until I have time to rewrite them for compatibility with Excel 2016. Sorry for any inconvenience.


Excel Add-ins


Below are add-ins for Excel. Please note which version of Excel each supports.

Recursive Creativity Dates Excel Add-in


There are several date related functions in this add-in.
  1. BONDINTDATE - Enter title (as text) of any USA Federal holiday and return Julian date value. Saturday, Sunday (Weekend) = Monday. Year is optional, defaults to current year if not provided.
  2. DATETEXT - For any given Julian date value, return the date written in text (English).
  3. DATETEXTDOW - For any given Julian date value, return the date written in text (English). Day of Week (DOW) at end.
  4. DOWDATETEXT - For any given Julian date value, return the date written in text (English). Day of Week (DOW) at beginning.
  5. DOWINMONTH - Calculate how many times a particular day of the week occurs in a given month.
  6. EVENTS - Enter title (as text) of USA celebrated Event that is not a recognized Federal Holiday and return Julian date value. Year is optional, defaults to current year if not provided.
  7. HOLIDAYS - Enter title (as text) of any USA Federal holiday and return Julian date value. Saturday = Friday, Sunday = Monday. Year is optional, defaults to current year if not provided.
  8. NDOW - Determine what date falls on a particular nth day of the week in a given month. Sunday = Week Day 1

Some notes on using a UDF from an Add-in with Conditional Formatting.

With Excel 2010, the HOLIDAYS and EVENTS functions can use the following to expedite entry:

  • Start typing =HOL or =EVE,
  • when =HOLIDAYS or =EVENTS appears, press "Tab" key,
  • when =HOLIDAYS( or =EVENTS( appears, press "\" key,
  •  
  • A list of Events and Holidays available will appear,
  •  
  • scroll down to highlight your choice,
  • then press "Tab" key again,
  • then press "Enter".

Top of Page

Microsoft Outlook Utilities Excel Add-in


There are currently two utilities in this add-in.
  1. Excel.pst ©
  2. Outlook Profiler ©

Excel.pst allows user to choose any profile in Outlook and for each folder found a separate tab in Excel will be created. On each tab will be a list of each e-mail item found and details about each. The subject, number of attachments and whether or not it was read are among the items listed.

Outlook Profiler provides many summarized details about a profile in Outlook. Provided will be a folder tree (list with indented subfolders) and the following details for each folder:

- Path
- Folder name
- The number of folders within that folder
- The number of e-mails in that folder
- How many unread e-mails
- The size of the folder (KBs)

To Do List:

  1. The compressed files available for download will eventually have two versions (files). Files ending in "c" have colored windows. They are currently available. Files ending in "v" (vanilla; color neutral) will be available in the near future.
  2. I plan to add at least one if not two more utilities to this add-in some time later this year.
  3. The add-in currently uses menus. I will post Ribbon versions for Excel 2007\2010 in the near future. However, I hope to post a version that incorporates both a menu and a Ribbon...the version of Excel will load whichever is applicable.

Current Issues or Caveats:

  • It would be best to run this from a blank worksheet. It deletes all sheets beginning with "Sheet". If a sheet already exists with the same name as an Outlook folder, it will delete the sheet and recreate it.
  • I added the option to or not to apply Printer Settings for Excel.pst. Not applying Printer Settings speeds up the process quite a bit. It might be faster to not apply the Printer Settings, run the process and then apply the Printer Settings manually by selecting all sheets.
  • I have only tested with Outlook 2007 on Windows Vista and XP. I have not made the switch yet to Windows 7 or Outlook 2010.

Top of Page



Unique Solutions


Inventory Dashboard
 
Problem:
Every week five large files containing Inventory data are received and you need to know which file contains the record and subsequent Inventory data for a particular part number.
 
Solution:
Excel 2007, 2010 Solution
Excel 2003 Solution
 
Directions:
Inventory Read Me
These directions are also included in each of the compressed files above.
Miscellaneous:
The hyperlink does not have to be in the Count column. However, Hyperlinks are in column D for this solution as this is a logical place for them.
 
I only remove the Hyperlink formatting because it makes for a more professional appearance when printing. The only purpose the Hyperlink serves is to create a link to unopened files so that the formulas will work.
 
As long as you have the Dashboard file open, you can change the Day backwards (20-24) and the data will update for each day you have run the above procedure. If you close the Dashboard file you will need to repeat the process for each day again.

Top of Page

Excel Videos


Some of these videos are my first attempt at using Camtasia Studio. Thus, the quality and narratives may improve as my I gain more experience using the program.

Depending upon your screen size and resolution, the entire video, in either format, may not appear on the screen when viewed in streaming format.

And yes, I do need to get a quieter keyboard!!!

Camtasia Studio and SnagIt are both products of TechSmith.


Autosum
Formatting Comments

Top of Page

Excel Freeware


Many of the items here are Freeware tools I have developed to automate certain tasks.

I have given credit to authors whose code or files I have used or modified. I have left untouched much of the code I have not authored.

Permission has been obtained for posting files that are modified versions from other authors.

If there are any objections to my posting any links or other content, please e-mail me with any related requests and, within reason, I will oblige.



Gantt Charts

These are Gantt Charts I used years ago that have been updated with some new features. I used the Weekly to show a high-level view and the Daily for details about various, tedious and time consuming or difficult tasks within projects.

Gantt Chart - Daily, 1 Year  Last Updated: JUL 9, 2012
 
Gantt Chart - Weekly, 3 Year  Last Updated: JUL 9, 2012

Top of Page


MP3 Manager - Windows Vista

This file was developed to provide details about .MP3 files and edit the metadata or ID3 Tag for the files. While the tool will currently list .WMA and .M4* (iTunes) file types, it will not edit the Metadata for them. Hopefully I will figure the file type specifications out and provide that functionality in a future release.

This file was originally developed on Windows Vista and has also been iused with Windows 7. Any feedback would be appreciated.

See Read Me.txt link below for more details and basic directions to edit metadata.

All Files  Last Updated: APR 28, 2012
 
Excel Files only     Excel 2003, 2007\2010  Last Updated: APR 28, 2012
 
cddbcontrol.dll     version 2.6.206.205  
 
Read Me.txt  Last Updated: APR 23, 2012

There are two types of data associated with .MP3 (audio) files. The underlying file properties are referred to as metadata. The last 128 bytes of an .MP3 file is the ID3 Tag. The first part of the ID3 tag begins with TAG. The remainder of the tag contains Artist, Album, Song Title, Genre and Track Number (not an all-inclusive list).

The following features are built into the MP3 Manager:

  • Get list of all .MP3, .WMA and .M4* (iTunes) files in a directory or folder.
  • Option to NOT recurse directories or search subfolders. Default is to search all subfolders.
  • List of files can be placed on default sheet or each folder can be placed on a separate sheet. Issue - duplicate folder names; no two sheets can have the same name. Duplicate sheets will be named Sheet#, whereby # is the next number starting at 1.
  • All data on active sheet can be deleted. If column headers get deleted they will be recreated each time you "Get MP3 Files".
  • All sheets, except default sheet, can be deleted. Your mulligan!
  • Metadata for a single file can be edited.
  • Metadata for all files can be edited.
  • ID3 tags for a single file can be edited.
  • I am debating adding the functionality of editing the ID3 tags for all files. It would write the metadata for each file to the ID3 tag.
  • Files can be renamed using the metadata.
Renaming files using the metadata only allows the following fields to be used:
  • Author
  • Album
  • Song Title
  • Track Number

Functionality also exists for selecting the order of each of the above. The track number was added because I have been told that is how iPads sort files? I do not have an iPad so I could not speak to this or even test it.


My to do list regarding future enhancements and features:
  • Figure out the technical specifications for the file type .WMA and, if possible, incorporate them into program to allow editing .WMA metadata.
  • Figure out the technical specifications for the file type .M4* (iTunes) and, if possible, incorporate them into program to allow editing .M4* metadata.
  • Determine logic and subsequent code to enable option to update all ID3 tags using the metadata.

Top of Page




Cube Metadata Query Tool

This file was developed to provide a mechanism for querying SQL Server Analysis Services (SSAS) Cubes (also known as Unified Dimensional Models, UDM) and provide examples on how to use the =Cube() functions in Excel 2007\2010.

MDXL - Cube Query Tool - 2007\2010     Excel 2007\2010  Last Updated: APR 14, 2012
 
MDXL - Cube Query Tool - 2007, No Examples     Excel 2007\2010; This file does not contain =CUBE() examples.  Last Updated: APR 14, 2012
 
MDXL - Cube Query Tool - 2003, No Examples     Excel 2003, This file does not contain =CUBE() examples.  Last Updated: APR 14, 2012

The "Examples" provide a guide to using the =CUBE() functions and require access to an Adventure Works DW 2008 cube. Otherwise equations will display errors.

The following metadata queries are available that provide data for components of the =CUBE() function equations listed below:

  • Dimensions
  • Measures
  • Measure Groups
  • Measure Group Dimensions
  • KPI's
For a given Dimension, queries provide metadata for:
  • Hierarchies
  • Specific Members: list of Members for selected Dimension
  • Dimension Metadata: all metadata for selected Dimension
Other Metadata queries:
  • Members: list of all Members for all Dimensions     This option not available for Excel 2003.
  • Metadata: all Metadata from a list of 30 different Metadata Tables

The Excel 2007, 2010 version of the file contains sheets with examples for each of the following =CUBE() functions:

  • CUBEKPIMEMBER
  • CUBEMEMBER
  • CUBEMEMBERPROPERTY
  • CUBERANKEDMEMBER
  • CUBESET
  • CUBESETCOUNT
  • CUBEVALUE

However, for the equations on each Example sheet to appear as values and not as errors, at least Read Access must be available for the Adventure Works DW 2008 cube. You must also create your own Connection to it from Excel.


Some other Adventure Works DW 2008 files and information are posted for a presentation I gave at the Houston SQL Server User Group.

Top of Page




Zipped Backup Lite © version 3.0x.bat

WinZip provides a command-line utility that allows you to WinZip a file, folder, or drive. I programmed, using Excel, a way to preset up to 28 different paths on any drive and utilize WinZip and the WinZip command-line utility.


The difference between the stand-alone version and the version that accompanies a Batch file is the name of the Command Prompt window.

I wrote the batch file because I did not know any other way of accomplishing what it does...it names the Command Prompt window! The name of the Command Prompt window, when open and running a backup procedure, tells you where you are in the processes if you have numerous buttons named.

Prior to incorporating the batch into the process, I would never know which process was running. When minimized and a process is running (Path is getting WinZipped up), the Command Prompt window in the Taskbar would tell me how far along the processes were. More specifically, which process was running.

The Batch file must reside in the same path as the Zipped Backup Lite Excel file.

The second zipped file does not rely on the batch file and the Command Prompt window is NOT named according to the button/process running.

The cost of external hard drives makes it more cost effective to just buy one and use a Synchronization program to duplicate or backup your data files. Microsoft provides an excellent, and FREE, tool for synchronization, SyncToy.


With .BAT File, Not Compressed
 
Zipped Backup Lite v3.07.bat.xlsm
Zipped Backup Lite v3.03.bat.xls
 
ZippedBackupLite.bat
(The batch file must reside in the same path/folder as the Excel file.)
 
Stand Alone, Not Compressed
 
Zipped Backup Lite v3.07.xlsm
Zipped Backup Lite v3.03.xls
 
.BAT Package, Compressed
 
Zipped Backup Lite v3.07.bat.zip
Zipped Backup Lite v3.03.bat.zip
 
ZippedBackupLite.zip
(The batch file must reside in the same path/folder as the Excel file.)
 
Stand Alone, Compressed
 
Zipped Backup Lite v3.07.zip
Zipped Backup Lite v3.03.zip

Top of Page




Excel.pst © version 2.0x

This tool was developed to facilitate keeping track of e-mails in various categories.

Whenever I find a good tip or trick related to a subject I send myself an e-mail with the details. An example of the subject line is as follows: Tips - Windows XP - How to create a desktop shortcut to enable screensaver.

I file the e-mails by Category in separate .pst files. I do not use Archive for this process. This tool is designed to list all .pst files in a given folder and provide the option to open any number of files selected individually. I have over 30 .pst files for various categories such as Tips-Windows, Tips-SQL, Tips-Office, Downloads-Windows, Downloads-Office, etc.

These e-mails are similar to how I make note of Freeware I have downloaded.

This tool will provide a list of all e-mails by Subject, provide number of attachments, if any, indicate whether each e-mail has been read, and provide the number in order received.

More specifically, point to an Outlook folder and for each folder found, a new Excel tab will be created and the aforementioned fields will provide details of every e-mail in each folder.

Some options available are:
  • List the e-mail in Excel tabs, to text files on root directory, or both.
  • Delete all previous tabs by default when starting the recursive search process.
  • Include Path with file name, and
  • Turn on or off attachment counts.

These files have been removed. It is now a utility in the Excel add-in, Recursive Creativity Outlook Utilities, posted above.


Top of Page




Outlook Profiler © version 2.0x

This tool was developed to provide an outline of any Outlook folder structure.

This tool will provide a list of all folders and various details about each folder: number of e-mails, number of unread e-mails, and the size of the folder (MB's).

These files have been removed. It is now a utility in the Excel add-in, Recursive Creativity Outlook Utilities, posted above.


Top of Page




Network Info

This application returns the logged on User Name, Server, Domain, list of all Network Groups the logged-on User Name belongs to and a list of all IP Addresses currently in use by PC. I have used the Network Groups (Active Directory) for security/access in various applications/spreadsheets.

Thanks to Rob Bovey, Application Professionals for permission to use his ideas and code. Rob's books are highly recommended:

Top of Page

Excel Resources


Because of the number of resources available via the internet, I only keep a short list of resources I use, books and websites. They can be found on the Excel Resources page of the Excel Users Group.

The .PST files (Microsoft Outlook archive files), which are compressed files, contain e-mails from various Excel related subscriptions available via e-mail. For each year there are Excel User, Excel Developer and Yahoo User Group folders.


Other than Yahoo! tips, the main source of the Excel Tips in the above files come from the Subscriptions listed below.

Tips List Subscriptions:


Top of Page