|I am making a database that imports data from excel files. |
However, those files are not originally from excel. So, I have to pull data about once a week from a system that saves the files to a format that excel can read, but Access doesn't want to read because it is not in the xml format. I have to convert the data to excel (easy as opening the file in excel and saving it as an xml). I then go into the worksheet and have to change the first row, because it contains repeating data and they need to be unique fields of course.
The first row is used as the header information. The first 10 columns A1 -J1 are fine as the data is unique in each. K1 is where the issue begins. K1 contains a year and other text. The following 13 columns L1:X1 contain other text without the year from K1. This repeats at Y1 having a year and the following 13 columns not having a year but having the same text as L1:X1. I need to take the year from K1 (the year will be the first four characters) and insert that year as text into the next 13 columns (as the beginning text of the cell) so they become unique. This needs to happen about 20 times, 2015 - 2035.
This is tedious at best doing it by hand, but I have to do it for every piece of data I pull. I could do it by hand once and then copy and paste it into all of the new worksheets, however I'd prefer to have a macro I could run to do this.
Any help would be greatly appreciated!