none
Windows paths in VB as variables ... how to?

    Question

  • I've added a macro to my floating toolbar in a spreadsheet that then gets plastered to the ribbon.  The calculator gets launched here at home just fine where I have WinXP but I'm sure there'll be a path change at work with Win7.  Currently the script looks like this:

    ---------------------------
    Sub Launch_Windows_CALCULATOR()
    ' from:  http://www.mrexcel.com/archive/VBA/17001.html
    ' CHECK PATH TO CALCULATOR WHENEVER YOU USE THIS SPREADSHEET WHEN YOU MOVE TO A NEW HOST COMPUTER.
    ' THE PATH WILL ALSO LIKELY CHANGE BETWEEN OSs.
        Dim RetVal
        RetVal = Shell("C:\WINDOWS\system32\calc.exe", 1)     ' The "1" is a position upper left of the screen?, JSW
    End Sub
    ---------------------------


    In AutoIt, for example, I'd write a line with a variable this way:

    ---------------------------
    ;====Path to calculator (change as needed) =====
    $exePath = "C:\WINDOWS\system32\calc.exe"
    ;=====================================
        RetVal = Shell($exePath, , 1)     ' The "1" is a position upper left of the screen?, JSW
    ---------------------------

    I like to use that approach so that all editable text is available at the top of a script as sometimes there can be several lines such as the above that the user needs to change when they get to a new host computer, etc.  Keeps things very simple.

    However, is there anything similar in XL that takes the concept of replacing actual strings, etc., with something similar in concept to "$exePath"?

    Thx.

    Thursday, June 13, 2013 9:01 AM

Answers

  • You can find special folders using code like this, for example, to find "My Documents" independent of the current user:

    Private Sub BackUpToMyDocuments()
        Dim MyFilePath As String
        Dim Extension As String
        MyFilePath = MyPCpath("MyDocuments")
        Extension = Left(ThisWorkbook.Name, Len _
        (ThisWorkbook.Name) - 4) & " Backup"
         
        On Error Resume Next '<< in case folder exists
        MkDir MyFilePath & Extension '<< create folder
         'save current version of this book in the folder
        ActiveWorkbook.SaveCopyAs FileName:=MyFilePath & _
        Extension & "\" & Extension & _
        (Format(Now, " mmm d yyyy, hh-mm")) & ".xls"
    End Sub
     
    Public Function MyPCpath$(Folder)
        MyPCpath = CreateObject("WScript.Shell").SpecialFolders _
        (Folder) & Application.PathSeparator
    End Function

    Thursday, June 13, 2013 2:48 PM

All replies

  • You can find special folders using code like this, for example, to find "My Documents" independent of the current user:

    Private Sub BackUpToMyDocuments()
        Dim MyFilePath As String
        Dim Extension As String
        MyFilePath = MyPCpath("MyDocuments")
        Extension = Left(ThisWorkbook.Name, Len _
        (ThisWorkbook.Name) - 4) & " Backup"
         
        On Error Resume Next '<< in case folder exists
        MkDir MyFilePath & Extension '<< create folder
         'save current version of this book in the folder
        ActiveWorkbook.SaveCopyAs FileName:=MyFilePath & _
        Extension & "\" & Extension & _
        (Format(Now, " mmm d yyyy, hh-mm")) & ".xls"
    End Sub
     
    Public Function MyPCpath$(Folder)
        MyPCpath = CreateObject("WScript.Shell").SpecialFolders _
        (Folder) & Application.PathSeparator
    End Function

    Thursday, June 13, 2013 2:48 PM
  • Thanks!  Neat to know.

    It seems, though, if I've interpreted this, that having such an easy variable system is not quite as do-able in XL.  In the scripting language I made an example of above, it's a one-liner:

    $ + any descriptive text you want followed by an "=" sign

    and what that variable you've made is assigned to or what it means.  When you have several variables all at the top of your coding that way, makes it very easy to re-use as you just edit once.  Then many lines down you can refer to that variable time and time again.  Seems in XL you have to define all sorts of things at the same time in order for it to work.  Doesn't seem to save as much time even though you can use MyFilePath later on as needed.

    Well, this is very good to know.

    Thank you.  I learn something new every day.



    • Edited by SGFan Sunday, June 23, 2013 4:03 PM missed words
    Sunday, June 23, 2013 4:02 PM