VBA宏在目录中查找Sheet count

3 回答

• 1

  请看下面的内容 - 请注意，您应该从空白工作表中运行此操作

  Set CurrentWB = ActiveWorkbook
  
  Dim folderPath As String
  Dim Filename As String
  Dim wb As Workbook
  Dim J As Long
  Dim N As Long
  Dim lc As Long
  Dim lr As Long
  
  'UPDATE FOLDER PATH OF WHERE XLS FILES ARE LOCATED
  folderPath = "C:\Users\username\Desktop\test\" 'change to suit
  
  J = 2
  
  '   Column Headers
      CurrentWB.Sheets(1).Range("A1").Value = "Filename"
      CurrentWB.Sheets(1).Range("B1").Value = "# of Sheets"
  
  If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
  
  'YOU CAN CHANGE TO BE ANY FILE TYPE BUT CURRENTLY SET TO .XLSX
  Filename = Dir(folderPath & "*.xlsx")
  Do While Filename <> ""
    Application.ScreenUpdating = False
      Set TempWB = Workbooks.Open(folderPath & Filename)
  
  
  
  '       Counts Per Worksheet
      N = ActiveWorkbook.Worksheets.Count
      CurrentWB.Sheets(1).Range("A" & J).Formula = Filename
      CurrentWB.Sheets(1).Range("B" & J).Formula = N
  
  
  '       Close Temporary Workbook
      TempWB.Close False
  
      J = J + 1
      Filename = Dir
  Loop
  

  回复于 2024-05-26T03:32:40+08:00
• 0

  在for循环中，打开文件（假设它们都是excel）并获取工作表的数量 .

  就像是：

  For Each objFile In objFolder.Files
      writeCell = ws.Cells(ws.UsedRange.Rows.Count + 1, 1)
      writeCell.Value = objFile.Name
      'ADD A WORKSHEET AND PASTE "=SHEETS()" in A1 the copy value of a1 in to list
      'close files with out saving
  
      Set wb = Workbooks.Open(objFile.Name)
      writeCell.Offset(,1).value = wb.Worksheets.Count()
      wb.Close(false)
  
  Next
  

  回复于 2024-05-26T03:32:40+08:00
• 0

  Sub ListallFiles()
      Dim sFileName As String
      Dim sFolderPath As String: sFolderPath = "C:\Temp\"     ' Change folder path. Ensure that folder path ends with "\"
      Dim oWB As Workbook
      Dim oWS As Worksheet
  
      ' Get the first excel file name from specified folder
      sFileName = Dir(sFolderPath & "*.xls*")
  
      ' Add a worksheet
      Set oWS = ThisWorkbook.Worksheets.Add
  
      With oWS
  
          ' Set folder name in the new sheet
          .Range("A1").Value = "The file found in " & sFolderPath & " are:"
  
          ' Loop through all excel files in the specified folder
          Do While Len(Trim(sFileName)) > 0
  
              ' Open workbook
              Set oWB = Workbooks.Open(sFolderPath & sFileName)
  
              ' Set workbook details in the file
              .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1).Value = sFileName
              .Range("B" & .Cells(.Rows.Count, "B").End(xlUp).Row + 1).Value = oWB.Worksheets.Count
  
              ' Close workbook
              oWB.Close False
  
              ' Clear workbook object
              Set oWB = Nothing
  
              ' Get next excel file
              sFileName = Dir()
          Loop
  
      End With
  
  End Sub
  

  UDF上面应该打开指定文件夹中的所有文件，并在新工作表上为每个工作簿提供工作表的数量

  回复于 2024-05-26T03:32:40+08:00