「Libreoffice VBA」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 49 次修訂)
第1行: 第1行:
==相容指令==
==相容指令==
*Option VBASupport 1
*Application.function
** Application.ScreenUpdating = TRUE &  Application.ScreenUpdating = FALSE 更新畫面與否
==SQL輸入範例==
<vb>
Option VBASupport 1
Option VBASupport 1
==SQL輸入範例==
Option VBASupport 1
  Private Sub CommandButton1_Click()
  Private Sub CommandButton1_Click()
     Dim I As Integer
     Dim I As Integer
第21行: 第25行:
     MsgBox "執行完成"
     MsgBox "執行完成"
  End Sub
  End Sub
</vb>


==判斷INSERT OR UPDATE==
==判斷INSERT OR UPDATE==
<vb>
  Option VBASupport 1
  Option VBASupport 1
  Private Sub CommandButton1_Click()
  Private Sub CommandButton1_Click()
第53行: 第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
第62行: 第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預存程式]
第93行: 第103行:
** BOMMC.MC002 單位
** BOMMC.MC002 單位
** BOMMC.MC004 標準批量
** BOMMC.MC004 標準批量
** BOMMC.MC005 製令單別
** BOMMC.MC005 製令單別,5101製造課工單,5102加工課工單,5103委外加工單,5198製造課開帳製令單,5199加工課開帳製令單
** BOMMC.MC009 版次
** BOMMC.MC009 版次
** BOMMC.MC010 備註
** BOMMC.MC010 備註
** BOMMC.MC013 來源
** BOMMC.MC013 來源
** BOMMC.MC016 確認碼
** BOMMC.MC016 確認碼
 
** BOMMC.MC017 確認日期
** BOMMC.MC018 確認者
** BOMMC.MC019 簽核狀態碼(預設N:不執行電子簽核)
** MC011,MC012 預留欄位,預設值0
* BOMMD BOM用量資料單身檔欄位名稱
* BOMMD BOM用量資料單身檔欄位名稱
** BOMMD.MD001 主件品號
** BOMMD.MD001 主件品號
第108行: 第121行:
** BOMMD.MD008 損耗率
** BOMMD.MD008 損耗率
** BOMMD.MD016 備註
** BOMMD.MD016 備註
 
* INVMB 品號基本資料表
** INVMB.MB001 品號
** INVMB.MB002 品名
** INVMB.MB003 規格
** INVMB.MB004 庫存單位
* 查詢元件品號VBA程式
* 查詢元件品號VBA程式
<vb>
  Option VBASupport 1
  Option VBASupport 1
  Private Sub SubObjectNumber()
  Private Sub SubObjectNumber()
第115行: 第133行:
   Dim cn As New ADODB.Connection
   Dim cn As New ADODB.Connection
     Dim rs As New ADODB.Recordset  '定義記錄物件  
     Dim rs As New ADODB.Recordset  '定義記錄物件  
   Dim strCn As String, strSQL As String    
   Dim strCn As String, strSQL As String
   strCn = "Provider=sqloledb;Server=192.168.1.101;Database=DEMO;Uid=sa;Pwd=2idialul;"
   strCn = "Provider=sqloledb;Server=192.168.1.101;Database=DEMO;Uid=xx;Pwd=xxxxxxx;"
   cn.Open strCn
   cn.Open strCn
   Set rs = New ADODB.Recordset
   Set rs = New ADODB.Recordset
第123行: 第141行:
       '底下寫執行sql語法
       '底下寫執行sql語法
       strSQL = "SELECT MB001 FROM INVMB WHERE MB002 = '"&Range("C" & I)&"'"
       strSQL = "SELECT MB001 FROM INVMB WHERE MB002 = '"&Range("C" & I)&"'"
rs.Open(strSQL, cn)   
      rs.Open(strSQL, cn)   
       Do Until rs.EOF
       Do Until rs.EOF
    Range("B" & I) = rs("MB001")     
    Worksheets("元件品號").Range("B" & I) = rs("MB001")     
     rs.MoveNext
     rs.MoveNext
   Loop  
   Loop
       rs.Close()
       rs.Close()
     I = I + 1
     I = I + 1    
  Loop
  Loop
  cn.Close   
</vb>
  Set cn = Nothing
  MsgBox "擷取資料成功"
End Sub
*[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方法?]
== APR匯入ERP==
# 利用線材標籤及加工發料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》
# 由上述資料庫分析,可利用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
*[http://excel.walk-nie.com/vba-basic-op/vba-basic-op-cell/419 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>
*[http://www.dotblogs.com.tw/topcat/archive/2008/03/05/1204.aspx RecordSet判斷是否有資料的觀念]


==銷貨單列印==
==銷貨單列印==
第144行: 第215行:


==參考資料==
==參考資料==
[http://www1.cpshs.hcc.edu.tw/leson/learn/ASP%E8%B3%87%E6%96%99%E5%BA%AB/new_page_8.htm 資料庫連接]
*[http://www1.cpshs.hcc.edu.tw/leson/learn/ASP%E8%B3%87%E6%96%99%E5%BA%AB/new_page_8.htm 資料庫連接]
*[http://www.1keydata.com/tw/sql/sql.html SQL語法查詢]
*[http://isvincent.pixnet.net/blog/post/39228927-excel-%E8%A1%A8%E5%96%AE%E6%8E%A7%E5%88%B6%E9%A0%85%E6%96%87%E7%AB%A0%E9%9B%86 Excel-表單控制項文章集]

於 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 銷貨單身檔

參考資料