How we can extract data using VB Scripting from SAP

You can use VBA for fetching data from SAP. You can find lot of blogs if you search online.

If I explain in short, it depends on the level of access that you have to SAP system. I have done something similar in past. It worked like this; I had an existing RFC which will fetch the required data for me and for some part I had an ABAP program in place to fetch the data.

Then I will use macros to make a call to respective RFC and program to do their job.