App

原创
2018/08/08 07:20
阅读数 2

'************************************************************************************************

'* 功能 入口

'* 参数

'* 返回值:

'* 备注

'************************************************************************************************

Public Sub Create(ByVal i As Integer)

Dim formatFilePath As String

Dim strFileName As String

Dim rs As ADODB.Recordset

Dim sql, excuteSql, orderSql As String

On Error GoTo ErrHandler

'暂收警告开始

Call WriteLog(Log_Prompt, "zanshou", I_MESSAGE5 & " " & Now())

' Format文件路径

formatFilePath = g_Path & Template_Dir & zsFileName & File_ExtentionName

'创建excel对象

Set xlApp = CreateObject("excel.application")

'打开template

Set xlBook = xlApp.Workbooks.Open(formatFilePath)

'设置excel对象可见

xlApp.Visible = False

'禁止弹出对话框

xlApp.DisplayAlerts = False

'只需要拼接条件的sql

sql = "select T3.PRO_PERIOD AS COL1," & _

"T1.POOMS_PO_NUM AS COL2," & _

"T3.SOD_WST_SS_SM_ORDER_NO AS COL3," & _

"T1.ITEM_NUM AS COL4," & _

"T2.PRT_CDESC AS COL5," & _

"T1.DWG_NO AS COL6," & _

"GET_BIANSHU(T1.PD2_PM2_RQST_NO) AS COL7," & _

"T1.QNTY AS COL8," & _

"T1.DEL_DT AS COL9," & _

"NVL(T1.QNTY - T4.SUMZS,T1.QNTY) AS COL10," & _

"GET_delay_days(T1.DEL_DT) AS COL11," & _

"T2.VEN_VENDOR_NUM AS COL12," & _

"T2.BUYER AS COL13 " & _

"FROM T_PODTL T1 " & _

"LEFT JOIN T_POMSTER T2 " & _

"ON T1.POOMS_PO_NUM = T2.PO_NUM " & _

"LEFT JOIN T_PRMSTER T3 " & _

"ON T1.PD2_PM2_RQST_NO = T3.RQST_NO " & _

"LEFT JOIN (SELECT PODTL_POOMS_PO_NUM, NVL(SUM(QTY_RCVD),0) AS SUMZS " & _

" FROM T_MIDTL " & _

" GROUP BY PODTL_POOMS_PO_NUM) T4 " & _

"ON T1.POOMS_PO_NUM = T4.PODTL_POOMS_PO_NUM " & _

"WHERE GET_delay_days(T1.DEL_DT) > 0 AND (T1.QNTY <>T4.SUMZS OR T4.SUMZS IS NULL) "

orderSql = " ORDER BY COL1,COL2"

'------------------------------------------

Set xlSheet = xlBook.Sheets(SheetName5)

xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A1' OR T3.PRO_PERIOD LIKE '%B3' OR T3.PRO_PERIOD LIKE '%C5')"

excuteSql = excuteSql & orderSql

Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs

xlSheet.Cells(1, 1).Select

xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'------------------------------------------

Set xlSheet = xlBook.Sheets(SheetName4)

xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'Z%'"

excuteSql = excuteSql & orderSql

Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs

xlSheet.Cells(1, 1).Select

xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'------------------------------------------

Set xlSheet = xlBook.Sheets(SheetName3)

xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'W%'"

excuteSql = excuteSql & orderSql

Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs

xlSheet.Cells(1, 1).Select

xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'------------------------------------------

Set xlSheet = xlBook.Sheets(SheetName2)

xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'L%'"

excuteSql = excuteSql & orderSql

xlSheet.[a2].CopyFromRecordset rs

xlSheet.Cells(1, 1).Select

xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'------------------------------------------

Set xlSheet = xlBook.Sheets(SheetName1)

xlSheet.Activate

excuteSql = sql & " AND T1.POOMS_PO_NUM LIKE 'P%'"

excuteSql = excuteSql & orderSql

Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs

xlSheet.Cells(1, 1).Select

xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'-------------------------------------------

strFileName = g_Path & Send_Dir & zsFileName & "_" & Format(Now, "YYMMDD") & File_ExtentionName

xlBook.SaveAs strFileName

xlBook.Close

xlApp.Quit

Set xlSheet = Nothing

Set xlBook = Nothing

Set xlApp = Nothing

Call WriteLog(Log_Prompt, "zanshou", I_MESSAGE6 & " " & Now())

Exit Sub

ErrHandler:

xlBook.Close

' 结束EXCEL对象

xlApp.Quit

' 释放xlApp对象

Set xlApp = Nothing

Call WriteLog(Log_Error, "zanshou", Err.Description)

End Sub

展开阅读全文
打赏
0
0 收藏
分享

作者的其它热门文章

加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部