Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Excel variable for path  (Read 23078 times)

0 Members and 1 Guest are viewing this topic.

Esgrimidor

    Topic Starter


    Hopeful

    Thanked: 1
    Excel variable for path
    « 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.




    Any similar solution is also possible for me .

    Thankxxx
    No dejes que tu felicidad dependa de lo que no depende de tí.

    soybean



      Genius
    • The first soybean ever to learn the computer.
    • Thanked: 469
    • Computer: Specs
    • Experience: Experienced
    • OS: Windows 10
    Re: Excel variable for path
    « Reply #1 on: August 30, 2009, 10:34:09 PM »
    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

      Topic Starter


      Hopeful

      Thanked: 1
      Re: Excel variable for path
      « Reply #2 on: August 31, 2009, 01:17:58 AM »
      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.
      No dejes que tu felicidad dependa de lo que no depende de tí.

      Salmon Trout

      • Guest
      Re: Excel variable for path
      « Reply #3 on: August 31, 2009, 03:19:03 AM »
      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

        Topic Starter


        Hopeful

        Thanked: 1
        Re: Excel variable for path
        « Reply #4 on: August 31, 2009, 03:36:33 AM »
        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 ?

        No dejes que tu felicidad dependa de lo que no depende de tí.

        Salmon Trout

        • Guest
        Re: Excel variable for path
        « Reply #5 on: August 31, 2009, 04:06:20 AM »
        « Last Edit: August 31, 2009, 04:38:14 AM by Salmon Trout »

        Esgrimidor

          Topic Starter


          Hopeful

          Thanked: 1
          Re: Excel variable for path
          « Reply #6 on: August 31, 2009, 02:17:41 PM »
          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.




          No dejes que tu felicidad dependa de lo que no depende de tí.

          soybean



            Genius
          • The first soybean ever to learn the computer.
          • Thanked: 469
          • Computer: Specs
          • Experience: Experienced
          • OS: Windows 10
          Re: Excel variable for path
          « Reply #7 on: August 31, 2009, 02:32:11 PM »
          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

          • Guest
          Re: Excel variable for path
          « Reply #8 on: August 31, 2009, 02:36:42 PM »
          We've done that. We need an env var as well.

          soybean



            Genius
          • The first soybean ever to learn the computer.
          • Thanked: 469
          • Computer: Specs
          • Experience: Experienced
          • OS: Windows 10
          Re: Excel variable for path
          « Reply #9 on: August 31, 2009, 02:41:40 PM »
          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.
          « Last Edit: August 31, 2009, 03:04:28 PM by soybean »

          Salmon Trout

          • Guest
          Re: Excel variable for path
          « Reply #10 on: August 31, 2009, 03:43:27 PM »
          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

          • Guest
          Re: Excel variable for path
          « Reply #11 on: September 04, 2009, 02:15:48 PM »
          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.


          « Last Edit: September 05, 2009, 02:29:11 PM by Salmon Trout »

          Salmon Trout

          • Guest
          Re: Excel variable for path
          « Reply #12 on: September 04, 2009, 04:56:36 PM »
          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

          • Guest
          Re: Excel variable for path
          « Reply #13 on: September 05, 2009, 06:48:32 AM »
          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

            Topic Starter


            Hopeful

            Thanked: 1
            Re: Excel variable for path
            « Reply #14 on: September 05, 2009, 02:21:11 PM »
            I was "outside". I began fast to study all this and comment.

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

            No dejes que tu felicidad dependa de lo que no depende de tí.

            Esgrimidor

              Topic Starter


              Hopeful

              Thanked: 1
              Re: Excel variable for path
              « Reply #15 on: September 05, 2009, 03:31:03 PM »
              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

              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 ?

              No dejes que tu felicidad dependa de lo que no depende de tí.

              Salmon Trout

              • Guest
              Re: Excel variable for path
              « Reply #16 on: September 05, 2009, 05:12:27 PM »
              indicating the path in a bat file outside excel.

              Could I ?




              Do you mean like this?

              Code: [Select]

              @echo off
              for /f "delims==" %%A in (' type "c:\test.txt" ') do (
                  set variable=%%A
                  )
              echo the value is %variable%


              Esgrimidor

                Topic Starter


                Hopeful

                Thanked: 1
                Re: Excel variable for path
                « Reply #17 on: September 07, 2009, 04:50:37 AM »
                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





                No dejes que tu felicidad dependa de lo que no depende de tí.

                Esgrimidor

                  Topic Starter


                  Hopeful

                  Thanked: 1
                  Re: Excel variable for path
                  « Reply #18 on: September 14, 2009, 05:05:56 PM »
                  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. .......

                  No dejes que tu felicidad dependa de lo que no depende de tí.

                  Salmon Trout

                  • Guest
                  Re: Excel variable for path
                  « Reply #19 on: September 15, 2009, 12:09:57 AM »
                  Or only exist during the execution of the bat file. .......

                  Exactly

                  Esgrimidor

                    Topic Starter


                    Hopeful

                    Thanked: 1
                    Re: Excel variable for path
                    « Reply #20 on: September 15, 2009, 12:58:27 AM »
                    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 ?

                    No dejes que tu felicidad dependa de lo que no depende de tí.

                    Salmon Trout

                    • Guest

                    Esgrimidor

                      Topic Starter


                      Hopeful

                      Thanked: 1
                      Re: Excel variable for path
                      « Reply #22 on: September 15, 2009, 08:40:21 AM »
                      I go flying.

                      I left for you a photo of my mountain :

                      No dejes que tu felicidad dependa de lo que no depende de tí.

                      Salmon Trout

                      • Guest
                      Re: Excel variable for path
                      « Reply #23 on: September 15, 2009, 09:18:05 AM »
                      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

                      Esgrimidor

                        Topic Starter


                        Hopeful

                        Thanked: 1
                        Re: Excel variable for path
                        « Reply #24 on: September 15, 2009, 10:58:12 AM »
                        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.
                        No dejes que tu felicidad dependa de lo que no depende de tí.

                        Salmon Trout

                        • Guest
                        Re: Excel variable for path
                        « Reply #25 on: September 15, 2009, 11:35:22 AM »
                        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


                        Esgrimidor

                          Topic Starter


                          Hopeful

                          Thanked: 1
                          Re: Excel variable for path
                          « Reply #26 on: September 15, 2009, 12:13:01 PM »
                          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.



                          No dejes que tu felicidad dependa de lo que no depende de tí.

                          Esgrimidor

                            Topic Starter


                            Hopeful

                            Thanked: 1
                            Re: Excel variable for path
                            « Reply #27 on: September 21, 2009, 07:48:14 PM »

                            Do you mean like this?

                            Code: [Select]

                            @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...

                            No dejes que tu felicidad dependa de lo que no depende de tí.

                            Esgrimidor

                              Topic Starter


                              Hopeful

                              Thanked: 1
                              Re: Excel variable for path
                              « Reply #28 on: September 21, 2009, 08:17:51 PM »
                              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

                              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



                              No dejes que tu felicidad dependa de lo que no depende de tí.

                              Salmon Trout

                              • Guest
                              Re: Excel variable for path
                              « Reply #29 on: September 22, 2009, 12:19:35 AM »
                              =
                              « Last Edit: September 22, 2009, 12:51:14 AM by Salmon Trout »

                              Salmon Trout

                              • Guest
                              Re: Excel variable for path
                              « Reply #30 on: September 22, 2009, 11:34:04 AM »
                              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.

                              Code: [Select]
                              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

                              Code: [Select]
                              @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

                              « Last Edit: September 22, 2009, 12:59:52 PM by Salmon Trout »