Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SET UP FORMULAS AND CODE TO WORK WHEREVER WKB IS USED
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SET UP FORMULAS AND CODE TO WORK WHEREVER WKB IS USED
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,INDIRE CT(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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SET UP FORMULAS AND CODE TO WORK WHEREVER WKB IS USED
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No formulas Work | Excel Discussion (Misc queries) | |||
Verify Canadian Postal Code ~ make the code work | Excel Programming | |||
formulas do not work | Excel Discussion (Misc queries) | |||
formulas don,t work | New Users to Excel | |||
How can I work around the non value (#N/A) when using formulas in. | Excel Worksheet Functions |