We initially tried this via SQL 2005 SSIS but now resorting to something that Excel/VB could do for us
We have a single worksheet workbook which has a dump of data from a database. First row is headers, first column contains account codes (strings) the next 12 columns are Period1, Period2, Period3, etc of the financial year (floats).
We then have a number of workbooks in a directory which contain multiple worksheets but always have one called 'Forecast' which is the one we are interested in. This sheet also has a column containing account codes (strings) and 12 columns of float data for each period of the year.
We need something to:
Loop through the directory of these workbooks Open the first one and use the Forecast sheet Find the first account code in a specified range of rows Search for a matching entry for this account code in the account code column of the data worksheet If a match is found and > 0, write the period1 value of that row back to the period1 value in the Forecast sheet for the row we are currently on Keep offsetting by one column for each period and write back if > 0 Move to the next account code Once hit the bottom of the range of account codes, close the current workbook with Save and open the next in the loop
Start Free Trial
