Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-14-2014, 03:09 AM   #1
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,250
Thanks: 54
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
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 of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.
Any help would be much appreciated.

Thanks
Attached Files
File Type: xls Sun Project.xls (19.5 KB, 58 views)


Last edited by aman; 08-14-2014 at 03:17 AM.
aman is offline   Reply With Quote
Old 08-14-2014, 03:03 PM   #2
tehNellie
Newly Registered User
 
Join Date: Apr 2007
Location: Bristol, UK
Posts: 751
Thanks: 2
Thanked 47 Times in 47 Posts
tehNellie will become famous soon enough
Re: Check Policy Number in Access table and populate related data in Excelsheet using

You could:
  • Use linked tables to the worksheets and have a query per linked table to perform the update.
  • Use linked tables, but rather than have 5 explicit queries build the SQL in code to perform the update.
  • Import all the data, do the updates and spit it back out again.
  • Write masses of code involving excel objects, recordsets, lots of debugging and shaking fists at the sky.

You might surmise that I favour one of the first two options, the third is an annoyance that I will contemplate if I have to.

Access queries are perfectly capable of updating/inserting information in linked tables, from that perspective they act exactly the same as "local" access tables.

In an ideal world I'd always recommend having an explicit named query in the database rather than building it, behind the scenes, in code.

This is one of those instances where, despite knowing I'll be told where to shove it, I normally ask the people who produce the spreadsheet whether they'd mind awfully giving me one worksheet rather than lots but I normally find that despite wanting the sky purple tomorrow they're generally very unwilling to help make that happen.

That said if there will always be 5 sheets, there is an initial overhead to having to do everything multiple times but once it's written you've got the flexibility moving forwards to handle the fact that although they said they'll always be the same that sheet3 is now different to all the rest.
tehNellie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically create headers in Excelsheet using Access table data aman Modules & VBA 19 04-08-2014 07:38 AM
Update Access table records with values from excelsheet aman Modules & VBA 10 03-21-2011 12:14 AM
Transfer data from excelsheet back to access table aman Modules & VBA 5 03-16-2011 12:44 AM
Transfer the data from access table to excelsheet aman Modules & VBA 1 01-11-2011 05:55 AM




All times are GMT -8. The time now is 09:18 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World