Excel variable for path

[Esgrimidor]

Excel variable for path

I would like propose a variable in an excel cell and launch a word document from other excel cell

Y:\document\%variable%\proof.doc ,

where  %variable% can take several values.

The taken value is proposed in an excel cell and seen.




Any similar solution is also possible for me .

Thankxxx

[soybean]

Can you elaborate on what the variable does here?  Do you want Excel to display a link to a Word document only if certain conditions are met?

[Esgrimidor]

Can you elaborate on what the variable does here?  Do you want Excel to display a link to a Word document only if certain conditions are met?

The variable does as a part of the path to the word document (proof.doc).

In the picture you see two written cells. The first can contain the variable and the second the link to the word document.

There are no additional conditions.

[Salmon Trout]

You can use HYPERLINK function

CELL is the cell (relative) where varying part of file path is

In this simple example I have hard-coded the unvarying parts of the path and the filename, but of course they can be cell contents too.

=HYPERLINK("Y:\document\"&(CELL)&"\proof.doc", "friendly name")

If you omit friendly name, the user sees the path and file name in the cell

Example

=HYPERLINK("Y:\document\"&(D5)&"\proof.doc", "Sales Report")

See HYPERLINK in Excel Help for full usage notes.

[Esgrimidor]

Perfect.

Thank a lot.
I have to accomodate to the excel spanish 2002 version . I think is HIPERVINCULO the name of the function.

Can I have this variable available outside excel ? I mean : Can I save this variable for any other windows application to use ?

I thin an environmental variable need reiniate the system . I am not sure .
what's the more simplest way to have this value available everywhere ?

[Salmon Trout]

A Google search found this...


http://www.computing.net/answers/office/excel-variable-for-path/9342.html

You are being unfaithful... 

You may need to study VBA

http://www.dailydoseofexcel.com/archives/2008/06/04/variable-hyperlinks/

[Esgrimidor]

A Google search found this...


http://www.computing.net/answers/office/excel-variable-for-path/9342.html

You are being unfaithful... 

You may need to study VBA

http://www.dailydoseofexcel.com/archives/2008/06/04/variable-hyperlinks/


I try, but I am not a programmer.

I am at 95% of my goals. I have to study now C++ language, so is not possible to study VBA. I can't manage everything.

Nevertheless I'll try to study the link proposed.

[soybean]

Why not simply put a link to whatever file you want in a cell?  Apparently, that does not satisfy your requirements but why is that not sufficient?  If you want to open the linked file, simply click on the link in Excel. 

[Salmon Trout]

We've done that. We need an env var as well.

[soybean]

Why?  How does adding an env var improve this spreadsheet regarding hyperlinks to other files?  I'm trying to get the full picture here of what you're trying to accomplish but I don't see it yet.

[Salmon Trout]

I came in because Esgrimidor wanted to place, in a cell, a link to a file, the path to that file being made up from a fixed part and a variable part held in another cell. After we had established how to that, he then asked how to make Excel save that variable for other applications to use. How it gets into the spreadsheet in the first place is something I am curious about.

[Salmon Trout]

if anybody is still interested, I found a way to get a Windows environment variable into an Excel spreadsheet.

1. In Excel, choose Tools, Macro, Visual Basic Editor

2. Add a module (You can see an example in the pic) which uses the VBA Environ function.

Code: [Select]

Function Name() As String
     Name = Environ("VARIABLENAME")
End Function
3. Go back to the spreadsheet and be in the cell where you want the variable to appear

4. Choose Insert, Function, then select category "User Defined", and select the module you created. Click OK.

5. You should see the value displayed.

6. Save spreadsheet.

Note: The environment variable must be the "permanent" kind, such as TEMP, USERNAME, Windir, etc, OR the sort you set in My Computer, Advanced, Environment Variables, or by using Control Panel->System->Advanced Tab->Environment Variable Button, or with a command line tool such as setx (Windows 2000 Resource Kit). You may need to log out & in again to see the new variable in Excel.

In the command window you can see, I had typed SET (with no arguments) to display the environment variables and their values.

[Salmon Trout]

Alternatively, you could write a line to a text file & then start Excel and get it to read the file

Code: [Select]

Function TextFileLine() As String
Dim MyString
Open "c:\test.txt" For Input As #1
Input #1, MyString
Close #1
TextFileLine = MyString
End Function


[Salmon Trout]

Another example, using a folder named in environment variables

Code: [Select]

Function MyTextFileLine() As String
Dim MyString, Homedrive, Homepath, Filename, Fullname
Homedrive = Environ("HOMEDRIVE")
 Homepath = Environ("HOMEPATH")
 Filename = "Excel-value.txt"
 Fullname = Homedrive & Homepath & "\" & Filename
Open Fullname For Input As #1
Input #1, MyString
Close #1
MyTextFileLine = MyString
End Function


[Esgrimidor]

I was "outside". I began fast to study all this and comment.

And anyways seems a good work . Thank a lot Salmon Trout.