Computer Hope
Software => Computer software => Topic started by: Esgrimidor on August 30, 2009, 03:27:44 PM
-
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.
(http://img40.imageshack.us/img40/8749/screenshot1251661545.jpg) (http://img40.imageshack.us/i/screenshot1251661545.jpg/)
Any similar solution is also possible for me .
Thankxxx
-
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?
-
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.
-
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.
-
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 ?
-
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/
-
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.
(http://img101.imageshack.us/img101/7251/besotene2.gif)
-
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.
-
We've done that. We need an env var as well.
-
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.
-
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.
-
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.
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.
(http://i124.photobucket.com/albums/p29/badoit/Excel-VBA.jpg)
-
Alternatively, you could write a line to a text file & then start Excel and get it to read the file
Function TextFileLine() As String
Dim MyString
Open "c:\test.txt" For Input As #1
Input #1, MyString
Close #1
TextFileLine = MyString
End Function
-
Another example, using a folder named in environment variables
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
-
I was "outside". I began fast to study all this and comment.
And anyways seems a good work . Thank a lot Salmon Trout.
-
Alternatively, you could write a line to a text file & then start Excel and get it to read the file
Function TextFileLine() As String
Dim MyString
Open "c:\test.txt" For Input As #1
Input #1, MyString
Close #1
TextFileLine = MyString
End Function
I think this is the best option. As you say the environtmental variable need reopen session and is not instantly recognized.
At the present moment I am not able to discern if I am able to use this variable instantly in any other application like Goldmine - by example - , indicating the path in a bat file outside excel.
Could I ?
-
indicating the path in a bat file outside excel.
Could I ?
Do you mean like this?
@echo off
for /f "delims==" %%A in (' type "c:\test.txt" ') do (
set variable=%%A
)
echo the value is %variable%
-
I think we are near the point.
I'm making consults for the permanency of the variables in the system.
I think user's variables are in the system while you don't close session or reiniate. And environmental variables remain in the system after a close session or reiniate the system......
Salmon, really is so simple that my problem is simply experiment with the variables. It's more quick or fast experiment than consult what will happen in every situation......
I will no have an answer on my own meanwhile I don't touch the "variables" with my own fingers.......
So.
I will come back with the results
Best Regards
-
I used the command SET
SET varexpediente=125.09 to observ in
MyPC -- properties - advanced options - environmental variables .
But I don't see the variable. The bat executed was :
echo off
set varexpediente=125.09
So the variable is not created this way.....
Or only exist during the execution of the bat file. .......
-
Or only exist during the execution of the bat file. .......
Exactly
-
So Salmon Trout :
There is any way to create an environmental variable with a script ?
Perhaps a way to modify an environmental variable with a script ?
-
Try setx (Microsoft resource kit)
Information
http://ss64.com/nt/setx.html
Download
http://www.microsoft.com/downloads/details.aspx?FamilyID=DC2D3339-8F36-4FBA-A406-2A2A2AD7208C&displaylang=en
-
I go flying.
I left for you a photo of my mountain :
(http://i113.photobucket.com/albums/n221/SerPan_album/Imagen030-4.jpg)
-
Gracias por la montana. Jo me gusta mucho los españoles y las españolas y sus montanas y sus peliculas y los canciones de Amaral y Pagina 2 (Oscar Lopez) y el brandy Carlos III
-
Tú sí que sabes. Estoy mirando los enlaces y son muy buenos
*****
You are a gourmet. I am looking the links and are very interesting. There are many ways, even with reg files, and the setx option link seems to be for windows 2000, but I think I have seen for xp too.
Have a nice day.
(http://img101.imageshack.us/img101/7251/besotene2.gif)
-
You very kindly did not correct my Spanish; I think I should have written me gustan las cosas como las canciones de Amaral (y me gustan mucho sus piernas también). Although setx is nominally in Windows 2000 Resource Kit it will work with XP, Server 2003 & 2008 and Vista, and Windows 7 - so will the other Resource Kit utilities.
I also like las peliculas y actores sudamericanos / sudamericanas ... Ricardo Darin, Inés Efron, etc
-
You very kindly did not correct my Spanish; I think I should have written me gustan las cosas como las canciones de Amaral (y me gustan mucho sus piernas también). Although setx is nominally in Windows 2000 Resource Kit it will work with XP, Server 2003 & 2008 and Vista, and Windows 7 - so will the other Resource Kit utilities.
I also like las peliculas y actores sudamericanos / sudamericanas ... Ricardo Darin, Inés Efron, etc
As you wish :
The prior phrases :
All perfect
The present phrase :
All perfect
But If you like you can improve. I am read in a Readers club (in spanish of course)
In a few weeks I'll read On Chesill Beach by Ian McEwan .....
Gracias por la foto de la montaña. Me gusta mucho España , sus montañas, su cine , las canciones de Amaral y Oscar López) y el brandy Carlos III. De Amaral además me gustan mucho sus piernas : largas, esculturales, muy atractivas. Creo que la tendría conmigo toda una tarde aprendiendo informática mientras ella me canta sus bonitas melodías.
Ultimamente he visto una película de Luis Buñuel : Ensayo de un crimen . Maravillosa. Te la recomiendo.
******
Recently I've seen a Luis Buñuel movie : An assasin trial . Simply wonderful. recommended.
Edited : http://www.imdb.com/title/tt0048037/
You can get on the web.
Looking for the last version of "La Tregua" by Benedetti I discovered an uncomplicated comedy : Carlos, siete mujeres y un homosexual. It's not a good movie, but is a pleasant time and Adriana have good legs. A mexican movie.
Edited : http://www.imdb.com/title/tt0395429/
From Spain I recommended Garci and the movie "Ninette" with Elsa Pataky. Splendid legs. All splendid. A wonderful comedy for all times with spanish people in the old Paris.
Edited : http://www.imdb.com/title/tt0442371/
If you have any problem gettings this films tell me. I'll help you.
-
Do you mean like this?
@echo off
for /f "delims==" %%A in (' type "c:\test.txt" ') do (
set variable=%%A
)
echo the value is %variable%
I can try this ?
@echo off & setlocal ENABLEEXTENSIONS
set "first="
for /f "delims=" %%a in ('more ^< numbers.txt') do (
if not defined first set first=%%a
)
echo/%first%
in my case :
@echo off & setlocal ENABLEEXTENSIONS
set "first="
for /f "delims=" %%a in ('more ^< Y:\GABINETE/PROYECTOS/MODELOS/CONTROL\MiControl.txt') do (
if not defined first set first=%%a
)
echo/%first%
The value will be the value of varexpediente, because is the first line in the MiControl.txt
I suppose the variable is %first%
How can I do the bat for launch word and open by a predefined bookmark ?
I suppose ..... :
entrarenworddeposicionvariableporunmarc ador.bat
@echo off & setlocal ENABLEEXTENSIONS
set "first="
for /f "delims=" %%a in ('more ^< Y:\GABINETE/PROYECTOS/MODELOS/CONTROL\MiControl.txt') do (
if not defined first set first=%%a
)
rem "%programfiles%\Microsoft Office\Office10\WINWORD.EXE" /t "Y:\GABINETE\PROYECTOS\%varexpediente%\%varexpediente%.Proyecto.doc" /mmacro3
"%programfiles%\Microsoft Office\Office10\WINWORD.EXE" /t "Y:\GABINETE\PROYECTOS\%first%\%first%.Proyecto.doc" /mmacro3
Note : Really I am made a mess with the actualization of the variable varexpediente, because now i don't get actualize the value. I am going to close the session and retry...
-
Alternatively, you could write a line to a text file & then start Excel and get it to read the file
Function TextFileLine() As String
Dim MyString
Open "c:\test.txt" For Input As #1
Input #1, MyString
Close #1
TextFileLine = MyString
End Function
I am trying this, but I obtain error....
Sub Auto_Open()
Function TextFileLine() As String
Dim MyString
Open "c:\test.txt" For Input As #1
Input #1, MyString
Close #1
TextFileLine = MyString
End Function
End Sub
(http://img269.imageshack.us/img269/9765/screenshot1253585747.jpg) (http://img269.imageshack.us/i/screenshot1253585747.jpg/)
-
=
-
It is solved!!! You can use run a Sub at Workbook Open time, to read the text file and get the text line and put it in a cell.
Private Sub Workbook_Open()
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
[A1] = MyString
End Sub
Here is a batch file to test it
@echo off
echo Send string to Excel
set folder=%HOMEDRIVE%%HOMEPATH%
set filename=Excel-value.txt
echo Folder: %folder%
echo File: %filename%
set /p mystring="Enter string to import into Excel ? "
echo.
echo %mystring% > "%folder%\%filename%"
echo Contents of file:
type "%folder%\%filename%"
echo.
echo Ready to start Excel
pause
echo.
start /WAIT "" "S:\Test\Excel\Environ004.xls"
echo.
echo Excel finished
pause
And a picture
(http://i124.photobucket.com/albums/p29/badoit/Excel-VBA3.jpg)