VBA: Get Excel FileDialogOpen to point to "My Computer" by default

Question

I'm trying to get excels save and open dialog boxes to open to "my computer" by default so the user can select a drive from there.

I have got the dialog boxes to open to any path on any drive or my documents etc but can't seem to find a way for it to open to my computer.

This is the code i'm using at the moment and it works fine for a known path:

MsgBox objFolders("desktop")
ChDrive objFolders("desktop")
ChDir objFolders("desktop")

strFileName = appRemoteApp.Workbooks("Export Template.xlsm").Application.GetSaveAsFilename(objFolders("desktop") & "\Replica Export " & UserName & " " & Format(Date, "yymmdd") & ".xlsm", FileFilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm,")     

Also, I have found this from this site.

If you paste ::{20D04FE0-3AEA-1069-A2D8-08002B30309D} into windows explorers address bar it takes you to my computer but if I use this in my VBA code

ChDir "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}"

it says it cant find the directory or something. So not sure if there is a work around for this or something.

This did not work either:

ChDir "C:\WINDOWS\explorer.exe /root,,::{20D04FE0-3AEA-1069-A2D8-08002B30309D}" 

The reason i'm wanting to have the dialog boxs open to computer is that we will be hosting the excel doc on a windows server with access though RemoteApp and remote desktop. The users will not have access (rights) to the servers drives and folders etc, they will only have access to their own drives on their local machines which will be mapped and are visible under the servers "My Computer" folder for lack of a better word. The master document on the server generates a replica using VBA code and is then saved to the users local hard drive.

Donegan

Answer 1

AFAIK there is no pure VBA solution to override the original behaviour. You can use an alternative from Robert Mearns answer but it doesn't show the windows form so it's less customizable.

Follow this answer if you want to achieve the exact effect - FileOpenDialog.

You can print all the environmental variables using the Environ$() function. This will not show any variable directly pointing to MyComputer therefore you can't pass it to the .InitialFileName property.

MyComputer is not a physical location that you can access through cmd. I think of it as an abstract Interface and it's quite difficult to explain how VBA and .InitialFileName uses a string to access a location.

Well, the only workaround the problem I can think of it's to use an external library written in for example C# that can access the MyComputer.

It's easier than it sounds!

Follow the below steps to create your Custom OpenFileDialog.

You need a Visual Studio Express For Desktop - it's free to download and use.

After installation - run as Administrator! (it's necessary for the libraries to get registered)

Select File and New Project. Rename it to CustomOFD and and hit the OK.

Right-click the CustomOFD Project in the Solution Explorer and Select Add References

Add references to the System.Windows.Forms as shown in the below img

Right-click Class1.cs in the Solution Explorer and rename it to CustomOFD.cs.

Double click your CustomOFD and replace the code with the one from below

using System;
using System.Runtime.InteropServices;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CustomOpenFileDialog
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("541EDD34-4CDC-4991-82E9-6FC23F904B5B")]
    public interface ICustomOFD
    {
        DialogResult ShowDialog();
        string FileName();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("E33102F0-B3C0-441C-8E7A-B9D4155A0D91")]
    public class CustomOFD : ICustomOFD
    {
        private OpenFileDialog box = new OpenFileDialog();

        public CustomOFD()
        {
            box.Multiselect = false;
            box.Title = "Select file";
            box.InitialDirectory = "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}";
        }

        public DialogResult ShowDialog()
        {
            return box.ShowDialog();
        }

        public string FileName()
        {
            return box.FileName;
        }
    }
}

Note: you can generate a new GUID for your own class using the Tools => Create GUID and replace it with your own, if you wanted to...

Right-click the CustomFileOpenDialog in the Solution Explorer and select Properties

In the Properties window go to Application tab and click Assembly Info and tick the Make COM-Visible box

Then go to the Build tab and tick Register for COM interop

Right-click the project and select Build from the menu

Now look in the Output tab as it shows you where the library was compiled to

usually its

c:\users\administrator\documents\visual studio 2012\Projects\CustomOpenFileDialog\CustomOpenFileDialog\bin\Debug\CustomOpenFileDialog.dll

Ok. Now save and close VS.

Open Excel and go into VBE ALT+F11 and insert a standard module

Click Tools on the menu bar and select References

Click the Browse button and navigate to the CustomOpenFileDialog.tlb file and click OK add to the list of references

Copy paste the code for module

Option Explicit

Sub Main()

    Dim ofd As New CustomOFD
    Set ofd = New CustomOFD
    
    ofd.ShowDialog
    
    Debug.Print ofd.Filename

End Sub

---

finally, run the sub and enjoy the computer as the default location for the customized OpenFileDialog box!

Chilly

Hi Mehow, Awesome detail man - really appreciated. I'm going to give this a good try. Its a first for me and C#. But you have really put in a step by step guide, wow. . . Will let you know how I fare. Thanks again. – Donegan

Hay i think i just found it. . . you need to add ".CustomOFD" to the end of "Dim ofd As New CustomOFD" and "Set ofd = New CustomOFD" so it reads: "Dim ofd As New CustomOFD.CustomOFD" and "Set ofd = New CustomOFD.CustomOFD". Now it seems to be working! Awesome!! Awhile later. . . I have now also been able to add filters and default file names to the dialog boxes. Theres probably a better way but this seems to work. Should i put my modified code on the post? if so how best? Through "Answer Your Question"? I'm new to this forum! Thanks a mill again for your Help! – Donegan

@Donegan this collision happened because you've named your namespace the same name as the Class!!! The namespace should have been CustomOpenFileDialog :) – Chilly

To install / use this addon (Class library) on a non development machine check my follow on question Regisering a Dll written in C# on a windows server machine - (Install a C# Class Library) – Donegan

Answer 2

I cannot see a way to use the GetSaveAsFilename or similar dialogs to open on Computer or My Computer.

It is possible to prompt the user to select a folder using VB Script. The root displayed is Computer and the user can select a folder. The file can then be saved to the selected folder programatically.

Sub Test()

    MsgBox BrowseForFolder(MyComputer)

End Sub

http://technet.microsoft.com/library/ee176604.aspx

Function MyComputer() As Variant

Dim objShell As Object, objFolder As Object

    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(&H11&)
    MyComputer = objFolder.self.Path
    Set objShell = Nothing
    Set objFolder = Nothing

End Function

http://www.vbaexpress.com/kb/getarticle.php?kb_id=405

Function BrowseForFolder(Optional OpenAt As Variant) As Variant

Dim ShellApp As Object

    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

    Set ShellApp = Nothing

End Function

Extine

+1 great and quick solution. I have built my answer around the original FileDialogOpen but this is a great alternative! – Chilly

Hay Robert, I Really appreciate you sharing this with me!!! I'm going to give Mehow's suggestion a try because it would be ideal if i could give the user a look a like interface to the standard dialog box. However if i fail in getting his to work (I have never used C# before) I will definitely give you suggestion a try. Seems strange that windows has no "name" for my computer, sinse the standard dialog box can browse to it and all. Thanks again for your input. – Donegan

Answer 3

.InitialFileName = "Computer"

Works for me with FileDialog(msoFileDialogFolderPicker)

Tested on Windows Vista - Excel 2007

Teacup

Answer 4

I don't know if this works on all flavours of windows or even VB(A)...

What I'm Using:

• Windows 10
• Excel VBA (Native Office 365 Edition)
• Dialog Call: Application.FileDialog(msoFileDialogFolderPicker)

If you set "InitialFileName" to an empty string ("") the file picker should show "This PC" as the location.

Assuming this works for your setup, I'd say it's the easiest way to achieve this... At the very least try it before going through the longer solutions above!

Hominy