Automated Import of Data from source files

My mentor has given me a project where I have to draw some specific data from some source files. The reason he wants me to do this is because they get the same type of source files annually and its a pain to keep performing such a repetitive, mundane task.

I don't have much of any excel experience. I'm really having a lot of trouble in seeing whether it is possible for me to do this task. The source files are from the EIA (Energy Information Administration) and they are quite large. I would attach them, but they are too big.

My first approach to solving this problem was with directly linking data from the source file to the spreadsheet containing all the data that I want. The problem is though is that once a new source file replaces the old one, the design could be different...

My mentor suggested that I write macros to complete this project... but I don't know if the task can be done in this way either.

Any suggestions as to what I can do with this project? Any help would be appreciated. Thanks.