Find full path address for workbook

[JoeS01]

Hello, I am using VBA in Excel 2010, and have this workbook and an Access database mdb file in the one folder called workbooks.

To extract data from the database to the workbook, I need to know the full address of the folder ie like C:\Usder\ My Documents\workbooks....... The locatioon of the workbooks folder can change when I am using different computers, so need to do it automatically.

Can anyone please advise how to get the current path of the workbook's folder as a string variable?

 

[JoeS01]

I have tried the following code, but the error meassge said : Invalid outside procedure. Can anyone please suggest the right was to get the address of a workbook folder and use it as a public string variable?

Public Const strDBFileName = "Tx_Master_Database.mdb"
Public strMasterDBPath As String
strMasterDBPath = ActiveWorkbook.Path <<<<< Error

 

[JoeS01]

Tried another approach , still without success - another Compiler error. HELP!!

Public Const strDBFileName = "Tx_Master_Database.mdb"
Public strMasterDBPath As String
strMasterDBPath = FolderPath() <<< Invalid outside procedure

********************************************************
Public Function FolderPath() As String
' Returns the path of the current workbook
FolderPath = ThisWorkbook.Path
End Function

 

[Alpacino]

I had a similar problem
Why don't u just make sure workbooks are in the dame folder and just put:
ThisWorkBook.Path

I wanted to know name of the destination to extract data from a closed workbook.
Code:
Workbook.Open("C:\users\Alan\documents\workfiles\example.")
Workbook.Open(ThisworkBook.Path & "\example.xls")

This works for me. Without using a function.

 

[Jaafar Tribak]

I have tried the following code, but the error meassge said : Invalid outside procedure. Can anyone please suggest the right was to get the address of a workbook folder and use it as a public string variable?

Well the error you are getting is clearly telling what you are doing wrong. You are trying to execute the code line outside a procedure .

try somethig along these lines :

Public Const strDBFileName = "Tx_Master_Database.mdb"
Public strMasterDBPath As String                                   

Sub Test
    strMasterDBPath = ThisWorkbook.Path 
End Sub

 

[JoeS01]

Many thanks Jaafar, that clarified my thinking. It is going correctly now