標籤

2020年5月20日 星期三

合併多個EXCEL檔

前提:此巨集僅限EXCEL 2007以上版本才能執行
步驟就是先把要合併檔案全放到一個資料夾

1.接著開一個EXCEL活頁簿,然後按ALT+F11,雙點左側功能選單的ThisWorkBook開啟程式碼視窗

2.然後將下列程式碼複製上去

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("D:\User\Desktop\進銷單據\新增資料夾\SN")
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

然後將D:\User\Desktop\進銷單據\新增資料夾\SN這個資料夾位置改成你放置合併檔案的資料夾位置

3.接著按F5(或是上方工具列上的向右箭頭)執行巨集

4.跑完之後在按ALT+F11

5.就能出現檔案合併後的資料

WIN32_WMISetting命名空間無效,導致.NET System.Management.ManagementException

問題 ReadyGo在LOGIN,判定CPUID時,因無Windows Management Instrumentation的類別,造成 System.Management.ManagementException: Invalid namespace 原因:此錯誤是由 WMI 問...