On Jan 30, 4:47*am, Bob Bridges
wrote:
Well, when I'm doing the same thing it's in VBScript, but I guess the
principle is the same: *I want some of my scripts to work both at work and at
home, so I have a routine that checks the path associated with the script and
gets the label of the drive at the top of that path. *At home my HD is named
MyCDrive and the path I'm looking for is C:\whatever\Code; at client A my HD
is named MAIN01 and the path is S:\something\else\General Code; at client B I
run off my flash drive and the desired path is F:\Code General\Code. *So my
program determines where it should look for routines based on that.

In VBA/Excel we're not talking about a script, but is there a way to
determine the path of the workbook itself? *Let's see, here.... *Well, close:
*=INFO("directory") will give you the "current" path, which at home is say
"C:\Documents and Settings\Owner\My Documents\" and at work
"R:\Benefits\Whatever\". *No workbook name, you'll notice, but you can
hardcode that. *So here's what I'd do:

a) In some holding cell, say X$1, put
="'"&INFO("directory")&"[BenDatabase.xls]Sheet1'!". *That gives you the first
part of the address that's common to your functions.

b) In that cell with the long formula, replace every occurrence of
"$A$1:$A$65536" with "$A:$A". *This doesn't have to do with your question,
I'm just tossing it in as free advice: *Saying "B:B" means "the whole column
B", meaning you don't have to specify the first and last rows. *It's just
simpler to look at. *Now back to your question:

c) In that cell with the long formula, replace every occurrence of "
'R:\Benefits\...\[BenDatabase.xls]Sheet1'!$A:$A" with
"INDIRECT(X$1&"$A:$A")". *INDIRECT, in case you didn't already know, is where
you specify an address as a character string and it looks it up as an
address. *X$1 is of course the first part of the workbook name, the path and
filename and sheet name. *I think the result looks like this:

=UPPER(IF(ISNA(MATCH($E$3,INDIRECT(X$1&"$A:$A"),0) ),"",INDEX(INDIRECT(X$1&"*$B:$B"),MATCH($E$3,INDIR ECT(X$1&"$A:$A"),1),0)))

d) In your VBA code, you can either open... *Hey, wait a minute. *Your VBA
code isn't in that workbook, is it? *Because you can't open yourself, so to
speak. *Ok, so you can't look at the value in X$1 to get the path, because
you have to know the path to open the workbook in the first place. *But in
VBA you can check Application.Path to get similar information; at home,
Application.Path starts with C:\... and at work it would start with something
else (right?). *So your program can determine whether you're working at home
or at work, and based on that choose which path to use when opening the
workbook. *Does that work for you?



--- "robzrob" wrote:
In a cell, I've got this formula

=UPPER(IF(ISNA(MATCH($E$3,'R:\Benefits\...\[BenDatabase.xls]Sheet1'!$A
$1:$A$65536,0)),"",INDEX('R:\Benefits\...\[BenDatabase.xls]Sheet1'!$B
$1:$B$65536,MATCH($E$3,'R:\Benefits\...\[BenDatabase.xls]Sheet1'!$A
$1:$A$65536,1),0)))

and in VBA, I've got this code

Workbooks.Open Filename:= "R:\Benefits\...\BenDatabase.xls"

This is ok when I'm in the office, but when I'm at home, I want this
formula

=UPPER(IF(ISNA(MATCH($E$3,C:\Documents and Settings\...\
[BenDatabase.xls]Sheet1'!$A$1:$A$65536,0)),"",INDEX('C:\Documents and
Settings\...\[BenDatabase.xls]Sheet1'!$B$1:$B$65536,MATCH($E$3,'C:
\Documents and Settings\...\[BenDatabase.xls]Sheet1'!$A$1:$A$65536,1),
0)))

and this code

Workbooks.Open Filename:= "C:\Documents and Settings\...
\BenDatabase.xls"

ie, the workbook BenDatabase.xls is in a different place, depending on
where I'm working. How can I set up the formula and the code to work
wherever I am?- Hide quoted text -

- Show quoted text -


Hello, Bob. (Sorry for delay - haven't been online lately.)
Somebody's given me a solution, I think:

Dim ofso: Set ofso = CreateObject("Scripting.FileSystemObject")

sDrive = "R:"

If ofso.driveexists(sDrive) Then
Call PutWorkFormulasIn 'In Mod 2
Else
Call PutHomeFormulasIn 'In Mod 2
End If

I'm hoping it'll run tomorrow when I open up at work. It didn't run
today at work because I tested for C: first and, of course, it found a
C; on my work pc, which I'd somehow forgotten about, so now, in the
above, I've changed it so it'll test for R: first. Fingers crossed.
I'll have a look through your stuff and will try that too. Still
learning.

Thanks.