I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

How can i pick up txt file and set path to variable through program ?

"Joel" wrote:

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

I didn't test this code but it is something like this

Sub test()


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const ReadFileName = "K:\foldername.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fread = fsread.GetFile(ReadFileName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
DefaultFolder = tsread.ReadLine

MyFileName = "abc.xls"
Workbooks.Open FileName:=DefaultFolder & "\" & MyFileName

End Sub


"Vijay Kotian" wrote:

How can i pick up txt file and set path to variable through program ?

"Joel" wrote:

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

Thank you for providing codes, even i have not tested but one more query. I
am new to macro world ... hence please adjust me.....

How to call this (the codes given by you) macro from my macros e.g. how to
call macro from another macro?

Thank you.

"Joel" wrote:

I didn't test this code but it is something like this

Sub test()


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const ReadFileName = "K:\foldername.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fread = fsread.GetFile(ReadFileName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
DefaultFolder = tsread.ReadLine

MyFileName = "abc.xls"
Workbooks.Open FileName:=DefaultFolder & "\" & MyFileName

End Sub


"Vijay Kotian" wrote:

How can i pick up txt file and set path to variable through program ?

"Joel" wrote:

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

Thank you with your coding it works. Anyway i need to copy this on each and
every macro. My purpose is solved.

"Vijay Kotian" wrote:

Thank you for providing codes, even i have not tested but one more query. I
am new to macro world ... hence please adjust me.....

How to call this (the codes given by you) macro from my macros e.g. how to
call macro from another macro?

Thank you.

"Joel" wrote:

I didn't test this code but it is something like this

Sub test()


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const ReadFileName = "K:\foldername.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fread = fsread.GetFile(ReadFileName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
DefaultFolder = tsread.ReadLine

MyFileName = "abc.xls"
Workbooks.Open FileName:=DefaultFolder & "\" & MyFileName

End Sub


"Vijay Kotian" wrote:

How can i pick up txt file and set path to variable through program ?

"Joel" wrote:

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?

Calling macro conventions

For Subroutines
1) Subroutines call from worksheet cannot have any parameters in parameter
fields
2) calling from one sub to another use the word call infront of macro name
call test(parm1,parm2)
3) sub routines do not return any values. You can declare a variable ByRerf
or ByVal. When using ByRef any value changed inside the subroutine will also
cahnge outside the subroutine

For functions
1) functions cannot be executed from the menu.
2) functions return one parameter
3) functions can be called from either worksheet, subroutines, or other
functions
4) When called from other macros just use name and paramerter (no call
required)

a = Myfunction(parm1,parm2)



"Vijay Kotian" wrote:

Thank you with your coding it works. Anyway i need to copy this on each and
every macro. My purpose is solved.

"Vijay Kotian" wrote:

Thank you for providing codes, even i have not tested but one more query. I
am new to macro world ... hence please adjust me.....

How to call this (the codes given by you) macro from my macros e.g. how to
call macro from another macro?

Thank you.

"Joel" wrote:

I didn't test this code but it is something like this

Sub test()


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const ReadFileName = "K:\foldername.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fread = fsread.GetFile(ReadFileName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
DefaultFolder = tsread.ReadLine

MyFileName = "abc.xls"
Workbooks.Open FileName:=DefaultFolder & "\" & MyFileName

End Sub


"Vijay Kotian" wrote:

How can i pick up txt file and set path to variable through program ?

"Joel" wrote:

You can create a text file in the root directory of your k drive that have
the latest folder name. Then have the macros open the text file to get the
latest folder.

"Vijay Kotian" wrote:

I think problem is not explained correctly.

We save specific data related issues in one specific drive and for another
issues to another drive...... if i work on multiple issues then very often i
need to change drive
Presently my drive location is K:\JPP\FCH after change in issue the location
is changed to K:\VIJAY\GORE and next time again to k:\VIJAY\HITEN\ABC and so
on and so forth.

I have around 12 macros connected to one issue and every time due to change
in path i need to individuallly change all macros. I want some way out ...

Please help.

"Joel" wrote:

Use a mapped drive letter. You could map a drive Z to equal
\\networkdrive\JPP\FCH. Then if you change the location all you have to do
is remapped the drive.

"Vijay Kotian" wrote:

I have created several macros for one of my assignement, In which i have
specified a directory (e.g. K:\JPP\FCH). In all my macros i have used this
path for processing. If the path is changed, then I need to go to all macros
and individually change the path. Is there any alternative so that if i
change path at one place all macros get updated OR assign path address to a
variable and assign this variable to all macros once, so that in future only
at one place the path needs to be updated. How this can be done?