VBA Excel: how to get location of Program Files

andy7789
andy7789 used Ask the Experts™
on
Hi X-perts,

I need to find location of Program Files from the VBA code, regardless what Os is, i.e.

C:\Program Files for winxp or
C:\Program Files (x86) for wistra and windows 7

Please advise

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:

Author

Commented:
Thank you! this code seems to be working

How can we make sure that it points to x86 directory on 64-bit machines, i.e. to Program Files (x86)  and not to "Program Files"?
Public Function GetProgramFilesFolder() As String
       Const PROGRAM_FILES = &H26&
       Dim objShell As Object
       Dim objFolder As Object
       Dim objFolderItem As Object

       Set objShell = CreateObject("Shell.Application")
       Set objFolder = objShell.Namespace(PROGRAM_FILES)
       Set objFolderItem = objFolder.Self

      Debug.Print objFolderItem.Path
End Function

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
In VBS, one method was already provided:

Const PROGRAM_FILES = &H26&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(PROGRAM_FILES)
Set objFolderItem = objFolder.Self
strProgramFiles = objFolderItem.Path
MsgBox strProgramFiles

another way is this:

Set objShell = CreateObject("WScript.Shell")
strProgramFiles = objShell.ExpandEnvironmentStrings("%ProgramFiles%")
MsgBox strProgramFiles


Regards,

Rob.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Most Valuable Expert 2012
Top Expert 2014

Commented:
You could try

Debug.Print objFolderItem.Path &" (x86)"

Regards,

Rob.

Author

Commented:
Rob,

I cannot try

Debug.Print objFolderItem.Path &" (x86)"

because I don't know in advance whether it is 32 or 64 bit OS. I tried the code on 64-bit machine and it shows a correct path to Program Files (x86). However i wanted to make sure that that code always points to 32-bit Prg Files directory.

Pls advise
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
For Excel up to 2007, and the 32-bit edition of Office 2010, the code will only give you "Program Files (x86)" on a 64-bit system.  This is because the environment defines %PROGRAM FILES% on a 64-bit machine.

For the 64-bit edition of Excel 2010... why would you be working in the 32-bit folder?
Most Valuable Expert 2012
Top Expert 2014

Commented:
Then what if you just test for the existence of the (x86) folder first, and if that doesn't exist, use the normal folder?

Rob.
Public Function GetProgramFilesFolder() As String
       Const PROGRAM_FILES = &H26&
       Dim objFSO As Object
       Dim objShell As Object
       Dim objFolder As Object
       Dim objFolderItem As Object

       Set objFSO = CreateObject("Scripting.FileSystemObject")
       Set objShell = CreateObject("Shell.Application")
       Set objFolder = objShell.Namespace(PROGRAM_FILES)
       Set objFolderItem = objFolder.Self

       strProgramFiles = objFolderItem.Path & " (x86)"
       If objFSO.FolderExists(strProgramFiles) = False Then strProgramFiles = objFolderItem.Path
       Debug.Print strProgramFiles
       GetProgramFilesFolder = strProgramFiles

End Function

Open in new window

Most Valuable Expert 2012
Top Expert 2014
Commented:
Or, if cyberkiwi is saying that on 64 bit, the environment variable for 32-bit is %PROGRAMFILES%, and 64-bit is %PROGRAM FILES%, then this should work too.

Set objShell = CreateObject("WScript.Shell")
strProgramFiles = objShell.ExpandEnvironmentStrings("%ProgramFiles%")
MsgBox strProgramFiles

Rob.

Author

Commented:
thank you! works perfectly!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial