VBA：获取Excel FileDialogOpen指向“我的电脑”默认 [英] VBA: Get Excel FileDialogOpen to point to "My Computer" by default
问题描述
我正在努力获取excels保存和打开对话框，默认打开我的电脑，所以用户可以从那里选择一个驱动器。
我让对话框打开任何驱动器或我的文档上的任何路径，但似乎找不到一种方式打开我的电脑。
这个是我现在使用的代码，对于一个已知的路径可以正常工作：
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宏启用的工作簿（* .xlsm） * .xlsm）
另外，我从本网站。
如果您粘贴 :: {20D04FE0-3AEA-1069-A2D8-08002B30309D} 进入Windows资源管理器地址栏，它带你到我的电脑，但如果我在我的VBA代码中使用这个。
ChDir:: {20D04FE0-3AEA-1069-A2D8-08002B30309D}
它说它找不到目录或东西。所以不知道是否有一个这个或某事的工作。
这没有工作：
ChDirC：\WINDOWS\explorer.exe / root ,,, {20D04FE0-3AEA-1069-A2D8-08002B30309D}
我想要将对话框打开到电脑的原因是我们将在Windows服务器上托管excel文档，可以通过RemoteApp和远程桌面。用户将无法访问（权限）服务器驱动器和文件夹等，他们只能访问本地计算机上的自己的驱动器，这些驱动器将被映射，并且在服务器我的电脑文件夹下可见，因为缺乏更好的字。服务器上的主文档使用VBA代码生成副本，然后将其保存到用户本地硬盘驱动器。
AFAIK不是纯粹的VBA解决方案来覆盖原来的行为。您可以使用Robert Mearns答案的替代方法，但它不会显示Windows窗体，因此不太可自定义。
如果要达到确切的效果，请按照以下步骤操作： FileOpenDialog。
您可以使用Environ $（）函数打印所有环境变量。这不会显示直接指向 MyComputer 的任何变量，因此您不能将其传递给 .InitialFileName 属性。
MyComputer 不是您可以通过 cmd 访问的物理位置。我认为它是一个抽象的接口，很难解释VBA和 .InitialFileName 如何使用字符串来访问某个位置。
嗯，唯一的解决方法，我可以想到它是使用写入例如C＃的外部库，可以访问 MyComputer 。
这听起来更容易！
按照以下步骤创建您的自定义OpenFileDialog。
您需要一个 Visual Studio Express for Desktop - 可以免费下载和使用。
安装后 - 运行为管理员！ （图书馆需要注册）
选择文件和新项目。重命名为 CustomOFD ，然后点击确定。
右键单击 CustomOFD 解决方案资源管理器中的项目，然后选择添加引用
添加对 System.Windows.Forms 的引用，如下所示img
右键单击 Class1.cs ，并将其重命名为 CustomOFD.cs 。
双击您的 CustomOFD 并将代码替换为下面的代码
using System;
使用System.Runtime.InteropServices;
使用System.Threading.Tasks;
使用System.Windows.Forms;
命名空间CustomOpenFileDialog
{
[InterfaceType（ComInterfaceType.InterfaceIsDual），
Guid（541EDD34-4CDC-4991-82E9-6FC23F904B5B）]
公共接口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 =选择文件;
box.InitialDirectory =:: {20D04FE0-3AEA-1069-A2D8-08002B30309D};
}
public DialogResult ShowDialog（）
{
return box.ShowDialog（）;
}
public string FileName（）
{
return box.FileName;
}
}
}
注意：你可以使用工具 => 创建GUID 并为您自己的类替换自己的类，如果您想...
右键单击解决方案资源管理器中的 CustomFileOpenDialog ，然后选择属性
在属性窗口中，转到应用程序选项卡，然后单击装配信息并勾选 Make COM-Visible 框
然后转到 Build 注册COM Interop
右键单击该项目，然后从菜单中选择 Build
现在查看输出选项卡，显示库被编译为
通常是
c：\users\administrator\documents\visual studio 2012 \Projects\CustomOpenFileDialog\CustomOpenFileDialog\bin \Debug\CustomOpenFileDialog.dll
好的。现在保存并关闭VS。
打开Excel并进入VBE ALT + F11 并插入标准模块
点击菜单栏上的工具，然后选择参考 / p>
点击浏览按钮并导航到 CustomOpenFileDialog.tlb 文件，然后单击确定添加到引用列表
复制粘贴模块代码
Option Explicit
Sub Main（）
Dim ofd作为新CustomOFD
Set ofd =新CustomOFD
ofd.ShowDialog
Debug.Print ofd.Filename
End Sub
最后，运行子程序并享受计算机作为自定义OpenFileDialog框的默认位置！
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.
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!
这篇关于VBA：获取Excel FileDialogOpen指向“我的电脑”默认的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
