How to verify headers

[nwarnock]

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 before: 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

 

[Bernie Deitrick]

NJ,

Create a new workbook, enter the desired values into the first sheet (into the expected cells), and
name that range "Headers".

In the same workbook, insert a codemodule and copy this macro into it:

Sub Compare()
Dim myCell As Range
Dim AllOK As Boolean

AllOK = True

For Each myCell In Range("Headers")
If ActiveWorkbook.ActiveSheet.Range(myCell.Address).Value <> myCell.Value Then
MsgBox "Cell " & myCell.Address & " isn't the expected value."
AllOK = False
End If
Next myCell

If AllOK Then MsgBox "All the headers are good."

End Sub

Save that file, but keep it open. Then, open the orthe file, and with that new file active, use
Tools / Macro / Macros... and choose the macro "Compare" (It will be listed with the file name
under which you saved the "Headers" workbook.)

HTH,
Bernie
MS Excel MVP

 

[nwarnock]

NJ,

Create a new workbook, enter the desired values into the first sheet (into the expected cells), and
name that range "Headers".

In the same workbook, insert a codemodule and copy this macro into it:

Sub Compare()
Dim myCell As Range
Dim AllOK As Boolean

AllOK = True

For Each myCell In Range("Headers")
If ActiveWorkbook.ActiveSheet.Range(myCell.Address).Value <> myCell.Value Then
MsgBox "Cell " & myCell.Address & " isn't the expected value."
AllOK = False
End If
Next myCell

If AllOK Then MsgBox "All the headers are good."

End Sub

Save that file, but keep it open. Then, open the orthe file, and with that new file active, use
Tools / Macro / Macros... and choose the macro "Compare" (It will be listed with the file name
under which you saved the "Headers" workbook.)

HTH,
Bernie
MS Excel MVP

Bernie,

That rocks -- thanks so much.

Only trivial change was that I've expanded the reference to the named
range so it reads like this:

Workbooks("NEW_ResRF.xls").Sheets("Latest").Range("Headers")

Nj