Find file path to link to access db

Posted by James Jones on December 10, 2001 5:49 AM

How do I find the path of the currrent worksheet so
that I can link to an access database.

The worksheet is in c:\customer_files\customer_name\contract_reports
the database is in c:\customer_files\customer_name\plan_reports\plan.

Posted by Tom Urtis on December 10, 2001 7:20 AM

To display the active workbook's file path, you can do it via VBA or formula.

VBA:
Sheets("Sheet1").Range("A1").Value = ActiveWorkbook.Path & "\"
Note, to include the worsheet name, the code would be:
Sheets("Sheet1").Range("A1").Value = ActiveWorkbook.Path & "\" & ActiveSheet.Name & ".xls"

Or via a formula method if you prefer:
In A1, enter =cell("filename")
in B1 enter =LEFT(A1,FIND("[",A1)-1)
(Note, if you want to have the worksheet included, substitute the "[" for "]").

Any help?

Tom Urtis