Compile Error Constant Expression Required

This might be a little confusing so please bear with me. I have excel sheets that contains data I want to use to update an Access Database. The sheets containing the data do not contain the macro I want to run. I don't want them to be macro enabled sheets. So I created a simple excel sheet that is macro enabled and contains the macro that reads in the data and updates the DB. I have the user open the excel sheet containing the macro then open the sheet containing the data. The macro from the first sheet then shows up on the data sheet when the macro tab is clicked. I want to make the reference to the access database relative so if the excel sheets and the data base are put on a different computer and different directory it would still work. Note: The excel sheet with the macro and the database WILL be in the same directory, but the excel may not be in the same directory.

Below is the code I have tried but I keep getting a Compile Error: Constant Expression Required where the word .Path is highlighted. The other concern I have is that if the excel sheet with the data IS in a different directory that it will not get the path from the macro excel sheet but from the path of the data excel sheet. I have also upload all of the code I am using in case someone need to to see the whole thing. I hope someone can help me.

Please Login or Register  to view this content.

Const variable MUST be constant, no variable/function/method etc can be used
So, need to change to

Please Login or Register  to view this content.