Results 1 to 9 of 9
  1. #1
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    68

    Do While Loop Problem


    I'm using vba code to import an Excel worksheet and not getting all the records because one of the records is missing a value in the first column. All of the records will always have a value in the 3rd column, but might be missing a value in the 1st and/or 2nd column. How can I ensure that I'm getting all rows with a value in the 3rd column?

    Do While xlc1.Value <> ""
    rst1.AddNew
    rst1.Fields(0).Value = xlc1.Offset(0, 0).Value
    rst1.Fields(1).Value = xlc1.Offset(0, 1).Value
    rst1.Fields(2).Value = xlc1.Offset(0, 3).Value
    rst1.Fields(3).Value = xlc1.Offset(0, 4).Value
    rst1.Fields(4).Value = xlc1.Offset(0, 6).Value
    rst1.Update
    Set xlc1 = xlc1.Offset(1, 0)
    Loop

    Please don't question why I'm using vba code or suggest other methods of importing an Excel file. I have my reasons. Just wondering how I can avoid the Loop from stopping prematurely.

    Thanks for your help.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Why are you...never mind.

    It would seem simple, so I might be missing something. Why don't you test the third column instead of the first?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    68
    I'm new to this. What do I need to change in my code to check the 3rd column?

  4. #4
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    68
    Thanks for the response.
    What do I need to change/add in my code to test the third column instead of the first?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I do that a little differently (I use Cells(R, C)) but I think the second argument of Offset is the column, so try referring to 2, which should be the third column.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    68
    I changed Set xlc1 = xlc1.Offset(1, 0) to Set xlc1 = xlc1.Offset(1, 2) but got an import error after importing only 1 record. (Runtime Error 3163). The first row however, did import correctly. The row with the missing value in the first column is row 9.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The more I think about Offset the more I think you're actually moving the cell with "focus" over, which you don't want. Go back to what you had and try this:

    Do While xlc1.Offset(0, 2).Value <> ""

    If that doesn't work, we'll switch you to the Cells method that I use, which I'm more familiar with.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    68
    That did it! Many thanks for your help.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah good, glad it worked for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-10-2011, 07:45 PM
  2. Help with a For Loop in VB
    By JFo in forum Programming
    Replies: 5
    Last Post: 09-29-2011, 02:45 AM
  3. Loop Problem
    By JDPrestige in forum Forms
    Replies: 3
    Last Post: 11-14-2010, 06:48 PM
  4. Do While loop
    By jgelpi16 in forum Programming
    Replies: 7
    Last Post: 07-23-2010, 08:21 AM
  5. Replies: 9
    Last Post: 04-28-2010, 11:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums