Solved: Getb Special Folders with Enums

[xluser2007]

Hi All,

I am trying to adapt the great code from Ron de Bruin here (http://www.rondebruin.nl/folder.htm) and generalise it with enums, to get the path for Windows special folder.

Private Enum WinSpecialFolderType

WindowsFolder = 0 ' The Windows folder contains files installed by the Windows operating system.
SystemFolder = 1 ' The System folder contains libraries, fonts, and device drivers.
TemporaryFolder = 2 'The Temp folder is used to store temporary files. Its path is found in the TMP environment variable.

End Enum

' Integrate th Enumerations abve into the special folders method

Function GetSpecialFolderPath(specialFolderType As WinSpecialFolderType)

Dim FSO As Object, SpecFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set SpecFolder = FSO.GetSpecialFolder(specialFolderType)

MsgBox SpecFolder
'Open folder in Explorer
Shell "explorer.exe " & SpecFolder, vbMaximizedFocus

End Function
It is not liking the way the Enum is Used or defined. It highlights the line:


Sub GetSpecialFolderPath(specialFolderType As WinSpecialFolderType)
with the error message:


Compile error: User defined type not defined

Could anyone please assist in debugging this code?

[mdmackillop]

From 2007 Help
Private Enum and user-defined types cannot be used as parameters or return types for public procedures, public data members, or fields of public user-defined types
A Public procedure is visible to all modules (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) in a project (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12), while a Private Enum type is not visible outside its own module. This error has the following cause and solution:


Your Public procedure is in a Public class, but it returns a value or has a parameter (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) that is defined in a standard module (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) or in a Private class. Declare the Enum Public. It must be in a class module (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12).



Enum WinSpecialFolderType

WindowsFolder = 0 ' The Windows folder contains files installed by the Windows operating system.
SystemFolder = 1 ' The System folder contains libraries, fonts, and device drivers.
TemporaryFolder = 2 'The Temp folder is used to store temporary files. Its path is found in the TMP environment variable.

End Enum

Sub test()
Dim fld As WinSpecialFolderType
fld = 2
GetSpecialFolderPath (fld)
End Sub

' Integrate the Enumerations above into the special folders method

Function GetSpecialFolderPath(SpecialFolderType As WinSpecialFolderType)

Dim FSO As Object, SpecFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set SpecFolder = FSO.GetSpecialFolder(SpecialFolderType)

MsgBox SpecFolder
'Open folder in Explorer
Shell "explorer.exe " & SpecFolder, vbMaximizedFocus

End Function

[xluser2007]

From 2007 Help
Private Enum and user-defined types cannot be used as parameters or return types for public procedures, public data members, or fields of public user-defined types
A Public procedure is visible to all modules (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) in a project (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12), while a Private Enum type is not visible outside its own module. This error has the following cause and solution:
Your Public procedure is in a Public class, but it returns a value or has a parameter (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) that is defined in a standard module (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12) or in a Private class. Declare the Enum Public. It must be in a class module (http://office.microsoft.com/search/redir.aspx?AssetID=HV012009291033&CTT=5&Origin=HV012024591033&app=EXCEL.DEV&ver=12).
Hi md,

many thanks for your great code, works a treat. Also thank you for looking into the main cause of the issue.

I was wondering - how do you make a module Private?

Also the way it is written, you can pass the variable through as:

Sub test2()

GetSpecialFolderPath (TemporaryFolder)

End Sub
So no need to dimension it in the test, do you agree? Or is there a specific reason that you wrote out your test as:

Sub test()
Dim fld As WinSpecialFolderType
fld = 2
GetSpecialFolderPath (fld)
End Sub
I'm just clarifying, so that I can apply this correctly.

Also I have one additional query:

If I want to display and open all three 'special' folders using the "GetSpecialFolderPath" macro, I tried the following:

Sub OpenAllFolders()

GetSpecialFolderPath (TemporaryFolder)

ThisWorkbook.Activate

GetSpecialFolderPath (SystemFolder)

ThisWorkbook.Activate

GetSpecialFolderPath (WindowsFolder)

ThisWorkbook.Activate

End Sub
However the issue is that once it executes the Shell command to open the special folder, it shifts focus to the Window Explorer and the second MsgBox doesn't come up (as we have shifted away from Excel to Explorer).

I gather this is an issue with not having a Waiting time after the Shelled command to execute the next line of code in Excel (as the ThisWorkbook.Activate does not seem to work)?

If so, could you please explain how to adapt Chip pearsons Shell and Wait routine as written here (http://www.cpearson.com/excel/ShellAndWait.aspx)?

Thanks again for your help, if ou coul please aasist with the above queries it would greatly help my understanding.

[Kenneth Hobs]

Try the routine that I posted to for a shell wait.
http://www.vbaexpress.com/forum/showthread.php?t=25961

[xluser2007]

Try the routine that I posted to for a shell wait.
http://www.vbaexpress.com/forum/showthread.php?t=25961
Hi Ken,

Appreciate your input. As I am going to be doing a few pieces of code that may require Shell and Wait, I was hoping to learn using Chip pearsons specific method (just so I can standardise it and refer anyone to the documented website). For a newb like me, it is probably easier to understand this documented method, I hope you understand. (Though given your prowess in VBA, I'm sure your method is fantastic also, just safer for me to learn this general method for all Shell and Wait queries.)

As such, I tried Chip's method as follows:

' Integrate the Enumerations above into the special folders method

Function GetSpecialFolderPath2(SpecialFolderType As WinSpecialFolderType) As String

Dim FSO As Object, SpecFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set SpecFolder = FSO.GetSpecialFolder(SpecialFolderType)

GetSpecialFolderPath2 = SpecFolder

End Function

' This is just a wrapper Function for C pearsons ShellandWait macro output

Sub ShellandWaitSpecialFolder(strInputPath As String)

Select Case ShellAndWait(strInputPath, 2000, vbMaximizedFocus, PromptUser)

Case 0

MsgBox "Success"

Case 1

MsgBox "Failure"

Case 2

MsgBox "TimeOut"

Case 3

MsgBox "InvalidParameter"

Case 4

MsgBox "SysWaitAbandoned"

Case 5

MsgBox "UserWaitAbandoned"

Case 6

MsgBox "UserBreak"

End Select

End Sub

' This the routine in which I am testing the revised code
' The Msgboxes don't flow on proprly when focus shifts from Excel to Windows Explorer
' Even when using ShellandWait

Sub test4()

Select Case MsgBox("Do you want to continue and open the TEMP Folder ", vbYesNo Or vbExclamation Or vbDefaultButton1, "Continue")

Case vbYes

ShellandWaitSpecialFolder ("explorer.exe " & GetSpecialFolderPath2(TemporaryFolder))

Case vbNo

MsgBox "WINDOWS Folder not opened"

Exit Sub

End Select

Select Case MsgBox("Do you want to continue and open the WINDOWS Folder ", vbYesNo Or vbExclamation Or vbDefaultButton1, "Continue")

Case vbYes

ShellandWaitSpecialFolder ("explorer.exe " & GetSpecialFolderPath2(WindowsFolder))

Case vbNo

MsgBox "WINDOWS Folder not opened"

Exit Sub

End Select

End Sub

But it would not cycle through the Msgboxes properly even with the ShellandWait method (of Cpearson) applied as above.

Could you please explain if I am applying Chip's method incorrectly, and if so, how to correct the above for it?

Thanks and kind regards,

[mdmackillop]

Sub test()
Dim fld As WinSpecialFolderType
fld = 2
GetSpecialFolderPath (fld)
End Sub

If you are using Option Explicit, then fld needs to be dimmed. The function requires a specific type, so it makes sense to dim it as the required data type.

[mdmackillop]

But it would not cycle through the Msgboxes properly even with the ShellandWait method (of Cpearson) applied as above.

Could you please explain if I am applying Chip's method incorrectly, and if so, how to correct the above for it?

Can you post a workbook containing your whole code?

[xluser2007]

If you are using Option Explicit, then fld needs to be dimmed. The function requires a specific type, so it makes sense to dim it as the required data type.


malcolm, I just tried without Dimensioning the variable in a public module with Option Explicit and it worked.

I didn't think it was necessary to Dim as we Dimension it in the "GetSpecialFolderPath" module which relies on our public enumeration list of the SpecialFolderTypes.

I have attached a workbook as a reply to next post to show an example of this test and the ShellandWait query.

Thanks for your interest.

[xluser2007]

Hi malcolm, please find the workbook with the full code and the test for the "GetSpecialFolderPath" macro.

They are stored in "Module2".

Thanks and regards,

[xluser2007]

Hi md,

Did you get a chance to look into this further?

[Kenneth Hobs]

I don't see any way around the problem. It works fine with other shells such as notepad or bat files.

If you don't need a wait time, there are other methods that could be used depending on your goal.

[xluser2007]

I don't see any way around the problem. It works fine with other shells such as notepad or bat files.

If you don't need a wait time, there are other methods that could be used depending on your goal.
Hi Ken,

Sure, if you feel the above approach won;t work, I am keen to learn how to approach the above problem using aletrantive methods.

This is rather a simple sort of output (opening 3 folders but allowing Msgboxes to display sequentially), but a good problem for me to understand how to approach a "Wait" methiod.

BTW, was i applying Chip's methid correctly above? Given I am a newb, I thought that I may be doing it incorrectly. Did you by any chance test the above (generic) code? Any success Ken?

I can't quite understand why the generalised ShellandWait will not work for even this simple problem.

Thanks for your interest.

[Kenneth Hobs]

Yes, your approach is fine. It is explorer that is the problem. If you did it for say Notepad, it would work as you should expect.

There a number of ways to browse for a folder. I usually do that rather than using Explorer.

Sub Test()
MsgBox BrowseForFolder(MyComputer)
MsgBox BrowseForFolder(TempFolder)
End Sub
'http://www.microsoft.com/technet/scriptcenter/guide/sas_fil_higv.mspx?mfr=true
Function MyComputer() As Variant
Dim objShell As Object, objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H11&)
'MsgBox objFolder.self.Name
MyComputer = objFolder.self.path
Set objShell = Nothing
Set objFolder = Nothing
End Function
Function TempFolder() As Variant
TempFolder = Environ("temp")
End Function
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=405
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename (file://\\servername\sharename). All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

[xluser2007]

Ken,

Thanks for your wonderful code - very nice way of approaching this problem.

I tested it as follows (just added more sequential msgboxes in your "test" macro.

Sub Test()

MsgBox BrowseForFolder(MyComputer)

MsgBox "next one"

MsgBox BrowseForFolder(TempFolder)

MsgBox "All Done"

End Sub

The only one issue is, for the:


MsgBox BrowseForFolder(MyComputer)

If you click OK on the folder, the MsgBox comes up as a "False" response. Which should actually say the name of the folder (the temp folder works as it displays "C:\WINDOWS\temp"), just wondering why this doesn't work?


Yes, your approach is fine. It is explorer that is the problem. If you did it for say Notepad, it would work as you should expect.


I'm a bit confused as to why this is Ken, one would think that Excel being part of microsoft Windows would have really nice integration with other core Windows applications such as Explorer - any ideas why Chip pearson's shell and Wait doesn;t specifically work for one Windows application (notepad) and not another (Windows explorer)?

[oleneazer]

Hi All,



I test this way


Sub Test()
BrowseForFolder (MyComputer)
BrowseForFolder (TempFolder)
End Sub

And there's no "False" message.

[xluser2007]

Hi All,



I test this way


Sub Test()
BrowseForFolder (MyComputer)
BrowseForFolder (TempFolder)
End Sub
And there's no "False" message.
Hi oleanazer, that's because you have taken off the msgbox's before the BrowseForFolder macro.

the reason that Ken tested with them (presumably) is to check that once you click OK on the Browse for Folder, for the my Computer fodler for example, the Msbox should display the folder path.

I get a False when I do this for My Computer, and not for Temp. Which suggests that maybe it is not recognising the My Computer folder propoerly? hence I just wanted to ask ken and clarify if I was interpret8ing anything incorrectly.

hope this clarifies my query and why your test is different.

[Kenneth Hobs]

The reason you get False for MyComputer when you press OK is because there is no path. It is a "Special" folder. Environment variables like Temp typically have a path.

I guess there are a few posts about this sort of problem but I have not seen any real solution. Some talked about using DoEvents but Chip's code used it where it seemmed logical to me.

I make no guesses about Microsoft thinking. Sometimes one can make a decent guess. MSDN articles sometimes help and sometimes not.

[xluser2007]

The reason you get False for MyComputer when you press OK is because there is no path. It is a "Special" folder. Environment variables like Temp typically have a path.

I guess there are a few posts about this sort of problem but I have not seen any real solution. Some talked about using DoEvents but Chip's code used it where it seemmed logical to me.

I make no guesses about Microsoft thinking. Sometimes one can make a decent guess. MSDN articles sometimes help and sometimes not.
Thanks for updating me on this Kenneth. makes sense (somewhat?). It's difficult to understand exactly why these inconsistencies in methods can occur within microsoft office applications (e.g. Word, Excel) accessing other microsoft programs (e.g. explorer). As you said it really is a "decent guess scenario" with these issues.

I'm marking this issue as solved, but will post back for the shell and Wait quries for other applications that i am hoping to apply it to.

Again thanks for your generous help Ken and md :friends:.

regards