We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet.

The user will be receiving spreadsheets containing data but that do not contain the VBA code. We need to be able to import the data from the spreadsheets containing the data into the spreadsheet containing the VBA code automatically. The worksheets containing the data have the same column format and datatypes as the worksheet of the spreadsheet containing the data.

Ideally, you would open the spreadsheet containing the VBA code, be presented with a UI allowing the user to navigate to the spreadsheet containing the data, click OK and the data will be imported.

How would you go about doing this? It has to be done using VBA in Excel spreadsheets.

2 Answers
2

This should get you started:
Using VBA in your own Excel workbook, have it prompt the user for the filename of their data file,
then just copy that fixed range into your target workbook (that could be either the same workbook as your macro enabled one, or a third workbook).
Here's a quick vba example of how that works: