A DOS batch file (file with extension .bat) can be a very effective way to automate a series of file operations. You can schedule the batch file to run at a specific time, and automate, say, a series of operations such as copying, moving, deleting or renaming files. You can create a batch file in any text editor like notepad and just save the file with an extension of .bat. In windows, if you open a DOS prompt, type edit and hit return a retro text editor will open which you can also use.

The batch file code below will copy all microsoft excel files (.xls) from the current folder into a folder called "import" and append the current date in yyyymmdd format to the filename. Additionally the file extension is changed from .xls to csv (I am not sure how readable the csv file will be to other programs so let me know the result you get if you use it). 

In this example the batch file needs to be saved in the same directory as the Excel files.

There are many applications in using it. For example, sales people ftp their expense reports to a folder, these are swept up at the end of a day, the date appended to the filename, they are imported into a DB, the files are then moved to an archive folder.

To generate dynamic XML in your PHP application add the following line to your Apache httpd.conf file

XML files will then be parsed as if they are PHP. You can add your PHP scripts to the file to generate dynamic content and then use mod rewrite to turn the URL into a friendly one, i.e. turn something like  dynamic.xml?company=eriginal into eriginal.xml

With a wireless phone jack you can add an extra phone line outlet anywhere in your home without any rewiring. All you need is an electrical socket. The jack can be used with any device using a phone line including a modem, set top box, phone and fax machine. It's also fully expandable meaning you can add as many additional units as you need.

I use a phone jack for a fax machine in my home office and can't recommend the product highly enough. It's simple plug and play set up and retails at around £50..

When it comes to systems and applications less is more. If you find your business increasingly relying on a satellite system of spreadsheets and access databases then it really is time to review your strategy and your core systems. On a current project I've begun to feel like Copernicus gazing into the sky, except instead of stars I'm gazing out at a huge constellation of spreadsheets. In fact. there are so many, that the core system, Oracle,  is almost completely obscured from view.

Excel is great for data analysis, but it isn't the best for collecting data. Microsoft Access, on the other hand,  is a step up the ladder. You can design a better user interface and exert more control over the data entry process and consequently on the output in your reports. Although outside the scope of my remit I decided to help one group of users by replacing their shared spreadsheet with an access database - doing the work on my own time, and emphasising it was only a temporary solution.

Access, being a relational database, means your data entry screens may pull data from several tables and may only add the data to one. In building a screen that does this you might like some of your form fields to depend on the result selected in another field. This type of chained select process is very common on web forms using Javascript but it is as equally achievable in Access.

Lets have a look at a very simple database which we will call "customer". We will set up three tables.

Table 1 is called check and is used to list the types of check that will be performed on a customer.

Table 2 is related to table 1. It is a list of the possible outcomes of the check applied. Therefore it includes a foreign key (fkey) which is the index from table 1. In the example below we can see that the first three rows all relate to the credit check in table 1.

Table 3 will be the main table use to input data from a form. To keep things simple for our example this table includes the customer name as a free text field. In practice this would actually be a key to a separate customer table.

You can set these tables up in Access by using the Create table by entering data menu option. The three table are related to each other as follows.

We are now ready to create the user form, which we will name update, to input the data. Using the Create Form in Design view menu option create a form with four fields. The fourth field will contain the date time stamp which we will hide from view.

1. Customer name. Text box
2. CheckType. List box
3. CheckResult. List box
4. DateTime. Text box

To create the chained select between the two list boxes we need to do the following. Still in design view, view the properties of the CheckType list box. In the data tab we will input a SQL query in the row source as follows

Next in the event tab we need to add an event procedure to the After Update field. Click the first button at the end of the line and select [Event Procedure]. Then click the second button which will take you to a VB (visual basic) screen. Add the following code and save.

Basically here we are saying after you make a selection in the check type field, i.e. select whether the check type was a credit or a technical check, then rerun the SQL query in the check result field.

In the properties of the check result field add the following SQL query

In summary, the chained select is obtained by using the selection in the first field as a parameter in the SQL query in the second field, and by re-querying the SQL in the second field when a selection is made in the first.

I was looking for a quick way to merge the data from multiple workbooks into one worksheet in a new excel workbook.

The process of manually going through 40+ files and manually extracting the data wasn't going to be an effective use of time.

Although I used code before to do it I couldn't lay my hands on it, which hard drive, which file etc. so I had to start again but I needed a fast solution so fired up google.

One of the first links I clicked was for DigDB, an excel add-in which actually could do the job and a lot more besides but I didn't want to pay the licence. I bookmarked the site to return to later.

The solution I found was on Ron de Bruin's Excel Tips page which contains a lot of VBA code examples which you can use for free. The code there solved the problem exactly. It copies all the ranges you specify in the source workbooks and copies them into one master worksheet.

The precise code is reproduced below with some minor additional comments. To use it open an Excel workbook. Click on tools, Macro and Visual Basic Editor. In the VB editor select View and Code. Copy the code below into that page and click save. Save the file as basebook.xls on your C drive.

In the VB editor change the ranges you want to copy as required.

To use it the source files need to saved into a folder named source_data on the C drive, i.e. C:/source_data

From within basebook.xls select tools and macro. Select the macro to run.

I once agreed a contract with a Business Angel to work on two of his companies. One of them was loss making and the other was evidently booming but it's processes and internal controls were weak.

There was a story about the latter. Eleven months into its financial year their accounting system developed a bug. The program of action the business planned and followed was to replace the system and recruit a number of  temps to re-input the eleven months of data and attempt to get everything up to date. Ten months later when I joined it still wasn't up to date, the prior year hadn't been closed, the business was running without management information (MI) and had no cash (never a good sign).

The system which developed the bug was Sage Line 50. The audit trail had apparently been wiped. This had been the conclusion of the acting Accountant who devised the plan to replace it. 

I have a natural curiosity. An off the shelf package is very unlikely to develop a bug so severe as to merit a complete system change. In addition the support team at Sage will ordinarily fix a problem if one arises.

Day one I switched on Sage to verify my hunch and sure enough when I clicked into the Audit Trail it was blank, but it was only blank because the search filter was on. When I took the filter off all the transactions were there.  When I ran the verify data routine there were no errors. There was nothing wrong with the system.

The company had actually lost over £200K during this blind period although it believed it was making money.

I use this example frequently to emphasise the importance of.

1. Having a written project plan with gateway reviews, a Prince2 like methodology, that prevents plans being ill conceived and carrying on indefinitely without giving any results.
2. Getting a second opinion if the solution seems too drastic. Hiring me for a half day when the problem was identified would have saved a substantial sum of money and over ten months of efforts.
3. Not doing things in a panic. Take time to use common sense, in this example, the un-likeliness of Sage Line 50 being unfixable.