Previously the data in the Excel file is manually loaded into database (copy-paste), now I was assigned to automate this procedure. See below as requirements:
- Excel file will update the database automatically
- If excel file crashed, reopen it and continue
Simple as that.
There are three main parts about this project.
- Excel VBA: Update the database; write to log file.
- Batch: Entry point of this project, is responsible for reopening and monitoring.
- VBScript Helper: Provide some functions which batch doesn't support.
ExcelWatcher2.bat: Entry point. ExcelWatcher.bat: another incorrect version. ExcelHelper.vbs: Provide different functions to support batch file, the batch file gets its return values by trapping the exit codes. LCHCME.xls: The target excel file we want to monitor. ExcelHelper.xls: Support for checking if the target file is opened or not. (Why we need this? See below) temp.txt: Store the temp value generated by ExcelHelper.xls
- There are different circumstances when opening excel program.
- When there is already excel file opened, double click another excel file, it will be in one instance (only one process)
- If we use command line (EXCEL.EXE) or double click EXCEL.EXE file, it will create a new instance (two processes).
- In the VBScript, the GetObject function can only get the first instance, so it's infeasible to use GetObject to the get the target instance (based on the first point). That's why I would to use the ExcelHelper.xls and temp.txt. More details about this will be added.
- Many hard-coded.
Just double click the Excelwatcher2.bat. It will prompt a CMD window showing what it's doing right now.
- Convert from VBA to VBScript.
- Less hard-coded.
- Enhance robustness.