重新分组excel文件 [英] re-grouping excel files

问题描述

大家好，

我有一个文件夹，其中包含大约40个子文件夹

在每个子文件夹中都有各种类型的文件;在每个文件夹中， 存在一个名为（比如说）的单个excel文件A1，A2，...，A40

每个excel文件有12个页面，全部与形状相同但内容（数字）不同;所有excel包含相同数量的行和列

让我们说表单的名称（进一步称为附件）是B1，B2，.. B12。从这12个附件我只需要7个（每个excel文件相同：B1，B2，B3，B4，B5，B6，B10）

现在我的问题是：

我想重新组合所有这些文件以获得7个文件，每个新文件包含40张如下"

拆分每个A1，A2..A40 in 12张

只保留7个附件（总共12个），这些附件是我感兴趣的

并为每个附件制作工作簿， 与 来自每40个excel文件的附件（A1，A2 ...，A40）

所以

第一个名为Book1的文件将仅包含40个工作表来自每40个excel文件（A1，A2，...，A40）的附件B1

第2个名为Book2的文件将仅包含来自每个40个excel文件的表附件B2的40个工作表（A1 ，A2，...，A40）

等等

完成此操作后，我必须为7中的每一个添加其他2张结果文件，包含不同公式的表格，添加了40张数据。

希望我足够清楚

任何人都有并解决了这个问题？

非常感谢任何回复/建议

亲切的问候，

Alcuni

解决方案

您好，

您的要求并不困难，但这将是乏味的。我建议你将它分成几个小部分并逐个完成。

逻辑应循环遍历文件并使用
Workbooks.Open Method（Excel）打开文件，使用  Workbooks.Add Method（Excel）
创建新工作簿并使用
Worksheet.Move方法（Excel）或
Worksheet.Copy Method（Excel） 将工作表拆分为新创建的工作簿。

循环文件夹或文件，您可以使用
目录功能或
FileSystemObject

例如

 Sub LoopF​​older（）
 
 Dim file As Variant 
 file = Dir（"D：\\\\"）
 While（file<> ""）
 Debug.Print文件
 file = Dir 
 Wend 
 
 Dim objFSO，objFolder，objFile As Object 
 Set objFSO = CreateObject（ "Scripting.FileSystemObject"）
设置objFolder = objFSO.GetFolder（"D：\\\\"）
 For each objFile in objFolder.Files 
 Debug.Print objFile。姓名
下一个
设置objFolder = Nothing 
设置objFile = Nothing 
设置objFSO = Nothing 
 End Sub 

上面的代码将获取当前文件夹中的文件。

下面的代码将列出一个文件夹中的所有文件夹名称和文件名

 Sub listAllfiles（）
 Dim fso，oFolder，oSubfolder，strFile，que As Collection 
 Set fso = CreateObject（" Scripting.FileSystemObject"）
 Set que = New Collection 
 que.Add fso.GetFolder（" D：\\\ quot;）
 Do while que.Count> 0 
设置oFolder = que（1）
 que.Remove 1 
 For each oSubfolder in oFolder.SubFolders 
 que.Add oSubfolder 
 Next oSubfolder 
 For每个strFile在oFolder.Files 
 Debug.Print oFolder 
 Debug.Print strFile.Name 
下一个strFile 
循环
结束子

问候，

Celeste

Hello everybody,

I have a folder which contain let's say about 40 sub-folders

In each sub-folder are various type of file; In each one of these folders,  exist a single excel file named (let's say) A1, A2, ..., A40

Each excel file has 12 sheets, all identical as shape but different as contents (numbers); all excels contain same number of rows and columns

let's say the names of sheets (call it further as annexes) are B1, B2, ..B12. From this 12 annexes I need only 7 (same from each excel files: B1, B2, B3, B4, B5, B6, B10)

Now my problems is:

I want to re-group all these files to obtain 7 files, each new file to contain 40 sheets as follow"

split each A1, A2..A40 in 12 sheets

keep only 7 annexes (from the total of 12) which are for interest to me

and to make a workbook, for each annex,  with  corresponded annex from each 40 excel files (A1, A2..., A40)

so

1st file called Book1 will content 40 worksheets only with sheet Annex B1 from each 40 excel files (A1, A2, ..., A40)

2nd file called Book2 will content 40 worksheets only with sheet Annex B2 from each 40 excel files (A1, A2, ..., A40)

and so on

After this is done, I must add other 2 sheets to each of the 7 resulted files, sheets which contain different formula with data from the 40 sheets added.

Hope I was clear enough

Anyone had and solved this problems?

Thanks a lot for any reply/suggestion

Kind regards,

Alcuni

解决方案

Hello,

Your requirement is not difficult, but it would be tedious. I would suggest you split it into several small parts and finish them one by one.

The logic should be loop through files and use Workbooks.Open Method (Excel) to open files, use Workbooks.Add Method (Excel) to create new workbooks and use Worksheet.Move Method (Excel) or Worksheet.Copy Method (Excel) to split sheets into new created workbooks.

To loop through folder or files, you could use Dir Function or FileSystemObject

E.g.

Sub LoopFolder()

Dim file As Variant
file = Dir("D:\test\")
While (file <> "")
Debug.Print file
file = Dir
Wend

Dim objFSO, objFolder, objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("D:\test\")
For Each objFile In objFolder.Files
Debug.Print objFile.Name
Next
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub

The code above would get the file in current folder.

The code below would list all folder name and file name in one folder

Sub listAllfiles()
Dim fso, oFolder, oSubfolder, strFile, que As Collection
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set que = New Collection
    que.Add fso.GetFolder("D:\test\")
    Do While que.Count > 0
        Set oFolder = que(1)
        que.Remove 1
        For Each oSubfolder In oFolder.SubFolders
            que.Add oSubfolder
        Next oSubfolder
        For Each strFile In oFolder.Files
        Debug.Print oFolder
        Debug.Print strFile.Name
        Next strFile
    Loop
End Sub

Regards,

Celeste

这篇关于重新分组excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！