不打开工作簿引用单元格数据

crreg

2个工作表间的“房号”时唯一对应的关系，想把备案汇总表1里的数据（f、h、i、k列）按照房号对应的关系自动填充到“统计表vba”中，要提高填充的速度，谢谢。
2008113014531449391.rar (49.85 KB, 下载次数: 149)

2008-12-9 15:22 上传

点击文件名下载附件

zhaogang1960

请测试：
Sub testGetValue()
    Dim p As String, f As String, s As String, a As String, m As Long, j As Integer
    Dim arr, brr(1 To 65535, 1 To 7), d As Object, i As Long, lr As Long, temp
    lr = [a65536].End(3).Row
    arr = Range("a3:g" & lr).Value
    p = ThisWorkbook.Path & "\"
    f = "备案汇总表1.xls"
    s = "Sheet2"
    Set d = CreateObject("scripting.dictionary")
    For i = 2 To 65536
        a = "B" & i: temp = getvalue(p, f, s, a)
        If temp = 0 Then
            Exit For
        End If
        d(temp) = i
    Next i
    For i = 1 To lr - 2
        temp = arr(i, 1)
        If d.exists(temp) Then
            a = "F" & d(temp): arr(i, 3) = getvalue(p, f, s, a)
            a = "H" & d(temp): arr(i, 5) = getvalue(p, f, s, a)
            a = "K" & d(temp): arr(i, 6) = getvalue(p, f, s, a)
            a = "I" & d(temp): arr(i, 7) = getvalue(p, f, s, a)
        End If
    Next i
    Range("a3:g" & lr).Value = arr
    MsgBox "处理完毕"
End Sub
Private Function getvalue(pa As String, File As String, SHEET As String, REF As String)
    Dim arg As String
    arg = "'" & pa & "[" & File & "]" & SHEET & "'!" & Range(REF).Range("A1").Address(, , xlR1C1)
    getvalue = ExecuteExcel4Macro(arg)
End Function

[ 本帖最后由 zhaogang1960 于 2008-12-9 18:58 编辑 ]

crreg

谢谢您的回答，速度比我的宏运行的要快，请问
Private Function getvalue(pa As String, File As String, SHEET As String, REF As String)
    Dim arg As String
    arg = "'" & pa & "[" & File & "]" & SHEET & "'!" & Range(REF).Range("A1").Address(, , xlR1C1)
    getvalue = ExecuteExcel4Macro(arg)
End Function
这段代码是作什么用的
还有  a = "B" & i: temp = getvalue(p, f, s, a)，不太懂什么意思

[ 本帖最后由 crreg 于 2008-12-10 13:23 编辑 ]

zhaogang1960

那段Function是自定义函数，调用它可以在关闭的工作簿中取值，它使用了excel5以前版本的老式宏，我也不懂，只管用吧
a = "B" & i: temp = getvalue(p, f, s, a)是两个语句，a = "B" & i表示b列第i行单元格
temp = getvalue(p, f, s, a)是调用Function自定义函数，取出关闭工作簿(备案汇总表1)Sheet2工作表中b列第i行单元格的值

exp_dyq

打搅了,
请问下面getvalue功能能不能get一整行的数据然后将数据赋给arr ,如何实现.
谢谢.

a = "F" & d(temp): arr(i, 3) = getvalue(p, f, s, a)

Private Function getvalue(pa As String, File As String, SHEET As String, REF As String)
    Dim arg As String
    arg = "'" & pa & "[" & File & "]" & SHEET & "'!" & Range(REF).Range("A1").Address(, , xlR1C1)
    getvalue = ExecuteExcel4Macro(arg)
End Function

zhaogang1960

请参考：
Sub aaa() '假设从本目录"1.xls"工作簿"Sheet1"的"A:K"列取值
    Dim i As Integer, arr
    Sheet2.Cells.Clear 'Sheet2为临时工作表
    i = 3 '取值行数
    bbb ThisWorkbook.Path, "1.xls", "Sheet1", "A1:K" & i, i
    arr = Sheet2.Range("A2:K" & i + 1)
    Range("A1").Resize(UBound(arr), UBound(arr, 2)) = arr '测试效果
End Sub
Sub bbb(Path As String, WorkbookName As String, SheetName As String, RangeName As String, i As Integer)
    Dim lr As Long, rng As String
    With Sheet2
        lr = .Range("a65536").End(xlUp).Row + 1
        rng = "A" & lr & ":K" & lr + i - 1
        With .Range(rng)
          .FormulaArray = "='" & Path & "\[" & WorkbookName & "]" & SheetName & "'!" & RangeName
          .Value = .Value
          .Replace 0, "", xlWhole
        End With
    End With
End Sub

南航JTP

Dim p As String, f As String, s As String, a As String, m As Long, j As Integer
请问一下，什么时候用“string”什么时候用“long”什么时候用“integer”？

zhaogang1960

请参阅VBA帮助
String 数据类型
字符串有两种：变长与定长的字符串。
Long 数据类型
Long（长整型）变量存储为 32 位（4 个字节）有符号的数值形式，其范围从 -2,147,483,648 到 2,147,483,647。Long 的类型声明字符为和号 (&)。
Integer 数据类型
Integer 变量存储为 16位（2 个字节）的数值形式，其范围为 -32,768 到 32,767 之间。Integer 的类型声明字符是百分比符号 (%)。
在本程序中，路径、工作簿、工作表名称使用String 数据类型
行号使用Long 数据类型
列号使用Integer 数据类型