Image of self in Comic Book effect, via Apple's Photo Booth application.

Code || VBA, SQL, X/HTML

Home

Code: Excel, Access, SQL, Other

Sites and Groups

Administration

Code/Modules/XLA
AND OR

Module Name Description
ZipCollection.cls.txt Access class module, although should work in Excel with minor modification, that automates zipping files, with parameters for the following:
    Properties
  • Output Name
  • Output Path
  • File Names (as single dimension array or single item)
    Methods
  • Create Method
  • Add Method (accepts full path of single item)
GetDomainAccounts.zip An Access DB that queries the domain for object, e.g., user ID's. Requires that you modify code to specify a domain The code can be run from the supplied form or directly, and truncates and appends to the supplied table. Please note, this was last edited in Access 2007, and if you are using an earlier version, you will need to correct broken references.
Access_Notes_Send.txt A function that sends (no prompt) a Notes e-mail, based on passed values for subject, recipient, body, and attachment.
CImportObject.cls A class that encapasultes an import process, although somewhat tied to specific process, easy modifiable to decouple and/or modify it as needed. In general, the process requires a source data file, an import type specified, staging and master tables, a report date field, a level1 field (a coupled requirement that can be removed), and a cleanup bit for dirty imports that can be turnd on or off.
ChangeLinkedDB_Excel.txt Changes the DB referenced in data refreshes, provided that all the linked queries are the same DB.
ASCII.xls Listing of ASCII character representation by number, as well as appearance if formatted as W*Dings in Excel.
MBackup.txt Access code to backup all modules, forms, and queries, as well as create tables of table features; code checks for tables' existence and creates if necessary. Dynamically creates backup folder in the folder of the Access MDB.
MScreenResolution.txt A very simple module to return the screen resolution of the current desktop.
MResizeForm.txt Form Resizer For Microsoft Access (By Jamie's Software)
The Form Resizer is a Microsoft Access MDB (Microsoft Database) that allows you to install a module in your own Access MDBs that can resize your program's forms to fit the current screen resolution. It is intended for Access developers with basic Visual Basic for Applications (VBA) coding knowledge. Works with Access 97, 2000, 2002 and 2003.
clsFunction_Workday.txt A simple class function to provide weekday calculation, native to Excel, in Access.
Installation.zip in SMS, a simple graphical interface for installations. Once compiled, one only need modify the INI file with application information, path information, and EXE name. The EXE and INI should be located in the same directory.
Access_MkDirMulti_Pearson.txt A module which replaces the MkDir function, such that it verifies and creates an entire path; MkDir only creates a new directory on an existing path. Please note that this module was create by Pearson Consulting for Excel, and I simply modified it to work in Access.
Excel_Fx_NextWorkday.txt Function that can be used to calculate target dates, taking into account a date and hours/minutes of work, compensating for weekends, but not holidays:
  • Function Name: Fx_NextWorkday
  • Variables:
  • StartDate, a date
  • Increment, number of hours or days
  • IncrementType, hours (1) or days (2)
  • Return: Date
It populates most of the Original Target Date column, I, and is built into the spreadsheet. Also, It might be inaccurate over longer time intervals, but it works in this context.
MGlobals.txt The entire list of Excel 2003 constants and values, useful when late-binding Excel in Access automation.
Analyst Toolkit.xla An XLA that exhibits a floating toolbar with numerous built-in functions, including numeric formatting, special print formatting via screen selections, and a drop-down menu with parsing options.
Access_TableExists.txt Verifies if a table exists; returns boolean for success; useful when users can delete files, preventing a process from executing if a table does not exist.
Access_QueryExists.txt Verifies if a query exists; returns boolean for success; useful when users can delete files, preventing a process from executing if a query does not exist.
Access_Excel_Sort.txt From Access, manipulates an Excel workbook - it defaults to the first tab but code can be improved to specify a worksheet object or a specific tab - sorting a worksheet object on a passed field and a sort order.
Access_Excel_FindRange.txt From Access, finds a text value in Excel and returns its range (cell). The function is passed a worksheet object, a text value to find, and an optional row to search. If no row is specified, it searches the used range of the specified worksheet.
Access_VerifyTablesAndColumnsExist.txt Verifies that columns in a table exist, created to verify column heading on imported Excel spreadsheets. The function receives a table name as string, and comma-delimited column names as string, verifies existence of table, parses the column string into array, and then tests for each column, returning TRUE if all exist, else False.
Access_CharacterStripping.txt Procedures (2) to compare strings in Access, either by stripping all non-numeric characters, or by stripping all non-alpha characters. Useful in queries when having to compare strings that are functionally the same, but typed differently, e.g., with hyphens or commas.
Access_ShutdownTimer.txt Primarily a timer, and paired with other code can be used to send reports, shut down a database, and/or perform other system activities.
StoredProcedure_Get.mdb.zip Access MDB to backup stored procedures. Code checks for table's existence and creates if necessary. Requires modification of module level variables to fit your environment. Creates both a date-stamped folder of individual SQL files, as well as an export to a single HTML document.
StoredProcedure_StringToTable.sql Converts strings to tables, necessary with long strings that need to be incorporated into dynamic SQL, and this circumvents the 128-character limit.
StoredProcedure_CrossTab.sql Stored procedure to create crosstabs; extensive documentation on parameters inside file.
Access_SP_Backup.txt Access code to backup stored procedures. Code checks for table's existence and creates if necessary. Requires modification of module level variables to fit your environment.
Excel_AutoSum.txt Sums to each column of a Excel range (UsedRange), but requires passed variables for the starting column and starting row.
Excel_AutoSave.txt An AutoSave feature for Excel, done entirely in code. Includes a procedure for adding a dropdown menu to a toolbar.
Excel_PivotChart_Drilldown.zip Excel Chart DrillDown: Captures Shift+Click, enabling drill down into a pivot chart. It has three (3) components, one a class module, one contains code for ThisWorkbook, and one is a regular module that sets and resets the event to the current chart. I've trimmed out anything specific to the client, but you still to debug and customize it to fit your needs.
Excel_SQLLogging.txt In conjunction with a backend database and stored procedure, modularizes logging in Excel workbook.
Excel_Recordset2Array.txt Executes a recordset and returns values to array. Internally, the array is transposed, then transposed again, to return the array in its expected recordset-like structure.
Excel_NewSheet.txt Worksheet event forces naming of new sheet or chart. Used with global variable, such that if the global variable is "", the procedure executes, otherwise it skips.
Excel_IllegalCharacterReplacement.txt Replace illegal characters: Function accepts string and replacement value; replaces string with value for a set of illegal characters, i.e.,[,],\,/,:, ', ?, and *.
Excel_FormatRange.txt Formats a range: supply a range and it formats to the end of the used range, meaning converts text to values and justifying.
Excel_Validation.txt Multiple functions that validate data types.
Access_ExportData.txt Procedure to export all data tables as Excel workbooks. includes a procedure to format each workbook. Note, that the internal path is fixed and will need to be modified to suit your needs, either as a new path, or as a recipient of a more dynamic, form value.
Excel_PivotTables.txt Two example procedures for creating pivot tables in code. One simply create a pivot table with an internally-specified worksheet, while the other works as a function, accepting various workbook-related objects, and returning a boolean to indicate success or failure.
Euro-Live.xla An XLA for Euro-US conversion. Built several years ago, the code could be improved, but includes a menu, command bar, internet data retrieval, and special functions to convert currencies in ranges/cells.
ControlLimits.xla An XLA that adds additional Operations Management statistical functions to Excel:
  • Process Capability (4)
  • Proportion Defective (4)
  • Number Defective (3)
Built several years ago while I was an MBA student, it might lack some controls, i.e., validation.
perl_DirListing.zip Perl script to list the files and file attributes of a directory tree. Requires perl installation. Code is acquired, not my own, although I have edited it for work.
Excel_Documentation.txt Exports Excel modules and forms as text to a hard-coded subdirectory. In conjunction with a form, the path can be dynamic, and provide additional source code backup.

Please note that you will need to modify macro security to enable "Trust access to the Visual Basic project" via the menu (Tools | Macro | Security... | Trust Sources).
Excel_FixText.txt Fixes numbers stored as text, a common problem with Access exports to Excel, by copying the value back into the cell, avoiding formulae.
Excel_FileExists.txt Simple function to test for a file's existence.
Excel_Printing.txt Used as a function, receives an optional print orientation, and formats a worksheet for printing; provides a consistency of appearance, with coloring, header and footer. Returns a boolean.
Excel_Formatting.txt A collection of procedures to format numbers; previously included in an XLA to reduce effort in using functions repeatedly.
Excel_Parsing.txt A collection of procedures to parse text; previously included in an XLA to reduce effort in using functions repeatedly.
Access_ADO_Generic.txt Access module using ADO to bring data back from server (Sybase, but modifiable to any major server brand) to populate a predefined table.
Access_GetUserName.txt Basic module using Windows API to get the username (UserID or login ID) of the currently logged in user.
VBScript_GetLogin_Loop.vbs.zip A VBScript that requests UserID information from a domain, which you will need to specify. Please note, this file is stored as a ZIP file.
Access_Convert_Postgresql.txt Exports Access data into form that can be inserted in PostgreSQL database. Not my own code, but provided by third party.
Access_TransposeArray.txt MS code to transpose an array, presumably used in conjunction with GetRows.
Access_SQLExecute.txt Instead of executing SQL literals in code, a function that receives an SQL string, the executes and logs it. Includes a module to deal with strings containing quotes.
Access_VerifyStrings.txt Two modules to verify strings, e-mail and URL. Works in either Excel or Access.
Access_Logging.txt Instead of executing SQL literals in code, a function that receives an SQL string, the executes and logs it. Includes a module to deal with strings containing quotes.
Access_ExportTables.txt Exports all internal tables as Excel spreadsheets. Has hard-coded internal output path, which can be modified, as well as recoded to be directed via form.
Access_SendReport.txt Sends e-Mail via Outlook. Written as function, so is passed recipient string and memo body. Can be modified to suit.
Access_FormsClose.txt Function to test for the existence of a form. Useful to test if a form is open, so as to close it.
ADOX Table Build.xls An Excel workbook to reduce the redundancy in using ADOX, when building tables and setting column constraints.
Excel_OutlookEMail.txt Late-bound Outlook E-Mail from Excel
Access_UserID.txt Gets the UserID from the Windows using an API call - it is recommended that none of the syntax is modified - as well as a function to evaluate the returned value.
Excel_CommandBar.txt Displays the basics of building an Excel command bar, as well as how to delete it. This can be part of a larger XLA or XLS, with workbook specific functions.
Access_ExportExcel.txt Three (3) procedures to export recordsets to Excel, one that copies the recordset en masse, and the other that copies on a cell by cell basis. This file includes two (2) procedures that format Excel, either when called from within other procedures, or alone from a form.
Access_ExcelEnumeration.txt Enumerates Excel values. Useful when Excel is late bound in Access, and many methods and properties are not readily available.
Access_Documentation.txt Exports Access components to tables, in the case of queries and tables, or subdirectories, for modules, to a hard-coded subdirectory. Also includes function to enumerate Access data type values as strings. In conjunction with a form, the path can be dynamic, and provide additional source code backup.
Access_AlterText.txt Code-based ALTER TABLE statement. Pass it the table, the field, and the type, and it executes an SQL statement to modify the field's table.

Thursday || 04:11 Z || 2008-08-28 Valid XHTML 1.0 Transitional Valid CSS