Using VBA, MS Access and Excel

Dear All,


I want to prepare a system which consists of no. of inputs excel files like cusomter orders and other files.

These should be loaded into access with click of a button on excel file.

And with another button they should be displayed in other sheet.

I wanted to prepare a complete business spreadsheet from getting customer orders, inventory management, planning and scheduling, delivery for a small manufacturing firm.

How can i go about with this? is it advisable to use VBA, MS access and Excel ? I have chose excel rather than VB application since i'm not much aware of VB coding and they already have many excels, which will make my job easy.

Thanks in advance.

I wouldn't use Excel for a sophisticated relational database application. Regardless, will need to use code with either approach. If use Access alone then code can be macros, otherwise VBA. Macros in Excel are really VBA code. Macros in Access are very different.

Thanks alot for the reply.

What i understand is i can use a vba in excel to read input excels and then load it to Access. Then generate different reports depending upon queries triggered by clicking some buttons on the spreadsheet. Let me know if my understanding is incorrect.


what are the drawbacks and plus points of this approach?

It is possible for Excel to push data to Access tables. Also, Access can pull data from Excel. The latter is easier.

You want to run Access reports? Never tried to run Access report by code in Excel. I don't think can be done unless the Access file is open.

I sure have never been able to write to access through excel vba. Not for lack of trying.

Let me know if my understanding is incorrect.

These should be loaded into access with click of a button on excel file.