JHB is giving examples. I will supplement his suggestion by giving some theoretical and pragmatic viewpoints.
If you are going to drive this from Access, the idea of opening some worksheet; converting its format to ANOTHER worksheet; and then importing that to a table ... just seems wasteful. Here is the question you should answer (for yourself, since you are the one who has to do this). Do you have the knowledge and ability to define what constitutes valid input to the first part of that process?
That is, if you were going to convert formats programmatically, do you already know what you expect to see in each cell/row/column/whatever for the folks are doing manually? Do you have visual clues, markers, etc. that you could theoretically test?
If the answer to that question is "NO" then your whole process falls apart anyway.
Now, as to that format you have created for your attachment, that is pretty easy to manipulate (I think). In overview, you do the following:
- Open an Excel Application Object
- Using the FileSystem Object, look for/identify the file.
- There IS such a thing as opening a FilePicker dialog box to help find the file.
- Using the Excel app object, open the file that you have chosen as the workbook for the item to be imported.
- Identify the desired worksheet (within the workbook) and make it the ActiveWorksheet
- Direct-access the cells, which are found either as a collection of rows (each of which is itself a collection of cells) or a collection of columns (each of which is itself a collection of cells). I.e. access by row first or by column first.
- You CAN do something like compare keywords vs. the content of a cell to see if you found a "marker"
- If you can persuade your folks to start using that format, you can easily just run your analyzer code on each spreadsheet you get.
Now, here is my thought. If you CAN decipher the spreadsheet in the first place, use Recordset operations to immediately import it. Don't bother to change formats first and THEN do a second operation to do the actual import via TransferSpreadsheet or whatever else you would use. Why touch it twice when you can touch it once and be done?
One more thought: It is possible using the file system object methods to rename that file to another folder to signify that you have processed it, or you could just provide your system with copies of the workbook files and delete them when you are done with them.
If you really DID want to do some reformatting i.e. to output a record in the "preferred" format, then you could use the File System Object to COPY the file to some desired name, open the Excel App, open the workbook, etc. to fill in the cells that need to be filled in.
A couple of final thoughts... have you ever heard of that old book, "Everything I Needed to Know I Learned in Kindergarten" ? Rules such as "If you open it, close it." "If you take it out, put it away." Always be sure to close workbooks and close application objects when you are done with them.