Validate Active Sheet format by comparing to an External Control Sheet

[Akker]

I have coded a macro that takes a data export from a companies proprietary system, manipulates some fields, and then saves as a new sheet to be parsed back into the system.

The problem is that I just found out that the format of the data export, as in column order and extra columns, will change from time to time. My workbook validates the export file by checking that each header corresponds to the column order of the sample export the company originally provided me.

However, this is static and hard-coded into the macro, whereas now I need a solution that will check the export file's header order/format, against a dynamically changing external control sheet and I have no idea how to do this

I ultimately want this external file to be updated whenever necessary, and for my macro, when run, to call this file, and compare it with the current export file awaiting manipulation. If the column order does not match up, then obviously the export file is not in the latest format and will not be dealt with.

Does anyone have any clues on how I can go about this? Any and all help will be thoroughly appreciated.

Thanks.

 

[Damon Ostrander]

What am I missing?

From your description I don't see how comparing the export file format to the file the company originally provided versus comparing it to a new external file format would be any different. Can't you just compare the headers to the new external file headers just as you did before?

Damon

 

[Akker]

Hi Damon,

Thanks for the reply mate.

The original sample file I had received, had headers in cells A10:AC10. I hard-coded into my macro that IF A10 = "test" AND B10 = "smile" AND C10 = etc etc, the file would be in the correct format and correct column order.

However, the company has since added more columns, and changed the order of a few, and will continue to do so because unfortunately this was not an isolated change.

If one of their employees has an export file from a week ago, and the format has since changed, when they try to manipulate the data, my hard-coding may not be up to date and will result in erroneous output when they try and parse it back into the system.

So now I'm in a situation where I want an external file, that can be updated whenever the company changes their format yet again, because I feel it bad practice to have this sort of validation hard-coded, and I don't want to have to change the source code every time they change their format.

The problem is, for the life of me, I don't know how to go about it. I hope that made sense.

Any advice would be appreciated.