用于打开文件的 Excel VBA 代码

Excel VBA code to open a file

提问by cemg

I created a macro button to open my daily files from a excel production sheet where I have all the my macro button for specific files.

我创建了一个宏按钮来从 Excel 生产表中打开我的日常文件，在那里我拥有特定文件的所有宏按钮。

The format for all my files are conventionally the same:

我所有文件的格式通常都是相同的：

1. Businese Unit Name: YMCA
2. Year:2012
3. Month: April
4. Week: Week 2
5. Day: 12
6. File Name: YMC Template 041212.xlsm

1. 事业单位名称：YMCA
2. 年份：2012
3. 月份：四月
4. 周：第 2 周
5. 天数：12
6. 文件名：YMC 模板 041212.xlsm

I am having issue with the last excel file name extension. how do I add the MyDaily Template and MyDateProd along with the .xlsm. I have this -J:.....\& myDailyTemplate & myDateProd.xlsm") see below for entire file path names.

我对最后一个 excel 文件扩展名有问题。如何添加 MyDaily 模板和 MyDateProd 以及 .xlsm。我有这个 -J:.....\& myDailyTemplate & myDateProd.xlsm") 请参阅下面的完整文件路径名。

Sub Open_DailyProd()

    Dim myFolderYear As String
    Dim myFolderMonth As String
    Dim myFolderWeek As String
    Dim myFolderDaily As String
    Dim myDateProd As String
    Dim myBusinessUnit As String
    Dim myDailyTemplate As String

    myBusinessUnit = Sheet1.Cells(32, 2)
    myFolderYear = Sheet1.Cells(11, 2)
    myFolderMonth = Sheet1.Cells(12, 2)
    myFolderWeek = Sheet1.Cells(13, 2)
    myFolderDaily = Sheet1.Cells(14, 2)
    myDateProd = Sheet1.Cells(15, 2)
    myDailyTemplate = Sheet1.Cells(6, 5)

    Application.Workbooks.Open ("J:\IASCMC05HA01\IAC Clients\myBusinessUnit\myFolderYear\myFolderMonth\myFolderWeek\myFolderDaily\& myDailyTemplate & myDateProd.xlsm")

End Sub

回答by psubsee2003

Excel is looking for a file called: "J:\IAS\3CMC05HA01\IAC Clients\myBusinessUnit\myFolderYear\myFolderMonth\myFolderWeek\myFolderDaily\& myDailyTemplate & myDateProd.xlsm"

Excel 正在寻找一个名为： "J:\IAS\3CMC05HA01\IAC Clients\myBusinessUnit\myFolderYear\myFolderMonth\myFolderWeek\myFolderDaily\& myDailyTemplate & myDateProd.xlsm"

since that is what is included in the quotes, but from your code, you appear to have a number of variables that are part of this string, you need to take them out of the quotes and concatenate them together. Try something like this:

由于这是引号中包含的内容，但是从您的代码中，您似乎有许多变量是该字符串的一部分，您需要将它们从引号中取出并将它们连接在一起。尝试这样的事情：

"J:\IASCMC05HA01\IAC Clients\" & myBusinessUnit & "\" & myFolderYear _
& "\" & myFolderMonth & "\" & myFolderWeek & "\" & myFolderDaily & _
"\" & myDailyTemplate & myDateProd & ".xlsm"

I added the continuation _ to make it more readable onthe screen here, but it is not necessary, you can put everything on one line together if you prefer.

我在这里添加了延续 _ 以使其在屏幕上更具可读性，但这不是必需的，如果您愿意，可以将所有内容放在一起。

Unless you need all of the myBusinessUnit, myFolderYear, etc variables elsewhere, I would think about doing it in some sort of array and then doing a Joinfunction to concatenate everything. I, personally, find this easier to maintain going forward and easier to see the hierarchy in the folder structure rather than looking at a very long string and trying to find what part of the path is wrong.

除非你需要的所有的myBusinessUnit，myFolderYear等变量的其他地方，我会考虑在某种阵列的做，然后做一个Join来连接一切功能。我个人认为，这更易于维护，更容易查看文件夹结构中的层次结构，而不是查看很长的字符串并试图找出路径的哪一部分是错误的。

Sub Open_DailyProd()

    Dim pathParts(1 To 10) As String
    Dim path As String

    pathParts(1) = "J:"
    pathParts(2) = "IAS"
    pathParts(3) = "3CMC05HA01"
    pathParts(4) = "IAC Clients"
    pathParts(5) = Sheet1.Cells(32, 2)
    pathParts(6) = Sheet1.Cells(11, 2)
    pathParts(7) = Sheet1.Cells(12, 2)
    pathParts(8) = Sheet1.Cells(13, 2)
    pathParts(9) = Sheet1.Cells(14, 2)
    pathParts(10) = Sheet1.Cells(6, 5) & Sheet1.Cells(15, 2) & ".xlsm"

    path = Join(pathParts, "\")

    Application.Workbooks.Open (path)

End Sub