VBA - Faixa de cópia de 300 pastas de trabalho fechadas em uma pasta de trabalho principal consolidada

Question

Por favor, ajude a escrever a fórmula para extrair dados de 300 pastas de trabalho fechadas (e não quero abrir nenhuma dessas 300 pastas de trabalho). Descrição do projeto: São 300 planilhas de horas em Excel. É necessário copiar o intervalo (A1:AC51) de cada planilha de horas na pasta de cálculo principal, calcular a folha de pagamento e, em seguida, copiar o resultado da folha de pagamento para outra planilha na mesma pasta de trabalho principal. O truque aqui é fazer isso sem abrir as 300 planilhas de horas do Excel. Escrevi o código abaixo, mas não sei como escrever a fórmula para extrair os dados de uma pasta de trabalho fechada, por favor me ajude? De novo,

• Existem 300 planilhas de horas do Excel (pastas de trabalho fechadas), Planilhas ("EmpInput")
• Há uma pasta de cálculo principal (aberta), Planilhas("Principal")
• Objetivo: Copiar exatamente o mesmo intervalo de cada planilha de horas, calcular a folha de pagamento, copiar o resultado para outra planilha, Planilhas("Resultados")

Muito obrigado antecipadamente!

Public Sub GetTimesheetData()

     Dim fsoFileObject As New Scripting.FileSystemObject
     Dim employeeTimesheet As File
     Dim folderPath As String
     Dim nextEmpty As Long

     folderPath = "C:\GatherTimesheets\"    '<<<<< There are 300 excel timesheets in this folder.

     For Each employeeTimesheet In fso.folderPath.Files  '<<<< Is this correct?

        If empoyeeTimesheet.Name Like "*.xls" Then
               Sheets("Main").Select
               With Range("A1:AC51")
                  .Formula = "='C:\GatherTimesheets\" & employeeTimesheet   '<<<< How to write this formula??
                  .Value = .Value
               End With

               nextEmpty = Sheets("Results").Range("D65444").End(xlUp).Row + 1

               Sheets("Main").Range("CS1:DF1").Copy
               With Sheets("StagingRaw").Range("D" & nextEmpty)
                  .PasteSpecial xlValues
                  Application.CutCopyMode = False
               End With
         End If

     Next employeeTimesheet

End Sub

Answer 1

O formato é ='C:\Path\[SourceFileName.xlsx]SourceSheetName'!A1:Z9.

Tentar Range("A1:AC51").FormulaArray = "='C:\GatherTimesheets\[" & employeeTimesheet & "]EmpInput'!A1:AC51".

Para mais informações vejaPropriedade Range.FormulaArray (Excel)eCrie uma referência externa (link) para um intervalo de células em outra pasta de trabalho. Além disso, useOpção Explícitapara evitar erros de digitação como empoyeeTimesheet.