in my new job I am working among other things with a big and very, very slow excel file. The purpose of the File is to show the Customers some information. For each product there is a new Excel File "X" created.
First I have to look for some Data in SAP and insert it in the file "X", than the file uses some lookup-makros and computes some information. After this information is created, some new information gets created through references to Excel File "A" and "B". The Files A and B are used as database at the moment. The generated information is then represented for the customer.
I haven't build the Excel file and think there is much room for improvement. My idea is now, to use VBA instead of the makros to automat all this stuff with less consumption of resources and use MS Access for the Files A and B. File A contains 30,000 rows and File B round about 200,000. I am familiar in programming, but haven't used MS Access ever before.
Before I want to start my project, I would like to figure out, where I should start. Should I use MS Access only as Database and do the computing in Excel, or should I do everything in MS Access and use Excel only as input/output file? Yet, I haven't figured out where to start and hoped to find here some help. Good sources for further information would be appreciated