Thanks for pointing me in the direction of using a recordset. I use ADODB recordsets in a number of places in my front end for temporary tables (the back end is SQL Server), so I created an ADODB connection and recordset pointing to the spreadsheet and am now importing the spreadsheet into a recordset. That is working well.
It's a good idea to concatenate the Section, Drawer, and Available into one "Location" field as part of the Query. Thanks.
But I don't understand how a crosstab would work in this situation. The parttype and quantity are specific to a given intpartnum. And the db might have multiple records for each intpartnum, which represent multiple locations for that intpartnum. But those multiple locations aren't identified as Location1, Location2, etc... in the db. There just may be multiple records for a given intpartnum. Defining them as Location1, 2, etc... is done after the fact just based on how many locations a given intpartnum has, with the prior knowledge that no part is likely to have more than 4 records.
A crosstab is normally done where one of the original fields has something in common with multiple entries in a different field, and one of those fields then becomes a set of rows instead of columns. That isn't the case here. None of the locations will match any other location.
Plus, a requirement of all of this is that the output spreadsheet has the same number of rows as the input spreadsheet, and contains all of the columns of the original (there are additional columns that I didn't show in the simplified examples above). The output spreadsheet is basically a copy of the input one, with some additional columns.