时间戳

首页 > 解决方案 > 收集的数据在外部 excel 关闭时被删除 - excel vba

excel - 收集的数据在外部 excel 关闭时被删除 - excel vba

问题描述

我在一个 excel 中有一个 vba 代码，它从特定位置的许多其他 excel 中读取数据，但是数据在外部 excel 上消失了 close

下面给出了影响行后集合的代码

Workbooks(fileName).Save
Workbooks(fileName).Close

代码：

Sub filefindermacro()
    Dim directory As String
    Dim fileName As String
    Dim sheet As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim datecollection As New Collection
    Dim majorcategory As New Collection
    Dim projectname As New Collection
    Dim partname As New Collection
    Dim username As New Collection
    Dim designerchecker As New Collection
    Dim fpactualhours As New Collection
    Dim ractualhours As New Collection
    Dim currentstatus As New Collection
    Dim softwareused As New Collection
    Application.ScreenUpdating = False
    directory = "D:\cam\UserExcel\"
    fileName = Dir(directory & "*.xl??")

    Set projectname = New Collection

    Do While fileName <> ""
        i = i + 1
        j = 2
        Workbooks.Open (directory & fileName)
        For Each sheet In Workbooks(fileName).Worksheets
            If sheet.Name = "HAI" Then
                Dim counter
                counter = sheet.UsedRange.Rows.Count
                For i = 3 To counter
                    datecollection.Add (sheet.Cells(i, 1))
                    majorcategory.Add (sheet.Cells(i, 2))
                    projectname.Add (sheet.Cells(i, 3))
                    partname.Add (sheet.Cells(i, 4))
                    username.Add (sheet.Cells(i, 5))
                    designerchecker.Add (sheet.Cells(i, 6))
                    fpactualhours.Add (sheet.Cells(i, 7))
                    ractualhours.Add (sheet.Cells(i, 8))
                    currentstatus.Add (sheet.Cells(i, 9))
                    softwareused.Add (sheet.Cells(i, 10))
                    'MsgBox projectname(i - 2)
                Next
            End If
        Next sheet
        **Workbooks(fileName).Save
        Workbooks(fileName).Close**
        fileName = Dir()
    Loop
    Dim projectfile, projname
    projectfile = Replace(ThisWorkbook.Name, ".xlsm", "")
    j = 1

    For i = 1 To projectname.Count
        'MsgBox projectname.Count
        **If projectname.Item(i) = projectfile Then**
            Sheet1.Cells(j, 1) = datecollection(i)
            Sheet1.Cells(j, 2) = majorcategory(i)
            Sheet1.Cells(j, 3) = projectname(i)
            Sheet1.Cells(j, 4) = partname(i)
            Sheet1.Cells(j, 5) = username(i)
            Sheet1.Cells(j, 6) = designerchecker(i)
            Sheet1.Cells(j, 7) = fpactualhours(i)
            Sheet1.Cells(j, 8) = ractualhours(i)
            Sheet1.Cells(j, 9) = currentstatus(i)
            Sheet1.Cells(j, 10) = softwareused(i)
            j = j + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

标签： excelvba

解决方案

我之前错过了，但这是你的问题：

projectname.Add (sheet.Cells(i, 3))

执行此操作时，您实际上向projectname集合添加了什么？

您可能希望它是单元格的值（因为这是 的默认属性Range），但您实际上是在添加对单元格本身的引用，并且一旦您关闭了源工作簿，就无法再访问它。

这是修复：

 projectname.Add sheet.Cells(i, 3).Value

永远不要编写使用默认属性而不明确包含它们的代码的另一个原因......

而且你不需要()这里。

推荐阅读