05_【別紙】３－①.vbs

最新推荐文章于 2024-05-27 17:14:56 发布

always007

最新推荐文章于 2024-05-27 17:14:56 发布

阅读量175

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/always007/article/details/115327334

该脚本使用VBScript读取银行代码文件，打开指定的Excel工作簿，然后遍历特定工作表，根据设定的规则生成SQL更新语句。它检查单元格颜色并更新两个表格中的值：M_GENERALIZE_ORIGINSET和M_MESSAGE_ORIGINSET。如果满足条件，将写入SQL更新语句到文本文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

If(Wscript.Arguments.Count<3) then
'msgbox "The count of Arguments is "&Wscript.Arguments.Count
'Quit VBS script
'Wscript.Quit
End If

msgbox "Vbs5_実行開始"

Set oExcel=CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")
path = createobject("Scripting.FileSystemObject").GetFile(Wscript.ScriptFullName).ParentFolder.Path
'パス定義
'Wscript.echo path

'★★
Set f=fs.OpenTextFile(path+"\bankCd.txt")
DO While f.AtEndOfStream <> True
BankCode=f.ReadLine
loop
f.close
'★★

Set oWorkBook=oExcel.Workbooks.Open(path+"\01.しんきん預ナビ設定シート_"+BankCode+".xlsx")

'use the worksheet "【別紙】３－①"
sheetNm = "【別紙】３－①"
Set oSheet=oWorkBook.Sheets(sheetNm)

'Get the used range
'Set Sheet = oExcel.Worksheets("Prop_Methods").UsedRange

Set a = fs.CreateTextFile(path+"\05_" + sheetNm + ".sql", True)
a.Close
' Read=1 Write=2 Append =8
Set a = fs.OpenTextFile(path+"\05_" + sheetNm + ".sql", 8, false)

'背景色常数：RGB(255,255,102)値
Const COLORCONST = 6750207

'we can write more value to the text file by using loop

Dim OriginRow:OriginRow = 8 '
Dim OriginCol:OriginCol = 2 '

Dim DiffRow:DiffRow = OriginRow - 1 '
Dim DiffCol:DiffCol = OriginCol - 1
'Wscript.echo DiffRow '7
'Wscript.echo DiffCol '1

Dim Stp:Stp = 0 '
Do Until Stp = 12

Dim Col:Col = 13 + DiffCol ' 14
Dim Row:Row = 19 + DiffRow ' 26

Stp = Stp + 1
Row = Stp + Row

m = Stp
Select Case m
Case 1, 3
m = Stp * 2
Case 2, 6
m = Stp * 2 - 1
Case 7
m = 1
Case 8, 9, 10, 11
m = Stp - 1
Case Else
m = Stp
End Select

If m > 9 Then
GeneralizeCodeVal = "Confirm_0" & CStr(m)
Else GeneralizeCodeVal = "Confirm_00" & CStr(m)
End If

k = CStr(m)
Select Case k
Case 1, 2, 3
Id = k + 40
Case 4, 5
Id = k + 59
Case 6
Id = k + 60
Case 7, 8, 9
Id = k + 38
Case 10
Id = k + 68
Case 11
Id = k + 70
Case 12
Id = "I00135"
End Select

If k > 11 Then
MsgId = CStr(Id)
Else MsgId = "I000" & CStr(Id)
End If

For k = 1 To 2 step 1

Col = Col + 3

Select Case Col
Case 17
GeneralizeValueVal = oSheet.cells(Row,Col)
If GeneralizeValueVal = "○" Then
GeneralizeValueVal = 1
Else GeneralizeValueVal = 0
End If
cellColor1 = oSheet.cells(Row,Col).DisplayFormat.Interior.Color
Case 20
MsgOriVal = oSheet.cells(Row,Col)
cellColor2 = oSheet.cells(Row,Col).DisplayFormat.Interior.Color
End Select

If ( cellColor1=COLORCONST or cellColor2=COLORCONST ) then
a.WriteLine "UPDATE M_GENERALIZE_ORIGIN SET GeneralizeValue = " + "'" + CStr(GeneralizeValueVal) + "'" + ", LastUpdBankerNm = 'MAINTENANCE', LastUpdDateTime = CURRENT_TIMESTAMP WHERE GeneralizeCode = "+ "'" + CStr(GeneralizeCodeVal) + "'" + " AND ScreenId = '00000'"+ ";"
If ( cellColor2=COLORCONST and MsgOriVal <> "" ) Then
a.WriteLine "UPDATE M_MESSAGE_ORIGIN SET AtHeader = '0', WarningLevel = '1', Value = " + "'" + CStr(MsgOriVal) + "'" + ", LastUpdBankerNm = 'MAINTENANCE', LastUpdDateTime = CURRENT_TIMESTAMP WHERE Id = "+ "'" + CStr(MsgId) + "'" + ";"
End If
else
End If

Loop

a.Close

Set oSheet=Nothing
oExcel.Quit

msgbox "Vbs5_実行終了"