i am a telecom business financial analyst and i work on daily basis with excel for assessing financial viability of different offers to be offered to market. I usually receive the offers from different pricing teams in excel sheets tables in the following format:
Price Local minutes International Minutes Data (GBs) Customer Name Business Case Ref
100$ 1000 200 2 XYZ 5555
Due to the massive numbers of offers (assessments) we do, we did not have a single database to track all the offers that has been assessed (based on above table format). Accordingly, we have decided to put an Access Database to record and track these offers. The solution worked quite will. However, in order to maximise the time benefit, i thought of why not to put a command (Macro based) in the excel sheet of each business case assessed which will automatically export the above offer table in the same format into the Access Database. the challenge which i need help for is the following:
1- how do i create a command (VBA based) to export the data in the offer table?
2-what is the code to be written
3-should i save the excel workbooks with single unique names since each offer is customised for each customer with a unique Business Case reference number. How do i overcome this issue?