07_【別紙】３－②.vbs-CSDN博客

该脚本使用VBScript读取指定路径下的bankCd.txt文件，然后打开对应的Excel工作簿，从名为'【別紙】３－②'的工作表中读取数据。当单元格颜色匹配特定值时，生成SQL更新语句写入到.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 "Vbs7_実行開始"

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)

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

Set a = fs.CreateTextFile(path+"\07_" + sheetNm + ".sql", True)
a.Close
' Read=1 Write=2 Append =8
Set a = fs.OpenTextFile(path+"\07_" + 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 = 7 '
Dim OriginCol:OriginCol = 2 '

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

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

Dim Col:Col = 8 + DiffCol ' 9
Dim Row:Row = 2 + DiffRow ' 8

Stp = Stp + 1
Row = Stp + Row

k = Stp
Select Case k
Case 1, 2
Id = k + 29
Case 3, 4
Id = k + 32
Case 8
Id = 113
Case 9
Id = 143
Case Else
Id = k + 27
End Select

If ( k = 8 or k = 9 ) Then
MsgId = "I00" & CStr(Id)
Else MsgId = "I000" & CStr(Id)
End If

MsgOriVal = oSheet.cells(Row,Col)
cellColor = oSheet.cells(Row,Col).DisplayFormat.Interior.Color

If ( cellColor=COLORCONST ) then
a.WriteLine "UPDATE M_MESSAGE_ORIGIN SET AtHeader = '0', WarningLevel = '1', Value = " + "'" + CStr(MsgOriVal) + "'" + ", LastUpdBankerNm = 'MAINTENANCE', LastUpdDateTime = CURRENT_TIMESTAMP WHERE Id = "+ "'" + CStr(MsgId) + "'" + ";"
else
End If

Loop

a.Close

Set oSheet=Nothing
oExcel.Quit

msgbox "Vbs7_実行終了"