「Libreoffice VBA」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 14 次修訂)
第28行: 第28行:


==判斷INSERT OR UPDATE==
==判斷INSERT OR UPDATE==
<vb>
  Option VBASupport 1
  Option VBASupport 1
  Private Sub CommandButton1_Click()
  Private Sub CommandButton1_Click()
第58行: 第59行:
   MsgBox "執行完成 RANGE A" &Range("A" & I)
   MsgBox "執行完成 RANGE A" &Range("A" & I)
  End Sub
  End Sub
</vb>


==開舊檔案==
==開舊檔案==
<vb>
  Sub Main
  Sub Main
  Dim Doc As Object
  Dim Doc As Object
第67行: 第70行:
  Doc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, Dummy())  
  Doc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, Dummy())  
  End Sub 
  End Sub 
</vb>
==訂單備註與製令備註連結==
==訂單備註與製令備註連結==
* [http://yes.nctu.edu.tw/sql/StoredProcedure/StoredProcedure.htm Store Procedure預存程式]
* [http://yes.nctu.edu.tw/sql/StoredProcedure/StoredProcedure.htm Store Procedure預存程式]
第122行: 第127行:
** INVMB.MB004 庫存單位
** INVMB.MB004 庫存單位
* 查詢元件品號VBA程式
* 查詢元件品號VBA程式
<vb>
  Option VBASupport 1
  Option VBASupport 1
  Private Sub SubObjectNumber()
  Private Sub SubObjectNumber()
第143行: 第149行:
     I = I + 1     
     I = I + 1     
   Loop
   Loop
</vb>
*[https://www.ptt.cc/bbs/Visual_Basic/M.1405988823.A.2B9.html VBA 連到ACCESS方法?]
*[https://www.ptt.cc/bbs/Visual_Basic/M.1405988823.A.2B9.html VBA 連到ACCESS方法?]


第191行: 第198行:


== 參考SQL程式碼==
== 參考SQL程式碼==
* UPDATE ADMMG SET MG006= Stuff(MG006,3,1,'N') WHERE MG001 LIKE 'A%' 取代
* <vb>UPDATE ADMMG SET MG006= Stuff(MG006,3,1,'N') WHERE MG001 LIKE 'A%' 取代</vb>
* UPDATE LABEL SET LENGTH = REPLACE(LENGTH,'M','') 將M拿掉
* <vb>UPDATE LABEL SET LENGTH = REPLACE(LENGTH,'M','') '將M拿掉</vb>
* SELECT  WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL WHERE CPARTNO ='5BA-E24-J8BRM01' GROUP BY WIRE
* <vb>SELECT  WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL WHERE CPARTNO ='5BA-E24-J8BRM01' GROUP BY WIRE</vb>
* SELECT WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL WHERE CPARTNO IN(SELECT CPARTNO FROM PRODUCT WHERE TSKPARTNO ='003C-4010319951版34/103降5%') GROUP BY WIRE ORDER BY WIRE
*  
*SELECT  X.TA001,X.TA002,X.TA004,X.TA009,X3.TA009 FROM SFCTA X JOIN (SELECT TA001,TA002,TA004,TA009 FROM SFCTA WHERE TA004='HM03')X3 ON X.TA001=X3.TA001 AND X.TA002=X3.TA002
<vb> SELECT WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL  
WHERE CPARTNO IN(SELECT CPARTNO FROM PRODUCT WHERE TSKPARTNO ='003C-4010319951版34/103降5%')
GROUP BY WIRE ORDER BY WIRE</vb>
*<vb>SELECT  X.TA001,X.TA002,X.TA004,X.TA009,X3.TA009 FROM SFCTA X JOIN (SELECT TA001,TA002,TA004,TA009 FROM SFCTA WHERE TA004='HM03')X3 ON X.TA001=X3.TA001 AND X.TA002=X3.TA002</vb>
*[http://www.dotblogs.com.tw/topcat/archive/2008/03/05/1204.aspx RecordSet判斷是否有資料的觀念]
*[http://www.dotblogs.com.tw/topcat/archive/2008/03/05/1204.aspx RecordSet判斷是否有資料的觀念]



於 2015年10月5日 (一) 08:55 的最新修訂

相容指令

  • Option VBASupport 1
  • Application.function
    • Application.ScreenUpdating = TRUE & Application.ScreenUpdating = FALSE 更新畫面與否

SQL輸入範例

<vb> Option VBASupport 1

Private Sub CommandButton1_Click()
   Dim I As Integer
   Dim cn As New ADODB.Connection
   Dim strCn As String, strSQL As String    
   strCn = "Provider=sqloledb;Server=192.168.1.xx;Database=DEMO;Uid=xx;Pwd=xxxx;"    
   cn.Open strCn
   I = 2
   Do While Cells(I, 1) <> ""
       strSQL = "UPDATE PURTD set TD012='" & Range("E" & I) & "' where TD001='" & Range("A" & I) & "' and TD002='" & Range("B" & I) & "' 
       and TD003='" &    Range("C" & I) & "'"
       '底下寫執行sql語法
       cn.Execute strSQL
       I = I + 1
   Loop
   cn.Close    
   Set cn = Nothing
   MsgBox "執行完成"
End Sub

</vb>

判斷INSERT OR UPDATE

<vb>

Option VBASupport 1
Private Sub CommandButton1_Click()
  Dim I As Integer
  Dim cn As New ADODB.Connection
  Dim strCn As String, strSQL As String   
  
  strCn = "Provider=sqloledb;Server=192.168.1.x;Database=xxx;Uid=xx;Pwd=xxxxxxxxx;"
   
  cn.Open strCn
  I = 3
  Do While Cells(I, 1) <> ""   
      
      strSQL = "IF EXISTS (SELECT * FROM CUPRICE WHERE Date = '" &Range("A" & I) &"')"
      strSQL = strSQL & "UPDATE CUPRICE SET Exchange = '"&Range("B" & I) &"'"
      strSQL = strSQL & "WHERE Date='"&Range("A" & I) &"'"
      strSQL = strSQL & "Else "
      strSQL = strSQL & "INSERT INTO CUPRICE (Date,Exchange,LPrice,HPrice,LCPrice,HCPrice,L3MPrice,H3MPrice,L3MCPrice,H3MCPrice) VALUES ('" &Range("A" & I) &"','"_
               &Range("B" & I) &"','" &Range("C" & I) &"','" &Range("D" & I) &"','" &Range("E" & I) &"','" &Range("F" & I) &"','" &Range("G" & I) &"','"_
               &Range("H" & I) &"','" &Range("I" & I) &"','" &Range("J" & I)&"')"
      '底下寫執行sql語法
      
      
      
      cn.Execute strSQL
      I = I + 1
  Loop
  cn.Close    
  Set cn = Nothing
  MsgBox "執行完成 RANGE A" &Range("A" & I)
End Sub

</vb>

開舊檔案

<vb>

Sub Main
Dim Doc As Object
Dim sUrl As String
Dim Dummy()
sUrl = "file:///C:/test/test.odt" 」
Doc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, Dummy()) 
End Sub 

</vb>

訂單備註與製令備註連結

Create Proc sp_SprocName1 
As 
 Select * From Table 
Go
  • SQL Ageng排程
  • 日期格式:Select CONVERT(varchar(100), GETDATE(), 112): 20060516
  • UPDATE MOCTA SET MOCTA.TA029 = COPTD.TD020 FROM MOCTA,COPTD WHERE MOCTA.TA026 = COPTD.TD001 AND MOCTA.TA027 = COPTD.TD002 AND MOCTA.TA028 = COPTD.TD003 AND MOCTA.TA003=CONVERT(varchar(100), GETDATE(), 112)
  • MOCTA 製造命令單頭檔
    • MOCTA.TA024 母製令單別 MOCTA.TA025 母製令編號
    • MOCTA.TA026 訂單單別 MOCTA.TA027 訂單單號 MOCTA.TA028 訂單序號
    • MOCTA.TA029 備註
  • COPTD 客戶訂單單身資料檔
    • COPTD.TD001 訂單單別 COPTD.TD001 訂單單號 COPTD.TD001 訂單序號
    • COPTD.TD020 備註

SQL查詢

  • SELECT 材料名稱, SUM(數量) FROM quotat where 料號="ABCDETEST" GROUP BY 材料名稱
  • SELECT 材料名稱, SUM(數量*排序) FROM quotat where 料號="ABCDETEST" GROUP BY 材料名稱

BOM畫面

  1. 使用VBA匯入BOM表時,先判定單頭BOOMC.MD001(主件品號(KEY))是否存在,若無則可匯入BOMMD單身內。若有則要求使用者使用BOM變更單建立作業(BOMI04)進行變更。
  2. 從BOMMC及BOMMD的欄位名稱中發現,BOM的輸入主要是要填入正確的元件品號及用量,品名規格並不在BOMMC及BOMMD內。若要設計BOM匯入的功能,因零件品號不可能記憶,故可輸入零件的規格和規格從品號資料庫(INVMB)對應出BOM元件品號再輸入用量。
  3. 為了要從APR中撈出資料,快速的將現有的BOM導入ERP內,需討論現有的資料中,哪個資料較適合。(業務報價BOM OR 加工發料BOM)
  4. 決定了BOM的來源,需再考慮資料對應的問題,現有APR的資料存在一個很大的問題,業務和加工的零件名稱無法匹配,故怎樣用品名、規格來找出品號,是很大的問題,所以這是首先要解決的,接下來才有辦法製作外掛。統一名稱會有個好處,就是未來不同單位的資料溝通才不會存在歧異。
  • BOMMC BOM用量資料單頭檔欄位名稱
    • BOMMC.MC001 主件品號
    • BOMMC.MC002 單位
    • BOMMC.MC004 標準批量
    • BOMMC.MC005 製令單別,5101製造課工單,5102加工課工單,5103委外加工單,5198製造課開帳製令單,5199加工課開帳製令單
    • BOMMC.MC009 版次
    • BOMMC.MC010 備註
    • BOMMC.MC013 來源
    • BOMMC.MC016 確認碼
    • BOMMC.MC017 確認日期
    • BOMMC.MC018 確認者
    • BOMMC.MC019 簽核狀態碼(預設N:不執行電子簽核)
    • MC011,MC012 預留欄位,預設值0
  • BOMMD BOM用量資料單身檔欄位名稱
    • BOMMD.MD001 主件品號
    • BOMMD.MD002 序號
    • BOMMD.MD003 元件品號
    • BOMMD.MD004 單位
    • BOMMD.MD006 組成用量
    • BOMMD.MD007 底數
    • BOMMD.MD008 損耗率
    • BOMMD.MD016 備註
  • INVMB 品號基本資料表
    • INVMB.MB001 品號
    • INVMB.MB002 品名
    • INVMB.MB003 規格
    • INVMB.MB004 庫存單位
  • 查詢元件品號VBA程式

<vb>

Option VBASupport 1
Private Sub SubObjectNumber()
 Dim I,J As Integer
 Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset  '定義記錄物件 
 Dim strCn As String, strSQL As String
 strCn = "Provider=sqloledb;Server=192.168.1.101;Database=DEMO;Uid=xx;Pwd=xxxxxxx;"
 cn.Open strCn
 Set rs = New ADODB.Recordset
 I = 2  
 Do While Cells(I, 1) <> ""   
      '底下寫執行sql語法
     strSQL = "SELECT MB001 FROM INVMB WHERE MB002 = '"&Range("C" & I)&"'"
     rs.Open(strSQL, cn)   
     	Do Until rs.EOF
   	Worksheets("元件品號").Range("B" & I) = rs("MB001")    
   	 rs.MoveNext
 		 Loop
     rs.Close()
  	  I = I + 1     
 Loop

</vb>

APR匯入ERP

  1. 利用線材標籤及加工發料BOM之現有資料完成匯入ERP的BOM
    • 線材標籤現狀況,目前資料存放於\\DATA\ARP及\FAX_MAIL\其他主管\莊秀春下。
    • 線材標籤〈加工設計書標籤〉資料庫格式: AS 〈LABEL〉
      • 《客戶別》(CHAR 10) AS 《CUSTOMER》
      • 《料號》 (CHAR 30)對應PRODUCT.客戶料號 AS 《CPARTNO》
      • 《線材代號》(CHAR 55) AS 《WIRE》
      • 《長度》(CHAR 10) 單位m AS 《LENGTH》
      • 《確認日期》(DATE) AS 《ENSUREDATE》
      • 《版次》(CHAR 4) AS 《VAR》
      • 《條數》(CHAR 3) AS 《CORENO》
    • 加工發料BOM資料庫格式:
    • 〈FMHM01-2〉:AS 〈HMBOM〉
      • 《料號》(CHAR 40)對應PRODUCT.料號(即含客戶代號之料號) AS 《TSKPARTNO》
      • 《品名規格》(CHAR 66) 即TSK現有習慣稱法,對應《MS.品名規格》 AS 《PARTNAME》
      • 《每台用量》(N 6.0) AS 《AMOUNT》
    • 〈MS〉:AS 〈MS〉
      • 《品名規格》(CHAR 66)對應《FMHM01-2.品名規格》即ERP品號之規格 AS 《PARTNAME》
      • 《品名》(CHAR 60)即ERP品號之品名 AS 《ERPARTNAME》
    • 業務〈PRODUCT〉:AS 〈PRODUCT〉
      • 《料號》(CHAR 30) 即客戶代號+客戶料號 AS 《TSKPARTNO》
      • 《品名_規格》(CHAR ) AS 《PARTSPEC》
      • 《客戶料號》(CHAR 30) AS 《CPARTNO》
  2. 由上述資料庫分析,可利用NAVICAT定時批次將上述的DAT匯入SQL中,再善用SQL語法(如Join、Group by),以料號或客戶料號分別查出加工發料BOM及標籤BOM,將查出的值填回CALC中,再匯入ERP BOM內。
    • 取出HMBOM(發料BOM)
      • 查詢HMBOM.TSKPARTNO 即PRODUCT.TSKPARTNO
      • 取得HMBOM.PARTNAME及HMBOM.AMOUNT_CELLS(I,3)
      • 由HMBOM.PARTMAE取出MS.ERPPARTNAME_CELLS(I,5)
      • SELECT MS.ERPPARTNAME, HMBOM.AMOUNT FROM MS LEFT JOIN HMBOM ON HMBOM.PARTNAME=MS.PARTNAME WHERE HMBOM.TSKPARTNO = '156-2軸/200/JPN'
    • 取出LABEL(線材標籤BOM)
      • 先計算出HMBOM填入的位置,以便接續填入CAC中
      • LABEL BOM是以客戶料號做為建檔依據,即LABEL.CPARTNO=PRODUCT.CPARTNO
      • 但搜尋所鍵入的品名是採用PRODUCT.TSKPARTNO,即
      • 取得LABEL.WIRE
      • 取得HMBOM.PARTNAME及HMBOM.AMOUNT_CELLS(I,3)
      • 由HMBOM.PARTMAE取出MS.ERPPARTNAME_CELLS(I,5)

參考VBA 函數

  • Environ("ComputerName") '查詢電腦名稱
  • 不帶格式複製貼上
    • sheets(" ").range(" ").Copy
    • sheets(" ").Range(" ").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  • EXCEL VBA框線特性
  • EXCEL 取消框線 LineStyle = xlNone

參考SQL程式碼

  • <vb>UPDATE ADMMG SET MG006= Stuff(MG006,3,1,'N') WHERE MG001 LIKE 'A%' 取代</vb>
  • <vb>UPDATE LABEL SET LENGTH = REPLACE(LENGTH,'M',) '將M拿掉</vb>
  • <vb>SELECT WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL WHERE CPARTNO ='5BA-E24-J8BRM01' GROUP BY WIRE</vb>

<vb> SELECT WIRE, SUM(CAST(LENGTH AS FLOAT) * CAST(CORENO AS FLOAT)) FROM LABEL

WHERE CPARTNO IN(SELECT CPARTNO FROM PRODUCT WHERE TSKPARTNO ='003C-4010319951版34/103降5%')
GROUP BY WIRE ORDER BY WIRE</vb>
  • <vb>SELECT X.TA001,X.TA002,X.TA004,X.TA009,X3.TA009 FROM SFCTA X JOIN (SELECT TA001,TA002,TA004,TA009 FROM SFCTA WHERE TA004='HM03')X3 ON X.TA001=X3.TA001 AND X.TA002=X3.TA002</vb>
  • RecordSet判斷是否有資料的觀念

銷貨單列印

  • COPTC 訂單單頭檔
  • COPTD 訂單單身檔
  • COPTG 銷貨單身檔
  • COPTH 銷貨單身檔

參考資料