External Linking ro Excel and Component Prices

[johngwms]

I have decided to use an external Excel spreadsheet to store my master price list as I need to calculate prices based on the buy price and a number of other factors.

From the Actinic Help files it seems straightforward to link product price but I also need to keep component prices also on the spreadsheet. I can't work out how to do that. Has anyone out there worked it out?

[Tonyo]

I use a vba routine that fixes all my prices based on the cost price. I run it on my stock control data and it automatically updates the product table within access without opening the table. This way I have no need to use actinics mapping tool.

#########
Round((Sheet1.Cells(n, "C") * 100) / (1 - Range("B2")), 0)
#########

Run this in a macro.
Column 'C' is your cost price field.
'B2' is where I reference my margin as a percentage.

Hope this helps.

kind regards Tony

[johngwms]

Thanks Tony.

I am unclear where you are running the vba routine. The codse suggests that you are working with a spreadsheet - Is that an external excel spreadsheet?

Would you mind posting further details?

Thanks

John

[Tonyo]

I use a stock control system were I export the product data to an excel spreadsheet. Every time I book an item into stock, the system updates the product with a last purchase price. Because the prices can change daily I need to run a routine so that I always have the correct margin applied to my selling price.
I have to do it this way because my actinic is not synchronized with my stock control but that is another story.

The vba runs on this external spreadsheet that updates the product selling pricing based on the last supplied price. Once you have run the routine you can use actinic to map to your new pricing field.
If you have components for your products and are using a stock control system then these components are part of a bill of materials. They will have there own prices that you can map to.

I assumed you were using actinics External Links mapping tool to bring in your pricing.
I do it another way by using Microsoft ADODB to connect directly with actinic database.

PM a sample data so I can see what it is your are trying to achieve.

regards Tony