2018年12月4日 星期二

VBA:EXCEL 管理規則導入應用(檢查票防呆機制建立)(FormatConditions)

故事起源:
這段時間產品出貨的隨附檢查票(又稱COA)居然發生一件天大的慘案!!

慘案!慘案!慘案!(因為被客戶追殺所以重複了3次),有鑒於於此所以到製造現場了解為

何會發生這類的事,經過教育訓練老師過去的指導,透過三現主義(現時、現地、現況)作掌握

後,原因就是人沒確認檢查票就給他蓋章出貨了,故事到此為止後文就不提了。 為了避免再

發,小小的我就發現檢查票是用EXCEL製作跟維護的,就立馬寫了管理規則,透過管理規則

導入上下限的防呆機制,也進一步建立資料為文字與數字的判斷機制。但是!!!算一算近X千張

客製化檢查票ㄝ,怎可能一張一張建立阿。 開始思考使用EXCEL 內建的VBA來建立管理規

則得念頭,鑽研了一段時間,發現這類文章,尤其是中文的資料有點少,當然單純手動新增

管理規則的文章就超多,所以筆者決定透過本篇文章的記錄作分享。

物件:RANGE
屬性:FormatConditions
方法:FormatConditions.ADD

 MSDN 原文 1.管理規則寫入公式:(2個例子)
1.1公式
 ActiveSheet.Range(儲存格位置).FormatConditions.Add Type:=xlExpression, Formula1:="=" & 公式 
ActiveSheet.Range(儲存格位置).FormatConditions(1).Interior.ColorIndex = 3 '我是設定公式成立時反紅
1.2引用EXCEL函數
 ActiveSheet.Range(儲存格位置).FormatConditions.Add Type:=xlExpression, Formula1:="=" & EXCEL函數

 ActiveSheet.Range(儲存格位置).FormatConditions(1).Interior.ColorIndex = 3 

2.管理規則寫入格式化內容:
2.1 不介於(參數設定:xlNotBetween)
 ActiveSheet.Range(R1).FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:=10, Formula2:=20

 ActiveSheet.Range(R1).FormatConditions(1).Interior.ColorIndex = 3

看到此,有沒有一種脫褲子放屁的感覺,VBA寫好了,怎導入?該不會還是要手動建立吧!!!等等這樣的疑問產生。 其實僅有導入管理規則的VBA CODE是不夠的歐,還需要配合檔案開啟等等的VBA指令才能滿足"自動建立的目標"。
 
再請參筆者文章:


EXCEL 開啟檔案 VBA (一) FileDialog應用

開啟EXCEL 開發人員教學:ONEDRIVE 下載

 透過上列網址文件教學,我們可以在"命令按鈕"中貼入以下 VBA CODE。

 Dim FILE_OPEN As FileDialog '宣告FILE_OPEN為檔案對話框

 Set FILE_OPEN= Excel.Application.FileDialog(msoFileDialogFilePicker) '設定FILE_OPEN為選取檔案功能 

補充說明:"msoFileDialogFilePicker"指選取檔案若要選取資料夾請改
為"msoFileDialogFolderPicker "

FILE_OPEN.InitialFileName = Excel.ActiveWorkbook.Path '對話框開始目錄的設定

FILE_OPEN.Filters.Add "Excel File", "*.xls*" '設定對話框要顯示的副檔名

 FILE_OPEN.Filters.Add "所有檔案", "*.*" FILE_OPEN.Show '顯示對話框

圖1.對話框產生

但僅僅這樣還不夠,還需要以下的VBA CODE 才能開啟檔案歐

 For i = 1 To FILE_OPEN.SelectedItems.Count

 Source = Excel.ActiveWorkbook.NAME '儲存目前作業中檔案名稱

 FILE_OPEN_PATH = FILE_OPEN .SelectedItems(i) '取的檔案路徑

 Workbooks.Open Filename:=FILE_OPEN_PATH '開啟案路徑

 WORKNAME = Excel.ActiveWorkbook.NAME '儲存新開檔案的檔案名稱

 Windows(WORKNAME).Activate '啟用新開檔案的檔案名稱

 Windows(Source).Activate '啟用目前作業中檔案名稱

 NEXT I

 完整版:


功能說明:透過對話框打開一個或多個檔案後,自動開啟檔案,制於要開啟檔案作那些"加工",就看需求作增加了。

2018年11月20日 星期二

EXCEL VBA:集保庫存 (檔案包版)

之前花了一點時間作集保庫存自動抓取資料改版,後來從網頁爬蟲的方式改為檔案包,速度與穩定度明顯更好,且不會造成官方網站流量問題。
作業流程:
1.定時自動抓檔案包。(之前這部分是透過網路爬蟲方式,感覺不夠快)
說明:之前用網路爬蟲大約花15分鐘完成,但就是怕網站無回應產生錯誤,即使寫了防呆,中間也發現等待時間比處理資料的時間更久!!!一直都知道集保庫存的官方網站有提供檔案包,就開始萌生改成用檔案包方式作資料更新,透過檔案包一次性下載後,再透過陣列方式直接更新到各別股票檔案也僅需5分鐘不到(1607個股檔案,看等待時間花了多久),讚!!
主要是利用QueryTables的功能來完成資料擷取,順便自動存檔,為避免資料日期重複,在作檔案包時,檢查資料日期也很重要,總不能一直抓重複日期的資料。
範本參考:Microsoft MSDN
基本上僅修改範例中的網址部分(紅色圈記處),即可作動。



2.開啟檔案包資料並複製。
3.開啟個股EXCEL資料檔,將對應集保庫存資料寫入。
   說明:按每周資料作逐一維護,目前已陸續累積了約106周的資料。


圖1.現有集保庫存(部分資料)
4.開始分析各類持股的變化,並彙整成報告方式作彙整。分析結果參照片,喜歡已經整理好的輸出方式跟分類。
說明:如何完成減少幾期;增加幾期的分析,這部分主要是利用遞迴技巧完成的,有機會再整理遞迴的概念。
圖2.分析後報告輸出

數字統計:20240223

成交量大  漲幅大 統計: 演算法模擬: