+ Reply to Thread
Results 1 to 14 of 14

Check Policy Number in Access table and populate related data in Excelsheet using vba

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Check Policy Number in Access table and populate related data in Excelsheet using vba

    Hi Guys

    Please see attached the Workbook. I need to check the policy Numbers in Column A to be present in Access Table. If yes then write the corresponding ScanDate and BatchNo in columns I and J.

    I need to write VBA code to perform it.

    Any help would be much appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    1st result on Google should help!!

    http://stackoverflow.com/questions/7...2010-using-vba
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    Hi nathansav,My problem is different. Actually in the attached workbook, in sheet1 there are policy numbers present so we have to start from row 2 to the last used row and check if the policy number in column A of each row is present in Access table(Polno). If yes then in access table , there are fields scandate and batchno of each polno so the code should copy scandate and batchno in the corresponding cells in the excelsheet. There are actually sheet1 to sheet5 that contains data so the code should look for all the policy numbers in all the sheets and the update them with corresponding scandate and batchno.

    Many Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    Are you going back to access for the check? If so, that post will help, you just loop the numbers and insert your own SQL, you just need to do a query for each row, or pull it all Pol no and the 2 fields from access, then do lookups.

  5. #5
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    yeah, exactly this is what I have to do but can you help me in writing the code to get it done.. Any help would be much appreciated. Thanks

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    What is exactly, i asked a question with 2 outcomes.

  7. #7
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    Nathansav, I have tried writing this piece of code to check each policy no in Access table and then populating Scandate and BatchNo in the corresponding rows in the sheet but it doesn't work and gives me Application defined error. I hope you can make the changes accordingly..
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    Where does it error?

  9. #9
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    When I try to execute the code then error just pops up.. don't know where exactly is the error..

  10. #10
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    It comes after Do while statement. Before that the code works fine.

  11. #11
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    on this line

    strSql = "Select * from jabberwocky where PolicyNo ='" & ws.Cells(RowNo, j) & "'"

  12. #12
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    I changed the code as below but the error still appears:
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    The error comes after If Not rs.EOF statement.

  14. #14
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Check Policy Number in Access table and populate related data in Excelsheet using vba

    Can you attach a screen shot of where the error happens, does it happen on the rs.eof line or the one after. I cant see your computer so you have to tell me everything

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fetch Data from Access table into Excelsheet
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2014, 11:35 AM
  2. Write SQL query that will populate data from Access to Excelsheet using conditions
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2014, 09:02 AM
  3. Extract data from Access table into excelsheet
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2012, 05:36 AM
  4. Transfer data from excelsheet to access table
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2011, 04:35 AM
  5. Transfer data from Access table to Excelsheet
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 03:38 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1