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.
Works for me with
FileDialog(msoFileDialogFolderPicker)
Tested on Windows Vista - Excel 2007
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
andNew Project
. Rename it toCustomOFD
and and hit theOK
.Right-click the
CustomOFD
Project in the Solution Explorer and SelectAdd References
Add references to the
System.Windows.Forms
as shown in the below imgRight-click
Class1.cs
in the Solution Explorer and rename it toCustomOFD.cs
.Double click your
CustomOFD
and replace the code with the one from belowNote: 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 selectProperties
In the Properties window go to Application tab and click
Assembly Info
and tick theMake COM-Visible
boxThen go to the
Build
tab and tick Register for COM interopRight-click the project and select
Build
from the menuNow look in the Output tab as it shows you where the library was compiled to
usually its
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 selectReferences
Click the
Browse
button and navigate to theCustomOpenFileDialog.tlb
file and click OK add to the list of referencesCopy paste the code for module
finally, run the sub and enjoy the computer as the default location for the customized OpenFileDialog box!
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.
http://technet.microsoft.com/library/ee176604.aspx
http://www.vbaexpress.com/kb/getarticle.php?kb_id=405