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

问题描述

OK我发现这一点代码，并认为使用它可能是好的，如果我只需要从一个封闭的表单拉一个值。

  strInfoCell ='& strPath& [& strFile& ] Sheet1'！R3C3
 myvalue = ExecuteExcel4Macro（strInfoCell）
  

这个代码我得到一个strinfocell的值

：strInfoCell：'C：\Users\my.name\Desktop [QOS DGL stuff.xlsx] Sheet1 '！R3C3：String

但是当我运行代码时，文件打开对话框弹出，显示QOS DGL suff的桌面文件。

是什么原因造成的，为什么不只是按预期拉回数据？我知道路径和文件名是正确的，如果我从调试输出中复制它们并粘贴到开始>>运行，那么打开正确的表单

而且知道sheet1（命名为ACL），在单元格（3,3​​）中有一个值

干杯

Aaron

解决方案

这取决于你如何使用它。打开的文件对话框正在显示给您，因为strPath最后没有\;）

尝试此代码。它的作品

试用和测试

  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 Settings\Siddharth Rout\Desktop\
 wbPath =C：\Users\my.name\Desktop\
 
 wbName =QOS DGL stuff.xls
 wsName =ACL
 cellRef =C3
 
 Ret ='& wbPath& [& wbName& ]& _ 
 wsName& ！ &安培; Range（cellRef）.Address（True，True，-4150）
 
 MsgBox ExecuteExcel4Macro（Ret）
 End Sub 
  

OK 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)

No when I run this code I get a value for strinfocell of

: strInfoCell : "'C:\Users\my.name\Desktop[QOS DGL stuff.xlsx]Sheet1'!R3C3" : String

But when I run the code it the file open dialogue pop up, showing desktop files with "QOS DGL suff" showing.

What's causing this, why is it not just pulling back the data as expected? I know the path and file name are right, casue if i copy them from the debug output and paste them in to start>>run then the correct sheet opens

And I know sheet1 (named: ACL), has a value in cell (3,3)

Cheers

Aaron

解决方案

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 ;)

Try this code. It works

Tried and Tested

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 Settings\Siddharth Rout\Desktop\"
    wbPath = "C:\Users\my.name\Desktop\"

    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屋！