vba 将 Excel 文件附加到完成的 Visual Basic 项目

Attaching Excel File to finished Visual Basic project

提问by CaffeinatedCoder

I've created a Visual Basic project with many forms. In the project I write information to an excel file that I reference the location of on my local (C:) drive where the excel file resides. By reference I mean, I open an excel instance ten provide the source=local path on my hard drive. My question is how do I attach the excel file to the project so I don't have to reference the local location and can run the program on a different computer. In other words how do I bundle the excel file with the program? And how would I change the call to the excel file once I have it "bundled" to the program?

我创建了一个包含多种表单的 Visual Basic 项目。在项目中，我将信息写入一个 excel 文件，我引用了该 excel 文件所在的本地 (C:) 驱动器上的位置。通过引用，我的意思是，我打开一个 excel 实例十在我的硬盘驱动器上提供 source=local 路径。我的问题是如何将 excel 文件附加到项目中，这样我就不必引用本地位置并且可以在不同的计算机上运行该程序。换句话说，我如何将 excel 文件与程序捆绑在一起？一旦我将它“捆绑”到程序中，我将如何更改对 excel 文件的调用？

采纳答案by Jeff

Add the excel file to the project. Go to solution explorer and right click on the excel file and select properties. Change the Copy to Output Directory to Copy Always. Now the file will be in the same directory as your exe. You can use Reflection.Assembly.GetExecutingAssemblyto get the directory.

将excel文件添加到项目中。转到解决方案资源管理器并右键单击 excel 文件并选择属性。将复制到输出目录更改为始终复制。现在该文件将与您的 exe 位于同一目录中。您可以使用Reflection.Assembly.GetExecutingAssembly来获取目录。

To add the file to the project:

要将文件添加到项目：

Right click on project > Add > Existing Item > Your Excel File.xls

右键单击项目 > 添加 > 现有项目 > 您的 Excel File.xls

To include the file in build:

要在构建中包含文件：

Right click on the file > Properties > Copy to Output Directory. Set this value to either Copy always or Copy if newer.

右键单击文件 > 属性 > 复制到输出目录。将此值设置为“始终复制”或“如果更新则复制”。

Here is the code to get the path to the excel file:

这是获取excel文件路径的代码：

Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Your Excel File.xls")

xlPath should now be the full path to the excel file.

xlPath 现在应该是 excel 文件的完整路径。

回答by Ahmed Yahia

i think iam not really getting what you mean but if you need to open the excel file with one click in the app and you dont want to write the path of the excel file (may be because you want you app portable) so the answer is to

我认为我没有真正理解你的意思，但如果你需要在应用程序中一键打开 excel 文件并且你不想写 excel 文件的路径（可能是因为你希望你的应用程序可移植）所以答案是到

1. put the excel file anywhere in the project folder
2. add existing item (*.* filter) and select the excel file
3. change the properties of the excel file build action: resource Copy to output directory:copy always
4. open the properties of the solution and click resources the drag the file and drop it

5. use the (using system.reflection& using system.io& using system.resources)

   then write the code like this

   string sPath = Path.GetTempFileName();
   File.WriteAllBytes(sPath, Properties.Resources.excel File name here);
   

1. 将excel文件放在项目文件夹中的任何位置
2. 添加现有项目 ( *.* filter) 并选择 Excel 文件
3. 更改excel文件构建操作的属性：资源复制到输出目录：始终复制
4. 打开解决方案的属性并单击资源拖放文件

5. 使用 ( using system.reflection& using system.io& using system.resources)

   然后像这样写代码

   string sPath = Path.GetTempFileName();
   File.WriteAllBytes(sPath, Properties.Resources.excel File name here);
   

then take that (spath) and use it as your path so then you can change the project location with the excel file inside it and it will still be working without writing a constant path for it

然后将那个 ( spath) 用作您的路径，这样您就可以使用其中的 excel 文件更改项目位置，并且它仍然可以工作，而无需为其编写恒定路径

回答by Pradeep Kumar

MODIFIED REPLY AFTER USER COMMENTS

用户评论后修改回复

1) First create a Setting (named say MyExcelFile) to save the Name and Path of your excel file.

1）首先创建一个设置（命名为 say MyExcelFile）来保存你的 excel 文件的名称和路径。

2) Now you can use My.Settings.MyExcelFileto refer to your excel file path.

2) 现在您可以使用My.Settings.MyExcelFile来引用您的 excel 文件路径。

3) If excel file is not found at the desired location, you can reset it by opening a File Open Dialog and asking the user to specify the file location.

3) 如果在所需位置找不到 excel 文件，您可以通过打开文件打开对话框并要求用户指定文件位置来重置它。

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    If Not IO.File.Exists(My.Settings.MyExcelFile) Then
        Dim ofd As New OpenFileDialog
        ofd.Filter = "Excel Files (*.xls, *.xlsx)|*.xls; *.xlsx"
        ofd.Title = "Specify Excel File Location"
        If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
            My.Settings.MyExcelFile = ofd.FileName
            My.Settings.Save()
        End If
    End If
End Sub