SET UP FORMULAS AND CODE TO WORK WHEREVER WKB IS USED

[robzrob]

Hi

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?

Cheers
Rob

 

[Bob Bridges]

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,INDIRECT(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?

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?

 

[robzrob]

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 scriptand
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 myHD
is named MAIN01 and the path is S:\something\else\General Code; at clientB 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, iswhere
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,INDIRECT(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.  Butin
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 athome
or at work, and based on that choose which path to use when opening the
workbook.  Does that work for you?

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.