10-16-CSDN博客

该脚本使用VBScript创建一个Excel应用程序实例，打开指定路径的银行代码txt文件，遍历内容并打开相应的工作簿。然后，它定位到特定工作表并读取单元格数据，根据条件生成SQL更新语句，并写入到.sql文件中。脚本最后关闭工作簿和Excel应用程序。

摘要生成于 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 "Vbs16_実行開始"

Set oExcel=CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")
path = createobject("Scripting.FileSystemObject").GetFile(Wscript.ScriptFullName).ParentFolder.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)

Set a = fs.CreateTextFile(path+"\16_" + sheetNm + ".sql", True)
a.Close
' Read=1 Write=2 Append =8
Set a = fs.OpenTextFile(path+"\16_" + 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 = 3 '
testAnsert=oSheet.cells(OriginRow,OriginCol)
'Wscript.echo testAnsert

if testAnsert="条件" then
'Wscript.echo oSheet.cells(OriginRow,OriginCol).Row
'Wscript.echo oSheet.cells(OriginRow,OriginCol).Column
end If

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

Dim Col:Col = 35 + DiffCol '

Dim Stp:Stp = 0 '

Do Until Stp = 3

Col = Col + 8
Stp = Stp + 1

Dim Row:Row = 5 + DiffRow '12

Select Case Stp
Case 1
param = "ApprovalSetDiv"
Case 2
param = "ApprovalContentFlg" '★
Case 3
param = "BackContentFlg" '★
End Select

value=oSheet.cells(Row,Col)
Select Case value
Case "任意"
Val = 0
Case "必須","役席承認"
Val = 1
Case "管理者承認片方"
Val = 2
Case "管理者承認両方"
Val = 3
End Select

cellColor=oSheet.cells(Row,Col).DisplayFormat.Interior.Color
If cellColor=COLORCONST then
a.WriteLine "UPDATE M_APPROVALPATTERN SET "+ CStr(param) + " = " + "'" + CStr(Val) + "'" + ",LastUpdBankerNm = 'MAINTENANCE',LastUpdDateTime = CURRENT_TIMESTAMP WHERE ApplicationPhase = '9' AND ApplicationType = '01' AND DetailDiv = '00' AND CanvassOfferDiv = '00' AND InsuranceDiv = '00';"
else
End If

Loop

a.Close

Set oSheet=Nothing
oExcel.Quit

msgbox "Vbs16_実行終了"