Here is my current situation:
- Creating an Access app which relies on a series of Excel spreadsheets, each of which contains serveral worksheets.
- Given a form, user inputs a set of values, these values are then used to query the set of worksheets within the spreadsheets and generates a set of results based on the calculated logic and data found in these worksheets.
- These spreadsheets are updated from another source, hence the spreadsheet that links to the access app must be updated accordingly.
Problem:
- Need an efficient way to access the data, by either using VBA code to access these xls files in runtime. (Don't think I can link to an xls in the Tables section of Access and then retrieve seperate worksheets within the xls in runtime - it prompts me and asks which worksheet i want to link to which means i'd have to link to each one individually)
OR
have the user select these xls files when an update is needed, and have access import all the data into my own table structures and then using my tables, perform the neccessary operations i need on it within my app.
Please give me some suggestions as to which method is better or if there are any other methods. Thanks.
- Creating an Access app which relies on a series of Excel spreadsheets, each of which contains serveral worksheets.
- Given a form, user inputs a set of values, these values are then used to query the set of worksheets within the spreadsheets and generates a set of results based on the calculated logic and data found in these worksheets.
- These spreadsheets are updated from another source, hence the spreadsheet that links to the access app must be updated accordingly.
Problem:
- Need an efficient way to access the data, by either using VBA code to access these xls files in runtime. (Don't think I can link to an xls in the Tables section of Access and then retrieve seperate worksheets within the xls in runtime - it prompts me and asks which worksheet i want to link to which means i'd have to link to each one individually)
OR
have the user select these xls files when an update is needed, and have access import all the data into my own table structures and then using my tables, perform the neccessary operations i need on it within my app.
Please give me some suggestions as to which method is better or if there are any other methods. Thanks.
