使用公式中预定义单元格中的文件路径 [英] Use file path from a predefined cell in a formula

问题描述

我认为这应该很简单，但是我没有运气能够在网上找到它.显然，我缺少一些非常明显的东西.

I think this should be very simple but I have had no luck being able to do it or look for it on the internet. Clearly, I am missing something very obvious.

我有一个单元格(假设为A1)，其中包含以下文件路径:

I have a cell, lets say A1, that contains the following file path:

'C:\[Required file path]Sheetname'

现在我要使用它在单元格B1中创建一个公式，可以这样说:

now I want to use this to create a formula in cell B1, lets say,:

=Trim('C:\[Required file path]Sheetname'!B26)

由于此链接将动态确定，因此我希望能够执行以下操作:

Since this link will be decided dynamically, I want to be able to do something like this:

=Trim(A1!B26)

其中A1 =我所需的文件路径.但这是行不通的.

Where A1 = my required filepath. But this does not work.

希望我很清楚地解释了这个问题.谢谢大家的期待！

Hope I am clear enough in explaining the problem. Thanks in anticipation!!

推荐答案

如果您的第二个工作簿(路径为'C:\[Required file path]Sheetname')已打开(但在这种情况下，您无需使用完整路径要使用工作簿，仅使用WB名称就足够了)，您可以使用INDIRECT公式(如果您的A1包含''C:\[Required file path]Sheetname'):

If your second workbook( with path 'C:\[Required file path]Sheetname') is open (but in this case you needn't to use full path to workbook, it's enough to use only WB name), you can use INDIRECT formula (if your A1 contains ''C:\[Required file path]Sheetname'):

=TRIM(INDIRECT(A1 & "!" & CELL("address",B26)))

但是，如果您的第二个工作簿已关闭，我发现的方法是在第一个工作簿中添加用户定义的函数并使用它:

But if your second workbook is closed, the way I found is to add user defined function to your first workbook and use it:

=TRIM(getValue(A1 & "!" & CELL("address",B26)))

其中getValue定义为:

Function getValue(formulaString As String)
   Application.Volatile
   Dim app As Excel.Application
   Dim wb As Workbook

   'set default falue to #REF..if we'd get normal value - we'll change getValue to it'
   getValue = CVErr(xlErrRef)

   'if second WB is open - we can easily evaluate formula and exit function'
   getValue = Evaluate(formulaString)
   If Not IsError(getValue) Then
      Exit Function
   End If

   'if we appear here - second WB is closed...'
   On Error GoTo ErrHandler

   Set app = New Excel.Application
   Set wb = app.Workbooks.Add

   With wb.Sheets(1).Range("A1")
      .Formula = "=" & formulaString
      app.Calculate
      getValue = .Value
   End With

ErrHandler:
   If Not wb Is Nothing Then wb.Close False
   If Not app Is Nothing Then app.Quit
   Set app = Nothing
   Set wb = Nothing
End Function

这篇关于使用公式中预定义单元格中的文件路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！