Help

Visual Basic for Applications
programming services for
Microsoft Access and the Office suite

Contact

VBA and Access

An Access database to house and manage a set of data, and Visual Basic for Applications to control Access! What a powerful combination!

Access is designed to manage one or a few simple or several or many inter-related tables of data—and it does so very well. But it does have its limitations. VBA imparts manifold increases in the range and scope of what Access can accomplish.

Capabilities by Example

Take one of our productions, Docudex®, for example, a database custom made for the Denver Office of City Clerk. It constitutes an index of ordinances, council bills, etc. City Clerk files and references its ordinances primarily by ordinance number and series (year), e.g., Ordinance 101, Series of 2002. If a customer requests an ordinance by such reference, no sweat; the identification is definitive and the document is produced without need of any further information. If the ordinance number is not known and request is made by, say, subject matter and perhaps an approximate passage date, still no sweat. Criteria easily can be applied to a search and the index records of a few candidates are retrieved. The display includes the ordinance numbers, descriptions, etc. A very useful function has just performed an essential task, but that sort of data retrieval is nothing extraordinary for Access. (But how did they ever manage with paper and pencil?!)

However, if the customer is making request by phone, and, if at the click of a button a PDF of the ordinance appears in his email before he can even hang up, something special has happened. Access did its job by retrieving the index record. From there, Visual Basic for Applications takes over and performs the following:
 

	Prompts for the customer's eddress
	Locates the related PDF on a network drive
	Counts the pages in the PDF
	Calculates the per-page charge
	Creates a Word session
	Composes a Word invoice identifying the document and stating the charge
	Creates an Acrobat session to convert the Word invoice to a PDF
	Quits the Word and Acrobat sessions
	Creates an Outlook session
	Composes an email message including the customer's eddress, a relevant subject line, and a hyperlink to the Adobe Acrobat Reader free download, attaching the two PDFs
	Quits the Outlook session
	Saves a copy of the PDF invoice to a network folder
	Writes the salient transaction info to another database for subsequent reconciliation

Not only is the customer wowed, but the clerk barely has to lift a finger. Power. Efficiency. Convenience.

And that's not at all all. Other functions perform feats equally impressive, if not more so. At the click of another button, Docudex® populates itself by parsing, formatting, and importing into its tables the weekly ordinances that originate as City Council agenda Word documents. Yet another button takes that same agenda, creates a copy reformatted yet again for publication on the city's website, and goes on to post it there. See that document here. If you've got the time, expand the bullet below for exhaustive detail of the action executed by VBA.
 

Publish Agendas Determine operating system and file structure. Procedure must run under Windows 98, NT, or 2000, with network or local folders. Create Word session. Copy publication agenda Word document from origin folder to special DenverGov files folder elsewhere on network or comparable local drive. Apply formatting for DenverGov, such as Verdana font, size 7, etc. Convert council bill and ordinance labels to hyperlinks to full-text, signed-and-sealed .pdf's of those documents found elsewhere on DenverGov. Use Microsoft Office 2000 HTML Filter 2.0 to convert, export, and save Word document as compact Web (.htm) page—devoid of extensive declarations Word normally includes in HTML code when it saves document as .htm—in same network/local folder (Web page file can be viewed offline). Quit Word application. Invoke WordPad. Open Web (.htm) file in WordPad, revealing HTML code (Notepad limited to 64K on Windows 98). Edit HTML code to remove hyperlink formatting; thematic DenverGov hyperlink color will not be overridden. Save WordPad content as text (.txt) file of HTML source code in same network/local folder (if anything goes wrong or HTML code for some reason need be reposted later, and just on GP, code is saved and available). Exit WordPad. Open new browser window. Navigate to DenverGov editor login page. Log in. Navigate to file management entry page. Find and click entry link. Surf to files list box. Select previous week's previous publication agenda file. Surf to click delete button. Dismiss delete confirmation popup. Surf to click add button. Surf to upload path text box. Compose and supply network/local path of current week's current publication agenda file. Surf to click upload button. Surf to find and follow links to edit current publication agenda template page. Surf to body text box, select entire contents, overwrite with include statement composed to specify current week's current publication agenda file. Surf to click submit button to submit content, dismiss submission acknowledgement popup. Surf to find and follow links to edit previous publication agenda template page. Surf to body text box, select entire contents, overwrite with include statement composed to specify current week's previous publication agenda (previous week's current publication agenda) file. Surf to click submit button to submit content, dismiss submission acknowledgement popup. Surf to follow links to main editor page. Find and follow submitted content review and approval link. Find and follow legal notices link. Surf to find and follow links to review current publication agenda template page submitted content. Surf to click approve button and dismiss submission acknowledgement popup. Surf to find and follow links to review previous publication agenda template page submitted content. Surf to click approve button and dismiss submission acknowledgement popup. Surf to find and follow links to new content publication page. Surf to find and check legal notices check box and to click publish button. Surf to click OK button. Navigate to live current publication agenda page on DenverGov to view final result. Save live, published page as .mht single-file archive in (subfolder of) same network/local folder.

Benefits Summary

Wait a minute! What just happened here? A process was automated. Word, Outlook, WordPad, Windows Explorer, Internet Explorer, and Adobe Acrobat were controlled programmatically. And out of that one automated process comes standardization, a vast increase in productivity, and an appreciable reduction in labor! You just can't attain this level of functionality with Access alone, nor with casual programming efforts. VBA can make productivity and efficiency and expedient service happen in a big way—automatically. But...impeccable programming skills and expertise, such as those of VBArtistry, are essential to this grade of implementation.
 

	Automated standardization
	Increased functionality, productivity, service efficiency convenience
	Decreased time, labor, expense fatigue

VBArtistry conceived and suggested much of this advanced functionality. Stemming from our thorough understanding of Denver City Clerk's processes and objectives and exploiting resources already available, we recognized opportunities for integrating related tasks that previously had been performed independently.

Let us compose a procedure to automate and standardize your process, making it run with greater ease, convenience, and economy.
 

Top

Contact Info

Top

Send mail to Webmaster with questions or comments about this website. ~ Privacy Policy ~ Terms of Use

Copyright © 2007 VBArtistry ◊ All rights reserved.