Help

Home Contents Client Login Feedback Resources About Us

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:
 
bullet

Prompts for the customer's eddress

bullet

Locates the related PDF on a network drive

bullet

Counts the pages in the PDF

bullet

Calculates the per-page charge

bullet

Creates a Word session

bullet

Composes a Word invoice identifying the document and stating the charge

bullet

Creates an Acrobat session to convert the Word invoice to a PDF

bullet

Quits the Word and Acrobat sessions

bullet

Creates an Outlook session

bullet

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

bullet

Quits the Outlook session

bullet

Saves a copy of the PDF invoice to a network folder

bullet

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 . If you've got the time, expand the bullet below for exhaustive detail of the action executed by VBA.
 
bullet

Publish Agendas

bullet

Determine operating system and file structure. Procedure must run under Windows 98, NT, or 2000, with network or local folders.

bullet

Create Word session.

bullet

Copy publication agenda Word document from origin folder to special DenverGov files folder elsewhere on network or comparable local drive.

bullet

Apply formatting for DenverGov, such as Verdana font, size 7, etc.

bullet

Convert council bill and ordinance labels to hyperlinks to full-text, signed-and-sealed .pdf's of those documents found elsewhere on DenverGov.

bullet

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).

bullet

Quit Word application.

bullet

Invoke WordPad.

bullet

Open Web (.htm) file in WordPad, revealing HTML code (Notepad limited to 64K on Windows 98).

bullet

Edit HTML code to remove hyperlink formatting; thematic DenverGov hyperlink color will not be overridden.

bullet

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).

bullet

Exit WordPad.

bullet

Open new browser window.

bullet

Navigate to DenverGov editor login page.

bullet

Log in.

bullet

Navigate to file management entry page.

bullet

Find and click entry link.

bullet

Surf to files list box.

bullet

Select previous week's previous publication agenda file.

bullet

Surf to click delete button.

bullet

Dismiss delete confirmation popup.

bullet

Surf to click add button.

bullet

Surf to upload path text box.

bullet

Compose and supply network/local path of current week's current publication agenda file.

bullet

Surf to click upload button.

bullet

Surf to find and follow links to edit current publication agenda template page.

bullet

Surf to body text box, select entire contents, overwrite with include statement composed to specify current week's current publication agenda file.

bullet

Surf to click submit button to submit content, dismiss submission acknowledgement popup.

bullet

Surf to find and follow links to edit previous publication agenda template page.

bullet

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.

bullet

Surf to click submit button to submit content, dismiss submission acknowledgement popup.

bullet

Surf to follow links to main editor page.

bullet

Find and follow submitted content review and approval link.

bullet

Find and follow legal notices link.

bullet

Surf to find and follow links to review current publication agenda template page submitted content.

bullet

Surf to click approve button and dismiss submission acknowledgement popup.

bullet

Surf to find and follow links to review previous publication agenda template page submitted content.

bullet

Surf to click approve button and dismiss submission acknowledgement popup.

bullet

Surf to find and follow links to new content publication page.

bullet

Surf to find and check legal notices check box and to click publish button.

bullet

Surf to click OK button.

bullet

Navigate to live current publication agenda page on DenverGov to view final result.

bullet

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.
 
bullet

Automated standardization

bullet

Increased
bullet

functionality, productivity, service

bullet

efficiency

bullet

convenience

bullet

Decreased
bullet

time, labor, expense

bullet

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

 


For more info on expert VBArtistry programming services, please don't hesitate to contact us now!

Electronic mail
 

Programming ~ Client Support ~ Webmaster

Top

Home Contents Client Login Feedback Resources About Us


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

Copyright © 2012
VBArtistry All rights reserved.

 

Necessity data pertaining to hackers forum web money making programs with investment