Sample Bill Export Visual Basic Script with Commentary

About 50% of EnergyCAP Enterprise clients export billing data to their organization Accounts Payable or General Ledger system. The specific process and necessary software tools can be developed during software implementation. First, the information is entered or received into EnergyCAP. EnergyCAP audits the bills for billing errors, flags the issues and then passes the bills onto a supervisor for approval. When approved, the billing data is exported to the AP/GL system for payment. Once the AP/GL system pays the bills, information such as payment date, check number, etc can be imported back into EnergyCAP. The result is a complete bill record.

There are several advantages to doing business this way:

• Bill entry efforts are simplified. Many organizations enter billing data twice – once in an energy information system and again in their AP/GL system, doubling their efforts. By using EnergyCAP’s bill export capability, the organization saves valuable manpower by avoiding duplication of efforts.
• EnergyCAP becomes a “smart” front end to the AP/GL system. AP/GL systems are designed to facilitate the accounting process but not to analyze energy information. In most cases, as long as the vendor and date are correct, the system will issue payment. But if there are billing errors or if usage is irregularly high, the AP/GL system may not catch those types of savings opportunities.
• EnergyCAP can catch billing errors before payment is made. Many organizations pay the bill and look for errors later. When a mistake is caught, the payment has already been made. The organization has to recover the cost, which can take months. But with busy schedules, some organizations don’t go after their credits. They throw their money away because they don’t have the time to recover it. Catching a billing error before payment can save time and money.
• Consumption issues are spotted right away. With EnergyCAP’s audits and automatic benchmarks, you can spot problem facilities immediately. In one case, EnergyCAP benchmarks led a city energy manager to discover water pipes that were pouring directly into a drain. He quickly turned off the water. Although they eliminated future losses, they had already wasted thousands of dollars. AP/GL systems will not pick up many consumption issues but EnergyCAP will.

EnergyCAP can export to any AP/GL system. The company has extensive experience mapping data to and from a variety of different accounting systems. Typically, EnergyCAP exports to an intermediary file. A custom application reformats the EnergyCAP data to fit the AP/GL system and then imports to the system. When bringing data into EnergyCAP from the AP/GL system, the process is the same. This method ensures EnergyCAP’s compatibility with any system and at the same time protects the AP/GL system’s security and integrity.

NOTE: Bill Export and Import for AP/GL integration is a customized process for each organization. For pricing and implementation details, and/or a list of the applications interfaced with to date, contact EnergyCAP sales at sales@energycap.com.

See also—Exporting Bills from EnergyCAP to an AP/GL System.

Billing data can be exported from the EnergyCAP^® database to a user-selected file type. The following file formats are supported:

• .txt (text file—default format)
• .dat (text file)
• .csv (comma-separated value file in ASCII format)
• .prn (contains a sequence of raw bytes that are understood by the printer hardware)

The bill export procedure involves defining and/or selecting an export Profile which specifies precisely what data will be exported and the format of the file. The bill export Profile includes a topmost Cost Center filter, as well as options for selecting which accounts and/or billing dates will be included in the export. The exported file can then be imported to the client A/P system, manipulated in a spreadsheet program for energy analysis, and/or used for reporting or other purposes. For most EnergyCAP clients, the export process is defined and all details finalized during software implementation.

There are two export streams in EnergyCAP; these export options offer additional flexibility for integration into an organization’s accounting structure:

• A/P (often used for bill payment for accounts which receive bills)
• G/L (often used for internal invoicing from accounts which calculate and issue bills based on submeter data)

There are also two ways to initiate bill export:

• Using the Bill Export window/wizard
• Running the bill export external task (EXPTSK.EXE) found in the EnergyCAP program directory

The advantage of the export task is that it can be run independently of the EnergyCAP program, even as a ‘scheduled task’ in Windows^®, using a previously-defined export profile. For more information about this procedure, see the topic on External Tasks.

The advantage of using the Bill Export window/wizard is that the bill export profile can be defined and/or modified. Follow the procedure below to define an export profile and export billing data from the EnergyCAP program.

1. Open EnergyCAP and navigate to the Account Manager or Work Flow Manager.
2. Click File>Export.
   
   
   The Export As: window will open.
3. Click the Save in: drop-down arrow and browse to highlight/select the desired location for the export file on your hard drive or network.
4. Click the File name: field and input the desired file name for the export file, including the desired three-letter file type extension (txt, dat, csv, prn). NOTE: If no file type is specified, the export will default to a .txt format file.
5. Click Save. The Bill Export window will open.
   
   
6. If the desired export profile has previously been defined, click the Use an existing profile radio button and select the profile from the drop-down list. Then proceed to step 8.
   NOTE: Defined profiles reside in the billexp.ini file. The default location for this file is C:\Documents and Settings\[User]\Local Settings\Application Data\EnergyCAP Enterprise\.
   (where [user] is the Windows^® ID for the user)
7. If it is necessary to create a new export profile, click the Create a new profile radio button. Then input the Profile Name in the field provided.
8. Click Next. The Export Selection Filters window will open.
   
   
9. Click to select/highlight the desired Topmost Cost Center in your organization from which bill information will be exported. The Topmost Cost Center tree duplicates your Account Manager organizational hierarchy.
• If necessary, expand the hierarchy by clicking the ‘plus’ icon to the left of the Cost Center.
  NOTE: Bill export options may be limited by individual User Permissions.
• Click the radio button corresponding to the desired Bill Creation Method:
• All Bills will export all unfiltered bills associated with the Cost Center.
• Vendor Bills will export only bills from accounts that receive bills (see Account Properties/Billing tab).
• Customer Bills will export only bills from accounts that create bills (see Account Properties/Billing tab).
10. Click Next. The Bill Export-Delimiter and Output window will open.
    
    
11. Choose the desired delimiter (the character separating each piece of exported billing data from the next in each record in the exported bill file). Comma or tab-delimited files are most common.
12. Click the radio button corresponding to the desired Output type:
• a. Select Automatic output if the export parameters will be defined only by the presence or absence of export flags associated with the bill records.
• Select Manual output to add additional export filters, including specific accounts and bill start/end dates.
13. If using bill tracking options, click the Mark bill as “Exported” check box. When the bill is exported, this option sets an Exported flag (corresponding to export mode A/P or G/L) in the EnergyCAP database Bill table for the record associated with the bill, preventing the bill from being exported again in the future. This check box should ordinarily be checked.
    NOTE: Manual output includes an override option: Ignore “Exported” flag.
14. If the Ignore restrictions checkbox is unchecked, Work Flow Wizard export restrictions will be honored in the bill export process. If checked, the exporter will ignore Work Flow restrictions, generally resulting in export of a larger number of bills. the default (unchecked) setting is recommended.
15. Select the desired Export mode, A/P or G/L. Each bill record in EnergyCAP has an APExported flag and a GLExported flag. The Export mode determines which field will be flagged during the export process:
• Select A/P if exporting to A/P.
• Select G/L if exporting to G/L.
  NOTE: in Work Flow Manager are two Waiting for Export folders – one listing G/L bills and the other listing A/P bills. After each bill is created, approved, and the batch is closed, it appears in BOTH Waiting for GL Export and Waiting for AP Export since both the export flags are set to ‘0’. Then when an export is run, depending on the choice in the profile (A/P or G/L), the flag corresponding to the export mode is set to ‘1’ and the bill disappears from the corresponding folder.
16. Manual ouput options should only be used in consultation with an EnergyCAP support technician. If using the Manual output options, set these additional filter options:
• Accounts: To limit the export to a specific account or accounts, click the Plus button to add specific account filters to the export. The Advanced Search window will open, enabling input of account search options. Once accounts appear in the Results: pane, accounts to be exported can be selected/highlighted using the Ctrl-click and Shift-click keyboard-mouse combinations. Click the Select button to close the Search window and populate the Accounts pane with the selected accounts. Repeat the process as many times as necessary until all desired accounts have been included in the export.
• Start/End date: To limit the export to a specific timeframe, click the Start date: and End date: drop downs and use the calendar interface to select each desired date.
17. Click Next. The Bill Export-Line Selection window will open.
    
    
18. Click the checkboxes associated with the additional information desired for export. Then click Next. A series of windows corresponding to your previous selection(s) will open, one after the other.
    
    
    In each window, the Available pane (left) displays available data for export. Click the arrow keys to move desired Available data to the Selected pane. The double-arrow button moves all data. The single-arrow button moves only the highlighted item. When desired data has been relocated to the Selected pane, click Next. Repeat this step until the Finished window appears.
19. When the Finished window appears, click Finish to initiate the bill export. Bill data will be exported to the file and file location specified, and the Log window will open, displaying export information.
    NOTE: Depending on the number of bills and the amount of data required, and the selected export file format, the export process may take some time.
20. Click Close to close the Log window.

The bill export process is complete.

This advanced topic provides guidance in constructing a visual basic script that can be automatically executed as a Windows Scheduled task. This script example runs the bill exporter (exports everything that's in the Waiting to A/P Export folder in Work Flow Manager) and locates it in a folder called D:\UtilBill\Out. The file is named AP_ECAP_EXPORT.csv. The export "map" (called the billexp.ini profile) is named BILLEXPORT.

The A/P import process must obtain the AP_ECAP_EXPORT.csv file from the AP_folder.

"ecap" is the datasource name (the name of the ODBC connection)
"apexport" is the user name and user password of a user set up just for exporting purposes. This user can have only one permission - Other Settings/Export.

________________________________

Option Explicit

Dim objFSO, myshell, export_folder, archive_folder, AP_folder, strFilename, strDate, strTime, strDateTime, EnergyCAP_APExport, strECEProgramDirectory

export_folder = "d:\utilbill\out\temp\" 'The folder for the file to be exported

AP_folder = "d:\utilbill\out\" 'The folder where A/P will grab the data

archive_folder = "d:\utilbill\out\archive\" 'The folder where the file is to be archived

strFilename = "AP_ECAP_Export" 'Name of the file to be created by EnergyCAP

strDate = Cstr(Year(date) & "-" & MonthName(month(date)) & "-" & Day(date)) 'Just the date in format YYYY-MON-DD

strTime = Cstr(FormatDateTime(Now(),vbLongTime)) 'Just the time in format HH:MM:SS AM/PM

strTime = Replace (strTime, ":",".",1,-1,vbTextCompare) 'Time is formatted to be HH.MM.SS AM/PM

strDateTime = strDate & "_" & strTime 'Date and time are strung together

EnergyCAP_APExport = "exptsk -d ""ecap"" -u apexport -p apexport -c STANDARD:BILL_TEXT -f """& export_folder & strFilename & ".csv"" -s ""BillExport""" 'This is the command line to execute the AP exporter - note the various parts of the command line and change where necessary

strECEProgramDirectory = "c:\program files\energycap enterprise\" 'Install location of EnergyCAP - the location of the exptsk.exe utility on the applications server

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myshell = WScript.CreateObject("WScript.Shell")

if(objFSO.FileExists(export_folder & strFilename & ".csv")) then 'Looks in the dir to see if any files exist

objFSO.DeleteFile export_folder & strFilename & ".csv", "True"

end if

if(objFSO.FileExists(etl_folder & strFilename & ".csv")) then 'Looks in the dir to see if any files exist

objFSO.DeleteFile etl_folder & strFilename & ".csv", "True"

end if

myshell.CurrentDirectory = strECEProgramDirectory 'Sets the working directory to be the location where EnergyCAP is installed

myshell.Run EnergyCAP_APExport, 1, True 'Executes the AP Exporter

if(objFSO.FileExists(export_folder & strFilename & ".csv")) then 'Looks in the dir to see if any files exist

objFSO.CopyFile export_folder & strFilename & ".csv", archive_folder & strFilename & "_" & strDateTime & ".csv", "True" 'Archives the csv file created by EnergyCAP

objFSO.CopyFile export_folder & strFilename & ".csv", AP_folder & strFilename & ".csv", "True" 'readies the csv file created by EnergyCAP for AP

end if





if(objFSO.FileExists(export_folder & strFilename & ".csv")) then 'Cleans out any files left from the AP Export task

objFSO.DeleteFile export_folder & strFilename & ".csv", "True"

end if

There is no way to remove an unwanted export profile using the EnergyCAP software. However, the profile can be deleted by externally editing the billexp.ini file by hand.

! CAUTION: Be sure to make a backup of the billexp.ini file before editing it because if you accidentally delete the profile you want to keep you won't be able to recover it!

In a default EnergyCAP installation, the file is located in the following directory:

C:\Documents and Settings\your_user_name\Local Settings\Application Data\EnergyCAP Enterprise

The text document is organized in multiple sections. There will be a section for each profile, headed by the profile name in brackets, and ending with the MergeSelections= statement. Just delete the unwanted section and save the edited file to the same name and location.

Last Revised: EnergyCAP Release and Database Version: EnergyCAP Enterprise 6.2.64.130 [DB 64]

Purpose:

This document lists and describes all exportable AP fields and (if applicable) their field names in the EnergyCAP Enterprise database (assuming the database version mentioned above). This document will be helpful in developing specifications and processes for sharing EnergyCAP information with accounting (AP/GL) interfaces.

This topic does not provide details of the EnergyCAP bill export process. To learn more about exporting bill data, see Bill Export Detail and Process .

Line Selection

During the export setup, the user may select which lines of data to export. Best practices as of 4Q 2011 dictate that AT MINIMUM, the Bill summary, Account summary, Meter summary, G/L summary, and Bill Line summary should be selected for export. The individual fields within each of these lines may be chosen at the Project Manager’s discretion.

A Note on String Literals

String Literals are used to clearly identify individual lines of the export file. Best practices dictate that for EVERY line type used for bill export, a string “literal” field should be added to the output fields to identify the line type for any AP interface. This is done during export profile definition using the EnergyCAP export interface:

At minimum, the string literal must contain the letter “L” plus a number (0-6), depending upon the line type. Also, the string literal MUST be the FIRST field, in order, from top to bottom. The string literal should contain the [abbreviation] and full name of the line type. Custom also dictates that an “@” character is added to the END of the string literal. Customarily, each string literal will contain the name of the output line with which it is associated.

Below is an example demonstrating how to create and insert the string literal for the Bill Header line of the export profile during  profile definition:

Bill Header

The Bill Header line appears ONCE at the top of a given export file. It contains highest-level summary information about the bills contained in the file.

Bill Summary

Bill Summary lines appear ONCE per bill. They contain high-level summary information about a particular bill.

*Bills can be Automatic (1), Manual (2), Estimated (3), Simulated (4), Accrual (5), Adjustment (6) … from the ObservationMethod Table.  The Observation Method used depends on what method is used to create the bill.  For example, Manual is when bills are hand-keyed, Automated is for Bills that are imported, Estimated is when the Estimated flag is checked, Simulated is from processors like Bill Splits and Calculated Accounts.  Accrual bills are created by the accruals module/processor, and Adjustment bills are when the “monkey wrench” is used from the bill edit screen.

**Joined to Address table where addresstypeid = 3 (Note: Physical – 1, Billing – 2, Service – 3)

Account Summary

Account Summary lines appear ONCE per bill. They contain account-level summary information about a particular bill.

Meter Summary

Meter Summary lines appear MULTIPLE times per Account Summary. They contain meter-level summary information about a particular bill.

G/L Summary

G/L, GL, or General Ledger Summary lines appear ONCE per Meter Summary (if GL records are assigned to those meters), ONCE per Account Summary (for account-charges, if a GL record is assigned to the meter), or MULTIPLE times per Account Summary (for special charges, if the user inserts extra, non-template bodylines). They contain GL-level summary information about a particular bill.

Bill Line Summary

Bodyline or Bill Line Summary lines appear MULTIPLE times per GL Summary (if GL records are assigned to Accounts or Meters) or MULTIPLE times per Account or Meter summary. They contain bill bodyline-level information about a particular bill and contain the most granular information of all the export line types.

Bill Footer

The Bill Footer line appears ONCE at the bottom of a given export file. It contains highest-level summary information about the bills contained in the file.