System variables

[Arni Laugdal]

Does someone know how I can use System Variables in VBA
code to get specific folder position?

For example would I like to use:

%UserProfile% instead of
"C:\Documents and Settings\TheUser"

%AppData% instead of
"C:\Documents and Settings\TheUser\Application Data"

%WinDir% instead of
"C:\WINDOWS"

%Temp% instead of
C:\DOCUME~1\arnil\LOCALS~1\Temp

Practical use is for example when sending table to an
Excel workbook on the desktop:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"tblTest", SomeMagicUsing%UserProfile% & _
"Desktop\Test.xls", True

Thanks,
Arni Laugdal.

 

[Karl E. Peterson]

See the Environ() function...

Debug.Print Environ("UserProfile")

Later... Karl

 

[Steve Lang]

That's pretty cool... Where would one find a list of available environ
expressions that this would work for other than the ones that Arni had in
his examples? I could definitely use this in some situations as well.

Steve

See the Environ() function...

Debug.Print Environ("UserProfile")

Later... Karl
--
[Microsoft Basic: 1976-2001, RIP]

Does someone know how I can use System Variables in VBA
code to get specific folder position?

For example would I like to use:

%UserProfile% instead of
"C:\Documents and Settings\TheUser"

%AppData% instead of
"C:\Documents and Settings\TheUser\Application Data"

%WinDir% instead of
"C:\WINDOWS"

%Temp% instead of
C:\DOCUME~1\arnil\LOCALS~1\Temp

Practical use is for example when sending table to an
Excel workbook on the desktop:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"tblTest", SomeMagicUsing%UserProfile% & _
"Desktop\Test.xls", True

Thanks,
Arni Laugdal.

 

[Karl E. Peterson]

Hi Steve --

That's pretty cool... Where would one find a list of available environ
expressions that this would work for other than the ones that Arni had in
his examples? I could definitely use this in some situations as well.

Well, that's just the thing. You can't. Really. Many are user-defined. But, to
get an idea, drop to a command window and type SET at the command prompt. There are
other ways to get at almost all that information -- at least that which the system
provides -- so Environ is really something best reserved for user-defined settings.
That help?

Later... Karl
--
[Microsoft Basic: 1976-2001, RIP]

See the Environ() function...

Debug.Print Environ("UserProfile")

Later... Karl
--
[Microsoft Basic: 1976-2001, RIP]

Does someone know how I can use System Variables in VBA
code to get specific folder position?

For example would I like to use:

%UserProfile% instead of
"C:\Documents and Settings\TheUser"

%AppData% instead of
"C:\Documents and Settings\TheUser\Application Data"

%WinDir% instead of
"C:\WINDOWS"

%Temp% instead of
C:\DOCUME~1\arnil\LOCALS~1\Temp

Practical use is for example when sending table to an
Excel workbook on the desktop:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"tblTest", SomeMagicUsing%UserProfile% & _
"Desktop\Test.xls", True

Thanks,
Arni Laugdal.

 

[Arni Laugdal]

Thank you Karl.
This did all I wanted.
Have nice weekend.
Best regards,
Arni L.

 

[Martin Seelhofer]

Hi there

[...]
But, to get an idea, drop to a command window and type SET
at the command prompt.
[...]

Yeah that's one way to do it. And here's another - maybe closer
to VBA - way:

' returns all environment variables in a
' vbCr-separated string
'
' Note: there's a fixed number of entries
' in the table of environment variables.
' error handling (On Error Resume Next)
' is used to handle the case where all
' entries are occupied...
'
Function EnumEnvironmentStrings() As String
Dim envvar As String
Dim res As String
Dim i As Long
' turn off error handling
On Error Resume Next
res = ""
i = 1
Do
envvar = Environ(i)
If envvar = "" Or Err.Number <> 0 Then Exit Do

' compose result string
res = res & envvar & vbCr
i = i + 1
Loop
EnumEnvironmentStrings = res
End Function


Cheers,
Martin