Column names changing in a linked spreadsheet

[tbtcust]

Hello everyone. I have an excel spreadsheet linked as a table in a database. I have several queries that use this table. The column names in the linked spreadsheet is constantly changing, which is impacting all the queries. Columns are constantly being added and deleted as well.

Can someone recommend a method or two that I can use to reduce impact on the queries?

Thanks in advance for any help.

 

[Golom]

Are these changes just mysteriously happening or is someone opening the spreadsheet and changing things?

 

[MacroAlan]

Sometimes in Excel VBA, I use “Enum” to define what column certain data is in. Then you only have to change a number when the column moves. If it is deleted, you still might be in trouble:

Public Enum TheColumns
    CustomerID=1
    CustomerName=2
    Brand=3
End Enum

sub DoSomething()
    For N = 2 To CtRows
        Workbooks(fName).Activate
        Select Case Cells(N, TheColumns.Brand)   'Refers to current Row and Col 3

Not perfect but might help


Alan

 

[tbtcust]

Thanks everyone for your suggestions.

Did some reading on other sites and came up with the notion of a data access layer, which amounts to a set of select queries against the linked spreadsheet. I have standard names in the DA queries. Other queries, reports, etc, use the DA queries. Now when there is a change to the linked spreadsheet I update the DA queries and there is no impact downstream.

Thanks again everyone.