I am using the DoCmd.TransferSpreadsheet to import a large number of worksheets from an Excel 2003 file to an Access 2010 db and having good success.
I have a second spreadsheet that is causing me problems because the data is grouped by person on successive rows (btn one and three rows per person) but only the first row has the data for the person. I have attached a screen capture to better illustrate this.
How would I go about importing this data but associating the employee's name and Tax ID with the second and third row of data for her (lines 15 and 16)? Can I process row by row and check for null in next two rows?
Shouldn't be too difficult. Step down through the spreadsheet row by row. Use a variable to keep the current employee. If the spreadsheet cell is empty, do nothing, otherwise, update the variable. Then use the variable to add a record (either with SQL or the AddNew method of a recordset). You can use automation to access the spreadsheet: