{Excel} Regarding Relative Path in Macros

[teja1234]

Hi All,

I am using absolute path in my macro code, but i want to change it to relative path. I am not sure what exactly relative path will do how to use relative path in macro. Please can any one give me any idea on this.

Here is my path in code

Set Wkb2 = Workbooks.Open(Filename:="C:\Documents and Settings\admin\Desktop\Aezaz\BNP_Folder\Input\Manpower_Master_Forecast_10_BNPPISPL.xls")
Wkb2.Activate

Thanks in Advance

Teja

[NeoPa]

You can use the value in ThisWorkbook.Pa th to help you work out a relative path. Does this help?

Welcome to Bytes!

[teja1234]

You can use the value in ThisWorkbook.Pa th to help you work out a relative path. Does this help?

Welcome to Bytes!

Thanks for quick reply,

I have some input excel files in one folder, i am writting macro code in different excel workbook. I have to specify the location on input file as mention above in my 1st post. In that i am using absolute path that i want to make relative. Can you tell me what i need to do.

Reagards
Teja

[TheSmileyCoder]

Set Wkb2 = Workbooks.Open(Filename:=ThisWorkbook.path & "\BNP_Folder\Input\Manpower_Master_Forecast_10_BNPPISPL.xls")
Wkb2.Activate

Your not giving us enough information to work with. Relative to what? Ive given an example of using the path of the current workbook, and concatanating a string with the rest of the folder name.

Without more information we can't really help you.

[NeoPa]

It's hard to know what more you need Teja. Do you understand the concept of relative path? In this case I'm working on the principle that you want to open other workbooks (whose filenames you already know) from the same folder as that of the currently running workbook. ThisWorkbook.Pa th supplies the part that you don't have. You can use this string value to create an absolute path pointing directly to the file you require (As illustrated in Smiley's post #4). If you require the path of the workbook currently being worked upon (usually the same but may be different from the one whose code is running) then you can use ActiveWorkbook. Path instead.

An alternative would be to use the ChDir statement to set the current working directory and then open the file giving a relative name :

ChDir ThisWorkbook.Path
Set Wkb2 = Workbooks.Open(Filename:="Manpower_Master_Forecast_10_BNPPISPL.xls")
Wkb2.Activate

This is unusual however, as it effects other things than simply the code, and the code has all the information it needs to provide the full reference anyway.

[teja1234]

It's hard to know what more you need Teja. Do you understand the concept of relative path? In this case I'm working on the principle that you want to open other workbooks (whose filenames you already know) from the same folder as that of the currently running workbook. ThisWorkbook.Pa th supplies the part that you don't have. You can use this string value to create an absolute path pointing directly to the file you require (As illustrated in Smiley's post #4). If you require the path of the workbook currently being worked upon (usually the same but may be different from the one whose code is running) then you can use ActiveWorkbook. Path instead.

An alternative would be to use the ChDir statement to set the current working directory and then open the file giving a relative name :

ChDir ThisWorkbook.Path
Set Wkb2 = Workbooks.Open(Filename:="Manpower_Master_Forecast_10_BNPPISPL.xls")
Wkb2.Activate

This is unusual however, as it effects other things than simply the code, and the code has all the information it needs to provide the full reference anyway.

Thanks NeoPa,
I got how to use relative path in macro, thanks for explaining me about it. Its working now for me.

Thanks once again

Teja

[NeoPa]

Oh good. I'm glad that helped :)