编译excels保存在excel中不同选项卡下的文件夹下，并使用excel名称重命名该选项卡 [英] compile excels saved under a folder in a excel under different tabs and rename the tab with the excel name

问题描述

HI ..

我希望VBA整理保存在文件夹下的多个excel

I want the VBA to collate multiple excels saved under a folder

应该在excel电子表格下不同的标签 

should be on a excel spreadsheet under different tabs 

和标签名称应该是excel的名称。

and name of the tab should be the name of the excel.

谢谢，

Thanks,

Tushar 

Tushar 

推荐答案

以下是您可以使用的宏：

Here is a macro you can use:

Sub CombineWorkbooks（）

    Dim strFolder As String

    Dim strFile As String

    Dim strSheet As String

    Dim wbkS As Workbook

    Dim wbkT As Workbook

    On Error GoTo ErrHandler

   使用Application.FileDialog（4）

       如果。显示则为
            strFolder = .SelectedItems（1）& " \"

       否则为
           发出哔哔声b
           退出Sub¥
       结束如果是
   结束与$
    Application.ScreenUpdating = False

    Application.Cursor = xlWait

    Application.DisplayAlerts = False

   设置wbkT = Workbooks.Add（xlWBATWorksheet）

    strFile = Dir（strFolder&" * .xls *"）

   做strFile<> ""

       设置wbkS = Workbooks.Open（strFolder& strFile）

        wbkS.Worksheets（1）.Copy After：= wbkT.Worksheets（wbkT.Worksheets.Count）

        strSheet = wbkS.Name

        strSheet = Left（strSheet，InStrRev（strSheet，"。"） - 1）

        wbkT.Worksheets（wbkT.Worksheets.Count）.Name = strSheet

        wbkS.Close SaveChanges：= False

        strFile = Dir

   循环

    wbkT.Worksheets（1）。删除

ExitHandler：

    Application.DisplayAlerts = True

    Application.Cursor = xlDefault

    Application.ScreenUpdating = True

   退出Sub¥
ErrHandler：

    MsgBox Err.Description，vbExclamation

   恢复ExitHandler

End Sub

Sub CombineWorkbooks()
    Dim strFolder As String
    Dim strFile As String
    Dim strSheet As String
    Dim wbkS As Workbook
    Dim wbkT As Workbook
    On Error GoTo ErrHandler
    With Application.FileDialog(4)
        If .Show Then
            strFolder = .SelectedItems(1) & "\"
        Else
            Beep
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Application.DisplayAlerts = False
    Set wbkT = Workbooks.Add(xlWBATWorksheet)
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbkS = Workbooks.Open(strFolder & strFile)
        wbkS.Worksheets(1).Copy After:=wbkT.Worksheets(wbkT.Worksheets.Count)
        strSheet = wbkS.Name
        strSheet = Left(strSheet, InStrRev(strSheet, ".") - 1)
        wbkT.Worksheets(wbkT.Worksheets.Count).Name = strSheet
        wbkS.Close SaveChanges:=False
        strFile = Dir
    Loop
    wbkT.Worksheets(1).Delete
ExitHandler:
    Application.DisplayAlerts = True
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

这篇关于编译excels保存在excel中不同选项卡下的文件夹下，并使用excel名称重命名该选项卡的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！