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
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
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
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.
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
What is exactly, i asked a question with 2 outcomes.
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.
Where does it error?
When I try to execute the code then error just pops up.. don't know where exactly is the error..
It comes after Do while statement. Before that the code works fine.
on this line
strSql = "Select * from jabberwocky where PolicyNo ='" & ws.Cells(RowNo, j) & "'"
I changed the code as below but the error still appears:
Please Login or Register to view this content.
The error comes after If Not rs.EOF statement.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks