Macro in Excel: Sort and Delete Columns Based on Column Names

Every week I get an Excel file that needs to be modified to be imported in Access. Modification includes columns renaming, deleting some of them, changing their order and data type, etc.

I recorded a macro to do all this for me last week, and this week was very happy to discover the extract I am getting does not guarantee the order of columns, and my macro was based on the order (select column A, cut it, paste it in column Z, etc)...not the names of the original columns/fields. I am not a VBA expert, all I can do is record a Macro and maybe modify it a little bit with the help of this forum (thanks, everyone) :)


Question is - how to I select/delete a column based on its name? Below is the part of the VBA:

Columns("M:M").Select
Selection.Cut

It should be someting like

Columns(named "ID").Select
Selection.Cut

I'll figure out how to do the delete and other stuff by analogy, I hope :)

Thank you!
Lena

re: Macro in Excel: Sort and Delete Columns Based on Column Names

Hi, Lena!

Excel can be cumbersome and I understand your grief. Truth be told, I think you are better at it than I am. If you are using Access anyway, why not make an unmatched query to see which columns are different in the new excel file. I hope you are trying to distinguish data here and there...

Using MS Access to work with Excel files will then always be, so we hope, at least more than Excel, the columns you expect them to be with data properly aligned:

(1) You can do an unmatched query by adding a query in and loading the new Excel file

(2) Then compare with the old.

If you feel you need help, please write, we will then send this to Access forum for a closer look.

Hope this helps answer, or at least simplify the issue.

Good luck with the project!