VBA to choose and open an Excel file for import from a button on another Excel workbook
Posted on 2012-08-17
Please reference the question, VBA for Opening a Variably Named File in Excel and Copying it's Workbook Contents to Another Workbook.
I would take the code from the line "Now open the new file in Excel," and below, and put that in the on-click event of a newly created button on the Invoice Template workbook called, say, "Upload?" My idea is to create the file-to-be-copied-from in Access, and load it into a directory on a shared drive that could be accessed later by the accounting office. The accounting office could then, at a later date, open the Invoice Template, click the "Upload" button, have a dialog box open that would allow them to browse to the file they wanted, choose the file, and then have the remainder of the code you wrote do the copy/paste processing.