User-changeable paths into Excel - avoid hard coding

Hello all,

I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel.

At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ) and do NOT want to go into the VBA to change them.

So I have made a table in Excel for them to enter the paths.

My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module.

SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )?

Or maybe there is a better way...?

Thanks for your help.

Brgds,
Graham

i dont understand your question?

if you looking at getting the file structure of specific files use

Sub getdirector()
i = Application.GetOpenFilename ' brings up the open dialog box
x = Application.GetSaveAsFilename ' brings up the save as dialog box
ActiveCell.Value = i ' returns the open dialog box value
ActiveCell.Offset(0, 1) = x ' returns the save dialog box value
End Sub

Hope this helps

Sorry, I'll try be more clear (still new to posting here sorry! )

I have this now:

_______________________
sub one_of_my_subs

Dim example_path As String

example_path = range("example_path").value

Workbooks.Open Filename:=example_path

end sub
________________________


This allows my users to change the path via the cell in Excel.

However - it's really time consuming to have to declare (dim and set) the variables in every subroutine they are used.

Having found a similar entry on another post - I can make the variables global or public (which is better and does it matter?) at the top of one of the modules (I guess it need to be the first?), which solves the repeated dim thing, but how do I only set the value once (only) to the value in the spreadsheet?

Thanks.

My suggestion would be to create publis constants.

Eg...

--------------------------------------------------------

Option Explicit

Public Const cstMyPath as String = Range("MY_Path").Value

---------------------------------------------------------

This would be how I'd do it, or else create a message box for them to enter
it in. Either way works.

HTH.

"gearoi" wrote:

>
> Hello all,
>
> I want to enable my non-VBA friendly users to be able to change the
> paths that various files are pulled from / saved to via Excel.
>
> At the moment the paths are hard coded - they are of the non-geeky
> variety (unlike me ) and do NOT want to go into the VBA to change
> them.
>
> So I have made a table in Excel for them to enter the paths.
>
> My project has many, many modules and subroutines. I can't declare and
> set the variables to the path names once, as I'd have to do it in each
> sub/module.
>
> SO my question is - is there a way of making global constants which are
> set up from stuff in the excel sheet (e.g. range("mypath").value )?
>
> Or maybe there is a better way...?
>
> Thanks for your help.
>
> Brgds,
> Graham
>
>
> --
> gearoi
> ------------------------------------------------------------------------
> gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> View this thread: http://www.excelforum.com/showthread...hreadid=400711
>
>

Hi Gearoi,

> My project has many, many modules and subroutines. I can't declare and
> set the variables to the path names once, as I'd have to do it in each
> sub/module.
>
> SO my question is - is there a way of making global constants which are
> set up from stuff in the excel sheet (e.g. range("mypath").value )?
>

Declare the variables that are to hold the path as Public, at the top of
a normal module. This makes them global in scope, for all modules to use:

Public gsPath as String
Public gbVarsOK as Boolean

Then use an initialise routine:

Sub Initialise()
gsPath=Thisworkbook.Worksheets("Paths").Range("A1")
gbVarsOK=True
End Sub

In your entry subs (fired by the user), it is a good idea to check if
gbVarsOK=True so you know the paths have been read.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

if I am not correct if you try in one of the modules type

Public Type
example_path as string
End type

thats it should work right through

The way i told you about saves the user from typing out the file name other than if they want to save it then use the getsaveasfilename

Thanks guys - I will use an initialise routine I think that sounds what I'm after.

That way I can run the initialise subroutine at the beginning of any module that might be run independently.

later gator,
cheers ears.

But constants have to be constants.

DaveO wrote:
>
> My suggestion would be to create publis constants.
>
> Eg...
>
> --------------------------------------------------------
>
> Option Explicit
>
> Public Const cstMyPath as String = Range("MY_Path").Value
>
> ---------------------------------------------------------
>
> This would be how I'd do it, or else create a message box for them to enter
> it in. Either way works.
>
> HTH.
>
> "gearoi" wrote:
>
> >
> > Hello all,
> >
> > I want to enable my non-VBA friendly users to be able to change the
> > paths that various files are pulled from / saved to via Excel.
> >
> > At the moment the paths are hard coded - they are of the non-geeky
> > variety (unlike me ) and do NOT want to go into the VBA to change
> > them.
> >
> > So I have made a table in Excel for them to enter the paths.
> >
> > My project has many, many modules and subroutines. I can't declare and
> > set the variables to the path names once, as I'd have to do it in each
> > sub/module.
> >
> > SO my question is - is there a way of making global constants which are
> > set up from stuff in the excel sheet (e.g. range("mypath").value )?
> >
> > Or maybe there is a better way...?
> >
> > Thanks for your help.
> >
> > Brgds,
> > Graham
> >
> >
> > --
> > gearoi
> > ------------------------------------------------------------------------
> > gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> > View this thread: http://www.excelforum.com/showthread...hreadid=400711
> >
> >

--

Dave Peterson

True, but a constant can be defined as a range value. It's the holder's
contents that have to remain static, not the value itself.

For the purposes of the routines running, the data should remain the same.

I've never tried doing it, but it works in other code I've written from
other apps. Othwise, just declare a Public variable and set it on initialise.

"Dave Peterson" wrote:

> But constants have to be constants.
>
> DaveO wrote:
> >
> > My suggestion would be to create publis constants.
> >
> > Eg...
> >
> > --------------------------------------------------------
> >
> > Option Explicit
> >
> > Public Const cstMyPath as String = Range("MY_Path").Value
> >
> > ---------------------------------------------------------
> >
> > This would be how I'd do it, or else create a message box for them to enter
> > it in. Either way works.
> >
> > HTH.
> >
> > "gearoi" wrote:
> >
> > >
> > > Hello all,
> > >
> > > I want to enable my non-VBA friendly users to be able to change the
> > > paths that various files are pulled from / saved to via Excel.
> > >
> > > At the moment the paths are hard coded - they are of the non-geeky
> > > variety (unlike me ) and do NOT want to go into the VBA to change
> > > them.
> > >
> > > So I have made a table in Excel for them to enter the paths.
> > >
> > > My project has many, many modules and subroutines. I can't declare and
> > > set the variables to the path names once, as I'd have to do it in each
> > > sub/module.
> > >
> > > SO my question is - is there a way of making global constants which are
> > > set up from stuff in the excel sheet (e.g. range("mypath").value )?
> > >
> > > Or maybe there is a better way...?
> > >
> > > Thanks for your help.
> > >
> > > Brgds,
> > > Graham
> > >
> > >
> > > --
> > > gearoi
> > > ------------------------------------------------------------------------
> > > gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> > > View this thread: http://www.excelforum.com/showthread...hreadid=400711
> > >
> > >
>
> --
>
> Dave Peterson
>

Did that line work for you?

It didn't for me.

DaveO wrote:
>
> True, but a constant can be defined as a range value. It's the holder's
> contents that have to remain static, not the value itself.
>
> For the purposes of the routines running, the data should remain the same.
>
> I've never tried doing it, but it works in other code I've written from
> other apps. Othwise, just declare a Public variable and set it on initialise.
>
> "Dave Peterson" wrote:
>
> > But constants have to be constants.
> >
> > DaveO wrote:
> > >
> > > My suggestion would be to create publis constants.
> > >
> > > Eg...
> > >
> > > --------------------------------------------------------
> > >
> > > Option Explicit
> > >
> > > Public Const cstMyPath as String = Range("MY_Path").Value
> > >
> > > ---------------------------------------------------------
> > >
> > > This would be how I'd do it, or else create a message box for them to enter
> > > it in. Either way works.
> > >
> > > HTH.
> > >
> > > "gearoi" wrote:
> > >
> > > >
> > > > Hello all,
> > > >
> > > > I want to enable my non-VBA friendly users to be able to change the
> > > > paths that various files are pulled from / saved to via Excel.
> > > >
> > > > At the moment the paths are hard coded - they are of the non-geeky
> > > > variety (unlike me ) and do NOT want to go into the VBA to change
> > > > them.
> > > >
> > > > So I have made a table in Excel for them to enter the paths.
> > > >
> > > > My project has many, many modules and subroutines. I can't declare and
> > > > set the variables to the path names once, as I'd have to do it in each
> > > > sub/module.
> > > >
> > > > SO my question is - is there a way of making global constants which are
> > > > set up from stuff in the excel sheet (e.g. range("mypath").value )?
> > > >
> > > > Or maybe there is a better way...?
> > > >
> > > > Thanks for your help.
> > > >
> > > > Brgds,
> > > > Graham
> > > >
> > > >
> > > > --
> > > > gearoi
> > > > ------------------------------------------------------------------------
> > > > gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=400711
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

No, you're right you can't define a constant as the results of a range. What
a pain.

Oh well, looks like the Public variable and not constant would have to be
the way to go. Hate it when I'm wrong.

Good spot Dave P!!

"Dave Peterson" wrote:

> Did that line work for you?
>
> It didn't for me.
>
> DaveO wrote:
> >
> > True, but a constant can be defined as a range value. It's the holder's
> > contents that have to remain static, not the value itself.
> >
> > For the purposes of the routines running, the data should remain the same.
> >
> > I've never tried doing it, but it works in other code I've written from
> > other apps. Othwise, just declare a Public variable and set it on initialise.
> >
> > "Dave Peterson" wrote:
> >
> > > But constants have to be constants.
> > >
> > > DaveO wrote:
> > > >
> > > > My suggestion would be to create publis constants.
> > > >
> > > > Eg...
> > > >
> > > > --------------------------------------------------------
> > > >
> > > > Option Explicit
> > > >
> > > > Public Const cstMyPath as String = Range("MY_Path").Value
> > > >
> > > > ---------------------------------------------------------
> > > >
> > > > This would be how I'd do it, or else create a message box for them to enter
> > > > it in. Either way works.
> > > >
> > > > HTH.
> > > >
> > > > "gearoi" wrote:
> > > >
> > > > >
> > > > > Hello all,
> > > > >
> > > > > I want to enable my non-VBA friendly users to be able to change the
> > > > > paths that various files are pulled from / saved to via Excel.
> > > > >
> > > > > At the moment the paths are hard coded - they are of the non-geeky
> > > > > variety (unlike me ) and do NOT want to go into the VBA to change
> > > > > them.
> > > > >
> > > > > So I have made a table in Excel for them to enter the paths.
> > > > >
> > > > > My project has many, many modules and subroutines. I can't declare and
> > > > > set the variables to the path names once, as I'd have to do it in each
> > > > > sub/module.
> > > > >
> > > > > SO my question is - is there a way of making global constants which are
> > > > > set up from stuff in the excel sheet (e.g. range("mypath").value )?
> > > > >
> > > > > Or maybe there is a better way...?
> > > > >
> > > > > Thanks for your help.
> > > > >
> > > > > Brgds,
> > > > > Graham
> > > > >
> > > > >
> > > > > --
> > > > > gearoi
> > > > > ------------------------------------------------------------------------
> > > > > gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> > > > > View this thread: http://www.excelforum.com/showthread...hreadid=400711
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Well, one of us Dave's has tried that before (probably several times, <vbg>).



DaveO wrote:
>
> No, you're right you can't define a constant as the results of a range. What
> a pain.
>
> Oh well, looks like the Public variable and not constant would have to be
> the way to go. Hate it when I'm wrong.
>
> Good spot Dave P!!
>
> "Dave Peterson" wrote:
>
> > Did that line work for you?
> >
> > It didn't for me.
> >
> > DaveO wrote:
> > >
> > > True, but a constant can be defined as a range value. It's the holder's
> > > contents that have to remain static, not the value itself.
> > >
> > > For the purposes of the routines running, the data should remain the same.
> > >
> > > I've never tried doing it, but it works in other code I've written from
> > > other apps. Othwise, just declare a Public variable and set it on initialise.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > But constants have to be constants.
> > > >
> > > > DaveO wrote:
> > > > >
> > > > > My suggestion would be to create publis constants.
> > > > >
> > > > > Eg...
> > > > >
> > > > > --------------------------------------------------------
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Public Const cstMyPath as String = Range("MY_Path").Value
> > > > >
> > > > > ---------------------------------------------------------
> > > > >
> > > > > This would be how I'd do it, or else create a message box for them to enter
> > > > > it in. Either way works.
> > > > >
> > > > > HTH.
> > > > >
> > > > > "gearoi" wrote:
> > > > >
> > > > > >
> > > > > > Hello all,
> > > > > >
> > > > > > I want to enable my non-VBA friendly users to be able to change the
> > > > > > paths that various files are pulled from / saved to via Excel.
> > > > > >
> > > > > > At the moment the paths are hard coded - they are of the non-geeky
> > > > > > variety (unlike me ) and do NOT want to go into the VBA to change
> > > > > > them.
> > > > > >
> > > > > > So I have made a table in Excel for them to enter the paths.
> > > > > >
> > > > > > My project has many, many modules and subroutines. I can't declare and
> > > > > > set the variables to the path names once, as I'd have to do it in each
> > > > > > sub/module.
> > > > > >
> > > > > > SO my question is - is there a way of making global constants which are
> > > > > > set up from stuff in the excel sheet (e.g. range("mypath").value )?
> > > > > >
> > > > > > Or maybe there is a better way...?
> > > > > >
> > > > > > Thanks for your help.
> > > > > >
> > > > > > Brgds,
> > > > > > Graham
> > > > > >
> > > > > >
> > > > > > --
> > > > > > gearoi
> > > > > > ------------------------------------------------------------------------
> > > > > > gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
> > > > > > View this thread: http://www.excelforum.com/showthread...hreadid=400711
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson