[Molly Mae]

This isn't homework, just something I've been trying to accomplish. I didn't know where else to ask...

If your familiar with the windows command shell and/or batch scripting, you probably know that windows stores variables in shell32.dll. You can view the semi-permanent values in the environment variable section of system properties. Really, though, this is a MS Excel question for anyone who knows how to integrate VBA.

Here's the situation:

I wrote a simple RPG (like a MUD--except only single-user....so...a SUD) using batch files. Stats and the like are stored as windows variables. Incidentally, variables are temporary unles you use the setx command, which I've done. So all of my stats are saved in the environment variables and are semi-permanent (they can be written over). I've done this to act as a save/load game function.

Now, I want to integrate an auto-updating character sheet using excel. It's possible, from what I've read. Traditionally, I just google something and mess around with it until I figure it out. VBA is more of a headache than I bargained for, however.

My search returned THIS. I've copied their code into a module using the steps provided HERE.

Ultimately, I want to be able to input a variable name (say windir), and have it output the variable value (C:\windows).

Am I doing something incorrectly?

Any help with this would be greatly appreciated.

[EDM]

well....I haven't done modules so i can't really help much......you seem to have done it right....but did you try connecting the excel file to the program by adding the file path using if-else statement?

If you have, then I really don't know how to help you.....unless you can explain what you have done (as in...the steps you made and the code, etc)

*I'm sorry if I've not been of any help....

[Molly Mae]

I shouldn't have to connect the excel spreadsheet to the batch, though. All variables from the game are saved as windows environment variables, so I should be able to pull them directly from windows.

I guess a simpler method of asking would be: "How can I type in an Environment Variable in an Excel spreadsheet and have it return the value of that variable without redefining all Environment Variables as variables local to excel?"

But the only thing I've really done is put that VBA code into the module and saved it. Perhaps =Environ$(%windir%) ? I have to recreate the file, so I'll check that.

[Molly Mae]

Here's what I have in my module now.

I'm trying to call the environment variable windir. It doesn't matter what variable I call, I just want to call one. When I try the function Environ(windir) in excel, it switches over to the Visual Basic Module and returns "Compile Error: Invalid Outside Procedure"

Dim EnvString, Indx, Msg, PathLen    ' Declare variables.

Indx = 1    ' Initialize index to 1.

Do

    ' Get environment variable.

    EnvString = Indx

    ' Check PATH entry.

    If Left(EnvString, 5) = "PATH=" Then

        ' Get length.

        PathLen = Len("PATH")

        Msg = "PATH entry = " & Indx & " and length = " _

               & PathLen

        Exit Do

    Else

        ' Not PATH entry, so increment.

        Indx = Indx + 1

    End If

Loop Until EnvString = ""

If PathLen > 0 Then

    ' Display message.

    MsgBox Msg

Else

    MsgBox "No PATH environment variable exists."

End If

EDIT: Don't trust the color in the codebox, though. Comments are preceded by the single quote (').

Edited September 21, 2010 by Molly Mae

[Guest]

Hey MM, I'm logging in quickly to help a friend here... I haven't been able to get to the site(s) lately and I really miss it, but work is consuming all of my time...

Anyways, back on topic...

I didn't check your code, but there's a way to get the environment variable from VBA using the "environ" statement... You just pass the name of the variable you need to check to this function and it returns the value of the variable.

I'll attach an example I did in MS Excel 2003, hope it helps... If it doesn't work PM me, I'll send the code...

Take care and happy coding!

test.xls

[EDM]

You need to organize your code.....your if-else statements have to be in brackets......If it still doesn't work, try doing Select Case statements

e.g: If (PathLen>0) then...

also, I think you may need to put your Msgbox(Msg) or something like that......

Didn't the program give an error when you ran it???

[Molly Mae]

Hey MM, I'm logging in quickly to help a friend here... I haven't been able to get to the site(s) lately and I really miss it, but work is consuming all of my time...

Anyways, back on topic...

I didn't check your code, but there's a way to get the environment variable from VBA using the "environ" statement... You just pass the name of the variable you need to check to this function and it returns the value of the variable.

I'll attach an example I did in MS Excel 2003, hope it helps... If it doesn't work PM me, I'll send the code...

Take care and happy coding!

test.xls

You, sir, are awesome.

I'm not entirely sure how this is working, but I'll take it.

I've gotten this for your module code:

Sub Botón1_Haga_clic_en()

Cells(2, 3) = displayEnvVar(Cells(2, 2))

End Sub


Private Function displayEnvVar(ByVal strName As String) As String

displayEnvVar = Environ(strName)

End Function

The button macro just calls the whole code, correct? Or is it just the Private Function?

[Guest]

I'm not entirely sure how this is working, but I'll take it.

I've gotten this for your module code:

Sub Botón1_Haga_clic_en()

Cells(2, 3) = displayEnvVar(Cells(2, 2))

End Sub


Private Function displayEnvVar(ByVal strName As String) As String

displayEnvVar = Environ(strName)

End Function

The button macro just calls the whole code, correct? Or is it just the Private Function?

Well what it does is:

a. The button calls the macro (with the weird name, sorry for that): Boton1_Haga_Clic_en(): this as you can see populates cell(2,3) with the result of the next step

b. The private function displayEnvVar() is called, this function takes the name of the environmental variable and returns its value.

c. The displayEnvVar function only calls the function (built-in) Environ.

You can just copy the displayEnvVar function and use it as you wish in your own code/macros... That way you can call it whenever you want and use it to suit your needs...

Well gtg now, I can only log in for a bit in the night....

Let me know if it helps

[Molly Mae]

Well what it does is:

a. The button calls the macro (with the weird name, sorry for that): Boton1_Haga_Clic_en(): this as you can see populates cell(2,3) with the result of the next step

b. The private function displayEnvVar() is called, this function takes the name of the environmental variable and returns its value.

c. The displayEnvVar function only calls the function (built-in) Environ.

You can just copy the displayEnvVar function and use it as you wish in your own code/macros... That way you can call it whenever you want and use it to suit your needs...

Well gtg now, I can only log in for a bit in the night....

Let me know if it helps

It more than helps, Jar. It's precisely what I was looking for.

I owe you one.