My goal is to relieve the user of my Access database from having to go into the TransferSpreadSheet Macro properties and modify the File Name parameter to reflect where on their hard drive they have or want the Excel spreadsheet to be found or saved.
I know I can write the macro as a VB function instead using the TransferSpreadsheet method of the DoCmd object. And, I could just have the user enter the path in a field on a form to be passed to the function. I would like to take it one more level and have a button they can click to open the dialog box for browsing to a location on the computer and have that location entered into the field and passed to the function. Now that I think of it, I would like it one of two ways:
1. Have a place on one form only where the user can specify where the database and spreadsheets are located, as I have both export and import spreadsheet macros and there's no sense in the user browsing more than once.
2. Be able to simply create a global constant that is the location of the database, since I intend the users to be importing and exporting the spreadsheets to the same location as the .mdb file.
I know I can write the macro as a VB function instead using the TransferSpreadsheet method of the DoCmd object. And, I could just have the user enter the path in a field on a form to be passed to the function. I would like to take it one more level and have a button they can click to open the dialog box for browsing to a location on the computer and have that location entered into the field and passed to the function. Now that I think of it, I would like it one of two ways:
1. Have a place on one form only where the user can specify where the database and spreadsheets are located, as I have both export and import spreadsheet macros and there's no sense in the user browsing more than once.
2. Be able to simply create a global constant that is the location of the database, since I intend the users to be importing and exporting the spreadsheets to the same location as the .mdb file.
