如何从合并的Excel文件的Final Excel文件中添加列 [英] How to Add Column in Final Excel file from merged excel files

问题描述

我目前在下面有一个excel宏，它基本上将路径中指示的所有文件合并为一个excel文件.

I currently have this excel macro below that basically merging all files indicated in the path into one excel file.

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("C:\Users\MERGE")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

现在，我的问题是，我想在每个最终excel文件的一列中包含每个excel文件的文件名，该文件包含合并的excel文件中的所有数据.

Now, my problem is that, I want to include the file name of each excel file on one column of the final excel file that contains all the data from the merged excel files.

还可以在宏中包括格式化吗?喜欢字体样式/大小/粗体吗?

Also is it possible to include formatting in macros? Like the font style/size/bold?

推荐答案

我对代码进行了一些重构，以便能够为粘贴的每个数据文件的最右列旁边的列添加文件名.用**进行编辑.

I refactored the code a bit to be able to add the file name to column next to the right-most column for each data file pasted in. I commented my edits with **.

(关于您的第二个问题.有很多资源可以了解如何在网络上的VBA中调整单元格格式.简单的搜索会产生很多结果)

Sub simpleXlsMerger()

    Dim bookList As Workbook
    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
    Dim wbMain As Workbook '** just to work directly with this workbook object

    Application.ScreenUpdating = False
    Set wbMain = ThisWorkbook
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    Dim lBeginRow As Long
    lBeginRow = 1 '** start with row 1 at beginning of loop

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("C:\Users\MERGE")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj

        Set bookList = Workbooks.Open(everyObj)

        'change "A2" with cell reference of start point for every files here
        'for example "B3:IV" to merge all files start from columns B and rows 3
        'If you're files using more than IV column, change it to the latest column
        'Also change "A" column on "A65536" to the same column as start point
        bookList.Worksheets(1).Range("A2:IV" & bookList.Worksheets(1).Range("A65536").End(xlUp).Row).Copy wbMain.Worksheets(1).Range("A" & lBeginRow)
        '** in above line i work directly with sheet

        With wbMain.Worksheets(1) 'to work with ThisWorkbook, Sheet 1 (change sheet index number as needed

            Dim lEndRow As Long
            lEndRow = .Range("A65536").End(xlUp).Row '** get last copied row

            Dim lNextColumn As Long
            lNextColumn = .Range("A" & lBeginRow).End(xlToRight).Column + 1 '** get next column after data paste (asssume contigous columns of data)

            '** place file name in newly pasted range
            .Range(.Cells(lBeginRow, lNextColumn), .Cells(lEndRow, lNextColumn)).Value = bookList.Name

            lBeginRow = lEndRow + 1 '** reset next begin row before new paste

        End With

        bookList.Close

    Next

End Sub

这篇关于如何从合并的Excel文件的Final Excel文件中添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！