Sample Bill Export Visual Basic Script with Commentary
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
