Excel檔案間資料自動處理
目的:
由A EXCEL檔內取得的資料，經加工處理後，移至另外一個B EXCEL檔內。
來達成兩個檔案間資料的交流存取，並作成巨集執行，以避免人為操作錯誤，提升作業效率。
1 流程說明
1.1 來源A EXCEL檔內之資料複製至 B EXCEL檔
1.2 於B EXCEL檔取得資料，依據需求作加工處理後，產出另一個sheet
1.3 上述兩個作業，設計成兩個巨集與按鈕，只要按下按鈕便自動完成
2 作業說明
2.1 先打開關聯的兩個 EXCEL 檔
2.2 依需求設計如下兩個巨集:
(1)複製來源EXCEL，出力更新資料至目地EXCEL
(2)加工處理產出所需之EXCEL工作表內容
2.3 EXCEL Macro 巨集作成步驟:
(1) 先於EXCEL檔案/ 選項 --> 啟用巨集
(2) 於檔案/ 選項 --> 開啟 " 開發人員"
(3) 開發人員-> 按“錄製巨集” 或 “ 巨集”以便作巨集編輯
依下圖進入巨集編輯
2.4配置兩個按鈕
按鈕作成:
EXCEL開發人員-> 插入 -> 表單控制項 ->按鈕圖示新作成->
再於按鈕右鍵 -> 指定巨集
3 實例說明
以某單位志工別服務時數之EXCEL檔內資料為例，經加工編輯處理後，產出所需的工作表(sheet)內容格式，再存至另一個EXCEL 檔
3.1 兩個EXCEL 檔準備
(3.1.1)志工服務時數
(3.1.2) 志工時數條VHPrint.xls
3.2 兩個巨集設計
(3.2.1) 兩個巨集存放於志工時數條VHPrint.xls內，
(3.2.2) 巨集名稱
*M1)巨集UpdateSheet，執行步驟:
STEP1刪除志工時數條VHPrint 檔內 requests sheet內容
STEP2 志工時數條檔 requests sheet 資料範圍選取後，copy取得
STEP3上述取得之資料，paste to 志工時數條VHPrint.xls檔內
執行完後顯示志工人數
*M2)巨集ReduceVHP， 執行步驟:
STEP 1 刪除志工時數條VHPrint檔內 VHPrint sheet del
STEP 2 新VHPrint sheet內資料，由requests sheet取得再加工處理
處理內容包括:
*P1)出力標題列，追加兩個欄位，"社工人員" 與"志工名字"
*P2) 來源檔內，每一志工有三列資料，經加工簡化為一列
來源檔內，每一位志工資料有三列
經加工處理簡化為一列，如下
(3.2.3) 巨集編輯，設計撰寫，測試執行，於如下環境:
3.3巨集按鈕作成
(3.3.1) 本例兩個按鈕名稱 為
”更新時數條內容” ，指定巨集為 巨集UpdateSheet
”簡化時數條” ，指定巨集為 巨集ReduceVHP
按鈕作成如下:
(3.3.2) 簡化時數條按鈕，按下後，結束會出力加工編輯後之sheet，並顯示志工人數，參考如下
4. 改善效果:
4.1 程式自動執行刪除舊資料，取得最新資料，完成加工處理
4.2 按下巨集按鈕，便可輕易完成，提高作業效率
5 將來運用
5.1其他業務可仿效，設計vba讓手動作業，自動處理
5.2 加工編輯後之sheet，可與WORD合併表格，作列印處理
6 本例巨集設計如下 :
6.1 巨集UpdateSheet
Sub 巨集UpdateSheet()
Dim crow As Integer
Dim i As Integer
Application.WindowState = xlNormal
'Windows("志工時數條VHPrint.xls").Activate
'Selection.SpecialCells(xlCellTypeVisible).Select
'Sheets("Requests").Cells.ClearContents
' STEP 1 刪除志工時數條VHP 檔內 requests sheet del
Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = crow To 1 Step -1
Rows(i).EntireRow.Delete
Next i
'MsgBox "VHP del end ??"
' STEP 2 原始志工時數條檔 requests sheet 資料範圍選取後，copy取得
Workbooks("志工時數條.xls").Sheets("Requests").Activate
'---must A1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'MsgBox "copy ? "
' STEP 3 將上述取得之requests sheet資料，paste to 志工時數條VHPrint.xls檔內
Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Sheets("Requests").Select
'must A1，paste from to A1----
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
MsgBox "Requests sheet was updated" & "; 志工數=" & (crow - 1) / 3
End Sub
6.2 巨集ReduceVHP
Sub 巨集ReduceVHP()
Dim crow As Integer
Dim outR As Integer
Dim i, iR, pos As Integer
Dim PIC, VS As String
'Windows("志工時數條VHPrint.xls").Activate
' STEP 1 刪除志工時數條VHPrint檔內 VHPrint sheet del
Workbooks("志工時數條VHPrint.xls").Sheets("VHPrint").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = crow To 1 Step -1
Rows(i).EntireRow.Delete
Next i
'MsgBox "old VHPrint sheet del end ??"
' STEP 2 新VHP sheet內資料，由requests sheet取得
Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
' VHP sheet內row 1 標題列取得
Range("A1:E1").Copy Sheets("VHPrint").Range("A1:E1")
Range("A1:B1").Copy Sheets("VHPrint").Range("F1:G1")
Sheets("VHPrint").Cells(1, 6).Value = "社工人員"
Sheets("VHPrint").Cells(1, 7).Value = "志工名字"
VS = "老人關懷服務 文書"
PIC = "黃X萍"
outR = 2
For i = 2 To crow Step 3
iR = i + 2
' range("A4:E4") copy to 另一sheet之range("A2:E2")
Range("A" & iR, "E" & iR).Copy Destination:=Sheets("VHPrint").Range("A" & outR, "E" & outR)
' 為取得 原始儲存格之格式 先copy取得 F, G之儲存格格式
Range("E" & iR).Copy Destination:=Sheets("VHPrint").Range("F" & outR, "G" & outR)
' Sheets("VHPrint").Range(G,outR)儲存格內容,由Range(A,i)左邊起取出字串至 "】"
pos = InStr(Range("A" & i).Value, "】")
Sheets("VHPrint").Range("G" & outR).Value = Left(Range("A" & i).Value, pos)
Sheets("VHPrint").Cells(outR, 2).Value = VS
Sheets("VHPrint").Cells(outR, 6).Value = PIC
outR = outR + 1
Next
'MsgBox "get ok??"
Sheets("VHPrint").Activate
Range("A1").Select
MsgBox "VHPrint sheet was reduced" & "; 志工人數=" & outR - 2
End Sub
-----by linct-----
你可能也想看
