网络驱动器上的 Excel 文件

Question

我工作的公司在网络驱动器上有一本非常大的主数据工作簿。还有数十个较小的“摘要”工作簿链接到同一驱动器上托管的主文件。这是我公司使用Excel作为数据库系统的尝试。这是一场难以管理的噩梦。

无论如何，问题是主文件每天会更改几次，并且每几周就会备份/重命名一次。这意味着我需要手动浏览数十个文件并更新到新文件的链接。例如，每隔几周我就必须将数十个文件和数百个选项卡中的“whatever Q3-2010.xls”更改为“whatever Q4-2011.xls”。我希望能够消除这个问题。

我有一些想法，但遇到了问题。你们中有人对如何简化这个有什么建议吗？我之前的想法/问题是：

1. 复制主文件并将其重命名为“Whatever Current.xls”。每次文件更改时都执行此操作。这种方法的问题在于，有几个人每天都会多次更改该文件，而有人肯定会忘记。

2. 在网络驱动器上创建主文件的快捷方式（符号链接）（即将网络驱动器上的快捷方式放在与主文件相同的目录中）。这有两个主要问题。首先，由于某种原因，我似乎无法在网络驱动器上创建快捷方式。我不知道这是一个安全问题还是无法完成。其次，当您在 Excel 中添加快捷方式的链接时，它会解析快捷方式指向的文件，而不是快捷方式本身。这并不比我们现在的更好。

3. 将主文件重命名为“Whatever Current.xls”这似乎是最简单的，应该可以完美工作。不幸的是，公司政策要求文件名中包含日期或季度标识符。

现在您看到了我的问题，是否有人对如何最好地实现这一目标有任何想法？ 谢谢！

编辑：拼写

编辑2：我再一次遇到了障碍。我已经使用链接创建了抽象文件，但实际上我无法将它们用作其他文件的引用。我环顾四周，看起来我必须为此使用 INDIRECT 和 CONCATENATE 函数。不幸的是，间接仅适用于打开的工作簿，并且我假设抽象文件将被关闭。我可以使用几个 Excel“扩展”来允许 INDIRECT 在封闭的工作簿上工作，但由于这必须适用于每个人，因此这不是一个选择。我现在唯一的选择是制作抽象文件，然后在引用它的每个文件中建立指向该文件的链接，然后使用 INDIRECT 作为对链接引用的引用（这变得很复杂）。有人还有其他想法吗？

EDIT3：实际上，我上面的想法行不通。评估的链接必须仍然打开（在我的例子中它是主数据表），我们不能假设它是打开的。我开始认为也许我应该使用一些 VBA 来运行查询或其他东西，但我不知道该怎么做？有什么帮助吗？

Answer 1

我会在必要时使用 VBA 更新链接，至少直到您可以说服他们重构流程为止。在每个子工作簿中，放置一个名为 Source 的 CustomDocumentProperty 并将其值设置为源文件的完整路径。我们将使用此属性的存在来确定我们是否正在处理子工作簿。在一个新的加载项中，插入一个包含此代码的类模块

Option Explicit

Private WithEvents mobjApp As Application

Public Property Get App() As Application: Set App = mobjApp: End Property
Public Property Set App(ByVal objApp As Application): Set mobjApp = objApp: End Property

Private Sub mobjApp_WorkbookOpen(ByVal wb As Workbook)

    Dim sOldSource As String, sNewSource As String
    Dim sh As Worksheet

    If IsChild(wb) Then 'only work with child workbooks, event fires for every workbook
        sOldSource = GetSource(wb) 'read the customdocprop
        If Len(Dir(sOldSource)) = 0 Then 'if the file doesn't exist, we need to update the links

            'get the new source file
            sNewSource = Application.GetOpenFilename("*.xlsx, *.xlsx", , "Select New Source", , False)

            'change the link source
            Application.DisplayAlerts = False
                wb.ChangeLink sOldSource, sNewSource, xlLinkTypeExcelLinks
            Application.DisplayAlerts = True
        End If
    End If

End Sub

在一个标准模块（我称之为 MUtilities）中放置此代码

Option Explicit

Private Const msDOCPROP As String = "Source"

Function IsChild(wb As Workbook) As Boolean

    Dim sSource As String

    On Error Resume Next
        sSource = wb.CustomDocumentProperties(msDOCPROP)
    On Error GoTo 0

    IsChild = Len(sSource) > 0

End Function

Function GetSource(wb As Workbook) As String

    On Error Resume Next
        GetSource = wb.CustomDocumentProperties(msDOCPROP)

End Function

在另一个标准模块（MOpenClose）中放置此代码

Option Explicit

Public gclsAppEvents As CAppEvents

Sub Auto_Open()

    Set gclsAppEvents = New CAppEvents

    Set gclsAppEvents.App = Application

End Sub

当加载加载项时，它将创建一个新的CAppEvents 将侦听工作簿打开事件。每次打开工作簿时，它都会检查它是否相关。如果是，它将查看源文件是否仍然存在。如果没有，它将提示选择新的源文件并更新链接。

您可能需要调整逻辑，并且您肯定希望使错误处理更加健壮。

一旦开始工作，就开始编写代码，将此信息移植到 Access（或其他）数据库中。

Answer 2

您可以在主工作表中嵌入一个事件，该事件也会自动保存备份（您提到您担心人们会忘记），这样它就不可能不被保存并且不会以您需要的相同名称进行备份它可以减轻人们编辑文件的工作。

使用 workbook_beforesave 事件进行探索 - 每次用户尝试保存工作簿时都会启动该事件。禁用提示并以您需要的名称秘密保存备份！不要忘记在代码期间关闭事件，否则您的备份保存也会触发该事件。