Собранные данные в коллекции удалены при внешнем закрытии Excel - excel vba

#excel #vba

Вопрос:

у меня есть код vba в одном Excel, который считывает данные из многих других Excel в определенном месте, но данные исчезают при внешнем закрытии Excel

ниже приведен код, который влияет на коллекцию после строки

 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:camUserExcel"
    fileName = Dir(directory amp; "*.xl??")

    Set projectname = New Collection

    Do While fileName <> ""
        i = i   1
        j = 2
        Workbooks.Open (directory amp; 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

1. В чем проблема, которую вы видите?

2. Поскольку исходные и целевые листы имеют одинаковую структуру, вы могли бы использовать одну коллекцию и myCollection.Add sheet.Cells(i, 1).Resize(1, 10).value и наоборот Sheet1.Cells(j, 1).Resize(1, 10).Value = myCollection(i)

3. @TimWilliams, я могу тебя понять, но это не моя точная проблема, моя проблема в том, что когда я закрываю Excel, где я собирал данные, переменная collection также теряет свои данные.

4. Из вашего кода я не вижу, как это могло произойти, если только книга с кодом не была сохранена и закрыта сама

5. имейте code trail в Excel, чтобы вы могли точно определить мою проблему, поскольку код звучит хорошо для чтения или анализа, но не работает, вот в чем проблема! @TimWilliams

Ответ №1:

Я пропустил это раньше, но вот ваша проблема:

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

Что вы на самом деле добавляете в projectname коллекцию, когда делаете это?

Вы могли бы ожидать, что это будет значение ячейки (поскольку это свойство по умолчанию Range ), но на самом деле вы добавляете ссылку на саму ячейку, и как только вы закроете исходную книгу, вы больше не сможете получить к ней доступ.

Вот исправление:

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

Еще одна причина никогда не писать код, который использует свойства по умолчанию без их явного включения…

И вам не нужно () здесь.