I can't remember what the best way to post this is... what group or
groups, I mean, so my apologies if this too not appropriate.

We receive 3-4 files per month. These are flat files; a header row
with a variable number of records on the following rows. We have a
process for importing them into a SQLServer database, after a few
steps in Excel.

But far more regularly than we would like, we find the file has
changed: a column added (sometimes at the end, sometimes in the
middle), a col deleted, etc. We aren't the primary customer, so we
don't have any say in this.

So it's on us to test that the header row matchs what we expect. I'm
building a macro to test this, and if it does not match, get a mini-
report of what's different.

Now, my first thought is a low-tech solution I've used befo put the
expected headers in the "driver" file, and just copy the headers from
the new file into the next row, and go thru and test that
range(1).value = range(2).value. A bit more than that, but that's the
jist.

My second thought is to capture the new file's headers into an array
variable and hardcode the expected values into the code, then compare
that way.

With either of these, I still need an efficient way to report on the
results. That's only tricky if aberrations are found. I guess a new
worksheet in the driver file is a reasonable option.

So I have two questions:
1. Has anyone done this and figured out an elegant way of
accomplishing this?

2. Can anyone point me towards a website with the basics of working
with array variables, populating, retrieving from same, etc? I think I
remember how to populate but I haven't really worked with them since I
first learned Excel's VBA many moons ago.

Thanks,
NJ

Take a look at this. Maybe it will help.

http://office.microsoft.com/en-us/ma...176501033.aspx

" wrote:

I can't remember what the best way to post this is... what group or
groups, I mean, so my apologies if this too not appropriate.

We receive 3-4 files per month. These are flat files; a header row
with a variable number of records on the following rows. We have a
process for importing them into a SQLServer database, after a few
steps in Excel.

But far more regularly than we would like, we find the file has
changed: a column added (sometimes at the end, sometimes in the
middle), a col deleted, etc. We aren't the primary customer, so we
don't have any say in this.

So it's on us to test that the header row matchs what we expect. I'm
building a macro to test this, and if it does not match, get a mini-
report of what's different.

Now, my first thought is a low-tech solution I've used befo put the
expected headers in the "driver" file, and just copy the headers from
the new file into the next row, and go thru and test that
range(1).value = range(2).value. A bit more than that, but that's the
jist.

My second thought is to capture the new file's headers into an array
variable and hardcode the expected values into the code, then compare
that way.

With either of these, I still need an efficient way to report on the
results. That's only tricky if aberrations are found. I guess a new
worksheet in the driver file is a reasonable option.

So I have two questions:
1. Has anyone done this and figured out an elegant way of
accomplishing this?

2. Can anyone point me towards a website with the basics of working
with array variables, populating, retrieving from same, etc? I think I
remember how to populate but I haven't really worked with them since I
first learned Excel's VBA many moons ago.

Thanks,
NJ