Automate a reformatting of a file being created........

Very unusual situation and not certain if excel gurus can answer..........


1. My end user uses an accounting software to generate an excel (csv) file with 6 columns. File is stored in a fixed location - it gets overridden each time the process is launched - Column 3 is a date column - when the file is created - the accounting package populates a date format - 01/01/2015 (Jan 1, 2015).

2. The end user then has to open the file and re-format column 3 to be a date of: 01-01-2015 before they can transmit to the bank (it's a safe pay type file).

3. It's obviously bad practice to manipulate a system generated excel file - therefore, is it possible to have some kind of VBA code run immediately after the file is created............or does the VBA have to be manually launched?

Hope my question is clear.............

Thanks in advance for any guidance or assistance............

Hi,

The csv file is really a text file that Excel can use. As such, the csv file acts like Kryptonite to most of Excel's magical powers. I'm afraid your VBA code must be manually launched, unless the end user's accounting software has a hook you can use to activate an external process.

The accounting software may have an option similar to the options in the Microsoft Safe Pay Configurator http://www.dynamicsgpinsights.com/20...with-safe-pay/ that will format the field properly for you.

Lewis

Thanks for reply. Wells Fargo was the problem - once we figured out their requirements we were able to configure the date field with / and not have to manually enter hyphens........I didn't think VBA would have launched automatically - Microsoft Dynamics GP does have a nice feature to fire off additional FTP software that will send file directly to bank without user intervention..........