通过VBA从另一个Workbook复制数据 [英] Copy data from another Workbook through VBA

问题描述

这里的人是我想做的，我有一点麻烦。
我有1个工作簿，我想从不同的文件收集数据，这样做。

  Do While THAT_DIFFERENT_FILE_SOMEWHERE_ON_MY_HDD.Cells（Rand，1）.Value< 而兰德65536 
然后'我会搜索我的主要工作表中的最后一行是否在这个文件... 
结束循环
  

如果是，我将退出 While 循环，如果不是我将复制所有内容。实际上这不会像我想要的那样工作，但是我不会找到正确的算法。

我的问题是我不知道如何访问不同的工作簿。

解决方案

您可能希望 GetInfoFromClosedFile（）功能

---

编辑：由于上述链接似乎不再工作，我添加了替代链接1 和替代链接2 +代码：

 私有函数GetInfoFromClosedFile（ByVal wbPath As String，_ 
 wbName As String，wsName As String，cellRef As String）As Variant 
 Dim arg As String 
 GetInfoFromClosedFile =
如果Right（wbPath，1）<> 然后wbPath = wbPath& 
如果Dir（wbPath&& wbName）=Then Exit Function 
 arg ='& wbPath& [& wbName& ]& _ 
 wsName& ！ &安培; Range（cellRef）.Address（True，True，xlR1C1）
 On Error Resume Next 
 GetInfoFromClosedFile = ExecuteExcel4Macro（arg）
结束函数
  pre> 
Guys here's what I want to do and I have a little trouble doing it.
I have 1 Workbook where I want to collect data from different files doing something like this. 
Do While THAT_DIFFERENT_FILE_SOMEWHERE_ON_MY_HDD.Cells(Rand, 1).Value <> "" And Rand < 65536
        then 'I will search if the last row in my main worksheet is in this file... 
End Loop           
If it is I'll quit the While Loop, if it's not I'll copy everything.  Actually this won't work as I want but I won't have trouble finding the right algorithm. 

My problem is that I don't know how to access different workbooks.
解决方案
You might like the function GetInfoFromClosedFile()   



Edit: Since the above link does not seem to work anymore, I am adding alternate link 1  and alternate link 2 + code:  
Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
    If Dir(wbPath & "" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function


                        
这篇关于通过VBA从另一个Workbook复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！