Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Import spreadsheet by row

    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?

    Any direction would be appreciated!

    Regards,
    AccessNY
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    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:

    Methods for transferring data to Excel from Visual Basic
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •