This can certainly be done. If you are looking for introductory SolidWorks API materials, I have over 3 hours of free videos at my web site, Video Tutorials for the SolidWorks API. Lesson 6.1 would be very helpful to you as well, should you decide to go premium.
Otherwise, to get started, you might check out the free macro I have in the Macro Library called "Get cell data in active sheet's table". This will at least show you how to work with the ITableAnnotation interface, which you'll need to use when you working your BOM.
Anyway, your steps seem OK. So your first page is the full assembly and each subsequent page is an individual part? And each part has a custom property called DWG NO, and you're going to get that value and put it in the appropriate row in the BOM?
Just signed up to the site Keith, although I'm going to struggle to become a premium member just yet. I will do some research though.
Your right yes, the first page is the full assembly featuring each sub assembly, of which each has a unique DWG NO and subsequently sheet number. Then throughout the multisheet drawing is each sub assembly containing its own BOM and components. Every part and assembly has a unique DWG NO in a custom property specified when modelled.
I need to write a macro that works through each BOM on every assembly drawing finding the corrosponding sheet number for that DWG NO and adding it to the BOM row. Its alot of work to create this, but in the long run it will save me many hours!
One possible change to your scheme--instead of adding the sheet number to the bill of materials as a direct edit, why not add the sheet number as a custom property to the part? Then include that property in a column in the bill of material. This will keep the sheet number associated to the correct line in the bill of material whatever the ordering. This is basically how we do it.
I realize that this won't work so well if you reference a part file in multilple drawing sets.