ExecuteExcel4Macro 从关闭的工作簿中获取价值 [英] ExecuteExcel4Macro to get value from closed workbook

问题描述

我发现了这段代码，并认为如果我只需要从封闭的工作表中提取一个值，它可能会很好用.

I found this bit of code and thought it might be good to use if I just need to pull one value from a closed sheet.

strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"
myvalue = ExecuteExcel4Macro(strInfoCell)

当我运行这段代码时，我得到了一个 strinfocell 的值

When I run this code I get a value for strinfocell of

'C:Usersmy.nameDesktop[QOS DGL stuff.xlsx]Sheet1'!R3C3

但是当我运行代码时会弹出一个对话框，显示带有QOS DGL suff"的桌面文件.

But when I run the code a dialogue pops up, showing desktop files with "QOS DGL suff" showing.

这是什么原因造成的，为什么不只是按预期撤回数据?

What's causing this, why is it not just pulling back the data as expected?

我知道路径和文件名是正确的，因为如果我从调试输出中复制它们并将它们粘贴到 start>>run 中，则会打开正确的工作表.

I know the path and file name are right, because if I copy them from the debug output and paste them in to start>>run then the correct sheet opens.

我知道 Sheet1(命名为:ACL)在 cells(3,3)

I know that Sheet1 (named: ACL), does have a value in cells(3,3)

推荐答案

这取决于你如何使用它.正在向您显示打开文件对话框，因为strPath"最后没有";)

It depends on how you use it. The open file dialog box is being showed to you because the "strPath" doesn't have a "" in the end ;)

试试这个代码.它有效

尝试和测试

Option Explicit

Sub Sample()
    Dim wbPath As String, wbName As String
    Dim wsName As String, cellRef As String
    Dim Ret As String

    'wbPath = "C:Documents and SettingsSiddharth RoutDesktop"
    wbPath = "C:Usersmy.nameDesktop"

    wbName = "QOS DGL stuff.xls"
    wsName = "ACL"
    cellRef = "C3"

    Ret = "'" & wbPath & "[" & wbName & "]" & _
          wsName & "'!" & Range(cellRef).Address(True, True, -4150)

    MsgBox ExecuteExcel4Macro(Ret)
End Sub

这篇关于ExecuteExcel4Macro 从关闭的工作簿中获取价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！