I have a data processing program that exports ascii files similar to the attached. I need a VBA program in Excel that will allow me to select multiple files of this type, and import them into an excel workbook. Each file would need to be delimited into a separate worksheet in the workbook, with the worksheet name changed to the ascii file name.
Notice in the ascii file that the depth values are negative at the beginning of the file. I would like these negative values to be deleted from the file at some point, as well as the first 10 records following the first positive depth value (this is when the instrument is coming to equilibrium and these data are meaningless).
Next, I would like the column headers to be changed to something a bit easier to understand for the end users. While 95% of the time the column headers will be the same as in the example file, there may be instances where some of these do not appear and/or additional ones are added (depending on the equipment used). I'd be satisfied if these nine headers were changed as follows:
DepSM – Depth(m)
Sal00 – Salinity (PSU)
T090C – Temperature (C)
WetStar – Chlorophyll (mg/m^3)
WetCDOM – CDOM (mg/m^3)
Sbeox0Mg/L – Oxygen (mg/L)
Sbeox0PS – Oxygen (% Sat)
Par – PAR
Sigma-t00 – Density (Sigma-t)
Scan - Scan
Any additional names could be changed by hand later (as they are not very common), but I need the program not to choke if one of these nine are missing. Once all of the data are properly named in each sheet, the data need to be charted vs. depth (except Scan) and the charts placed in the appropriate sheet. See attached excel file for examples.
I then need an additional sheet added to the workbook and all of the data from the previous sheets added to it (see DEC 2010 CTD data sheet in attached excel workbook). These need not be formatted exactly like the example (as they are mostly for quality control and later import into a database once a foreign key is assigned), but I do need to know which data go with which file.
Finally, I would like to have the code graph each variable vs. depth (except Scan, see Dec 2010 CTD graphs in attached excel spreadsheet). Notice that depth is plotted on the y-axis and is inverted. Also notice that the x- and y- limits are the same for all graphs of a particular type (i.e. for salinity, the x-limits would be the min and max salinity values between all stations), this is for comparison purposes. These don't need to be formatted the same as in the example files, so long as the above criteria are met.
The attached excel file shows an example done by hand. Note that the column names and order are different than in the ascii file provided. The format of the resulting file need not be exactly the same, but I provided it as a guide to give an idea of what I'm trying to do.