如何从Excel工作簿中选择值并按活动工作簿上的函数返回它们

My goal is to implement some of functions where I give them parameters of power, frequency and speed of an electric motor, and look in another workbook (in which I have motor data) and return the size, shaft diameter and other motor details.

我的目标是实现一些功能,我给它们电动机的功率,频率和速度参数,并查看另一个工作簿(我有电机数据)并返回尺寸,轴直径和其他电机细节。

As I have not mastered much VBA I tried to implement a function that simply goes to a cell in another workbook and returns the value:

由于我没有掌握很多VBA,我试图实现一个函数,只是转到另一个工作簿中的单元格并返回值:

Function Test() As String
Dim name As String 

  With Workbooks.Open("D:\ExcelTest\WbSource.xlsm").Sheets("Sheet1")  
    name = .Cells(2, 3) 
  End With

  Test= name

  ActiveWorkbook.Save
  ActiveWorkbook.Close

End Function

The problem is that it gives me a #VALUE! error, but each variable used is defined as a string and the cells has general format (if I change cells format to text it gives me the same message).

问题是它给了我一个#VALUE!错误,但使用的每个变量都被定义为字符串,并且单元格具有通用格式(如果我将单元格格式更改为文本,则会给出相同的消息)。

3 个解决方案

#1

Try as I might, I could not get workbooks.open to work in a function, even if the function calls a sub. You could open the catalogue file in the workbook open event, and close it again in the before close event.

尽量尝试,我无法让workbooks.open在函数中工作,即使该函数调用了一个子函数。您可以在工作簿打开事件中打开目录文件,然后在关闭前事件中再次关闭它。

In the VProject Explorer, right click on "ThisWorkBook," and "View code".
In the pick list at the top, select Workbook, and the sub Workbook_open() procedure should be created. If not, select "Open" in the right pick list. Put in the following:

在VProject Explorer中,右键单击“ThisWorkBook”和“查看代码”。在顶部的选择列表中,选择“工作簿”,然后应创建子Workbook_open()过程。如果没有,请在右侧选择列表中选择“打开”。放入以下内容:

Application.Workbooks.Open ("D:\ExcelTest\WbSource.xlsm")
ThisWorkbook.Activate 'restores the "focus" to your worksheet

Then click the right pick list and select "beforeClose" and put in

然后单击右侧选择列表并选择“beforeClose”并输入

On Error Resume Next 'this keeps it from crashing if the catalogue is closed first
Workbooks("WbSource.xlsm").Close

As long as the worksheet opens the wbsource file first, the function will work.

只要工作表首先打开wbsource文件,该函数就可以工作。

#2

Here is an approach with scheduling UDF execution in queue, and processing outside UDF that allows to get rid of UDF limitations. So the value from the closed workbook got via ExecuteExcel4Macro() by a link.

这是一种在队列中调度UDF执行的方法,以及允许摆脱UDF限制的UDF外部处理。因此,封闭工作簿中的值通过链接通过ExecuteExcel4Macro()获得。

Put the following code into one of the VBAProject Modules:

将以下代码放入其中一个VBAProject模块中:

Public Queue, QueueingAllowed, UDFRetValue

Function UDF(ParamArray Args())
    If IsEmpty(Queue) Then
        Set Queue = CreateObject("Scripting.Dictionary")
        UDFRetValue = ""
        QueueingAllowed = True
    End If
    If QueueingAllowed Then Queue.Add Application.Caller, (Args)
    UDF = UDFRetValue
End Function

Function Process(Args)
    If UBound(Args) <> 4 Then
        Process = "Wrong args number"
    Else
        ' Args(0) - path to the workbook
        ' Args(1) - filename
        ' Args(2) - sheetname
        ' Args(3) - row
        ' Args(4) - column
        On Error Resume Next
        Process = ExecuteExcel4Macro("'" & Args(0) & "[" & Args(1) & "]" & Args(2) & "'!R" & Args(3) & "C" & Args(4))
    End If
End Function

Put the following code into ThisWorkbook section of VBAProject Excel Objects:

将以下代码放入VBAProject Excel Objects的ThisWorkbook部分:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim Item, TempFormula
    If Not IsEmpty(Queue) Then
        Application.EnableEvents = False
        QueueingAllowed = False
        For Each Item In Queue
            TempFormula = Item.FormulaR1C1
            UDFRetValue = Process(Queue(Item))
            Item.FormulaR1C1 = TempFormula
            Queue.Remove Item
        Next
        Application.EnableEvents = True
        UDFRetValue = ""
        QueueingAllowed = True
    End If
End Sub

After that you can get the values from closed workbook via worksheet formula using UDF:

之后,您可以使用UDF通过工作表公式从已关闭的工作簿中获取值:

=UDF("D:\ExcelTest\";"WbSource.xlsm";"Sheet1";2;3)

Anyway you can add Workbooks.Open() or any other stuff into Function Process(Args) to make it to work the way you want. The code above is just an example. I've answered the similar questions here and here, so that descriptions might be helpful.

无论如何,你可以将Workbooks.Open()或任何其他东西添加到Function Process(Args)中,使其按照你想要的方式工作。上面的代码只是一个例子。我已经在这里和这里回答了类似的问题,因此描述可能会有所帮助。

#3

I suggest:

open WbSource.xlsm either manually or via VBA outside the UDF.

手动或通过UDF外部的VBA打开WbSource.xlsm。

pass the parameters to the UDF

将参数传递给UDF

have the UDF search down the columns of the newly opened workbook to find the correct record

让UDF搜索新打开的工作簿的列以查找正确的记录

have the UDF pass the row number back to the worksheet

让UDF将行号传回工作表

in the worksheet, use Match()/Index() formulas to retrieve other data.

在工作表中,使用Match()/ Index()公式来检索其他数据。

#1