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-----