CODEWITHSUNDEEP
ms-accessvba
MAW74656
MAW74656

Reputation: 3539

Function ignoring parameter?

I have VBA function in Access that should return the path of the special folder (MyDocuments, Desktop, etc) based on a string variable I passed it. However, I'm always getting the public desktop "C:\Users\Public\Desktop" instead of what I pass in. Here is the function code:

Function SpecialFolderPath(whichFolder As String) As String
    Debug.Print whichFolder

    Dim objWSHShell As Object
    Dim strSpecialFolderPath

    Set objWSHShell = CreateObject("WScript.Shell")

    SpecialFolderPath = objWSHShell.SpecialFolders(whichFolder)

    Debug.Print SpecialFolderPath

    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:

    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

So, no matter what I pass in as whichFolder, I always get C:\Users\Public\Desktop. How can I correct this?

EDIT:

I'm calling this function in the following ways:
- DoCmd.OutputTo acOutputQuery, "BoxForecasting_Jobs", "ExcelWorkbook(*.xlsx)", SpecialFolderPath("MyDocuments") & "\BoxForecastByJobs.xlsx", False, "", , acExportQualityPrint
-Set oWB = oXL.Workbooks.Open(SpecialFolderPath("MyDocuments") & "\BoxForecastByJobs.xlsx")

Upvotes: 0

Views: 91

Answers (2)

Serenity
Serenity

Reputation: 73

It is a collection. See what's in it using a for each loop.

Set wshshell = CreateObject("WScript.Shell")

For each thing in wshshell.SpecialFolders 
    wscript.echo thing
Next

These are the names it accepts.

  • AllUsersDesktop

    AllUsersStartMenu

    AllUsersPrograms

    AllUsersStartup

    Desktop

    Favorites

    Fonts

    MyDocuments

    NetHood

    PrintHood

    Programs

    Recent

    SendTo

    StartMenu

    Startup

    Templates

Upvotes: 2

Amen Jlili
Amen Jlili

Reputation: 1934

Change this line:

SpecialFolderPath = objWSHShell.SpecialFolders(whichFolder)

to:

SpecialFolderPath = objWSHShell.SpecialFolders("" & whichFolder & "")

I've tweaked your code a bit. Added WhichFolder = "Templates", made it a sub and returned the result through a msgbox.

My end result: enter image description here

Upvotes: 2

Related Questions