所以，在我放置我的代碼之前，我會解釋我想要做什么，因為我不能自己測驗腳本，因為它應該做什么，影響它必須做什么。我知道這有點奇怪，但請多多包涵。
每兩周左右一次，我們目前運行批處理檔案以更新我們組織中所有 WS 上的特定工具。
是的，我們確實有工具傳播軟體，但由于這個特定工具非常重要，我們不相信它會分發給任何自動化方法，這些方法在大多數情況下已被證明在我們無法理解原因的情況下失敗。
因此，我撰寫了一些簡單的命令批處理檔案來運行安裝命令，并將輸出寫入一個文本檔案，然后我們手動查看它安裝在哪個 ws 上，哪些沒有安裝。
未安裝它的 ws 是我們知道由于故障而知道的 ws，我們有其他問題，然后我們將所有努力都用于查找和修復這些問題。
正如你可以想象的那樣，這是一項耗時的作業，我決定盡可能地自動化手動檢查，以便快速知道哪個 ws 失敗，以及失敗代碼。
我從 excel 中的 ws 名稱串列開始。
例如，
K190ASSn1.domainname
m930eastgate.domainname
n190alka.domainname
n190amsv.domainname
n190amzi.domainname
N190ARME.domainname
N190AVMA.domainname
N190AVNT.domainname
n190chockstest.domainname
N190DLCR.domainname
N190DNBS.domainname
N190edsh.domainname
n190ehma2.domainname
N190EISH.domainname
我撰寫了我的腳本來執行以下操作：
- 將 A 列中的所有 ws 名稱讀入一個陣列。
- 遍歷陣列，并使用Shell函式呼叫外部cmd檔案，然后運行，并將運行結果寫入位于D盤名為“Minstall”的目錄中的TXT檔案。
- 然后我將在該目錄中創建的所有檔案的名稱讀入一個新陣列。
- 我對兩個陣列從 A 到 Z 進行排序（使用我在網上找到的腳本），以使下一階段的所有內容都以相同的順序排列。
- 然后，我遍歷第二個陣列中的檔案名，并將每個檔案讀入一個文本欄位，然后我對其進行決議以查找腳本運行的結果。
- 然后將該結果寫入與我讀取的檔案名相同的邏輯位置的第三個陣列中。
- 最后，我將檔案名重新寫入作業表，覆寫其中的內容，并在相鄰列中，將運行結果從第三個陣列中的相關單元格位置寫入。
然后，我將得到一個檔案，其中包含一個可見點中的所有資料（我希望如此）。
在稍后階段，我將添加一個腳本，該腳本將通過電子郵件向相關團隊發送他們需要處理的 ws 串列（那些運行結果不同于零的那些），以及他們需要做什么。但這不適合此時此地。
因為如果我運行代碼并且它可以作業（我希望）它會執行更新，而我還不想這樣做，我真正想要的是額外的眼睛來檢查我的代碼，看看我是否為上面定義的每個動作寫的都是正確的，并且會起作用，如果有辦法寫我所做的，那就更好了。
總的來說，我經歷了每個階段，一切“看起來”都很好。
有人愿意在這里提供幫助嗎？
應@CDP1802 的要求添加：
Examples of the two different results that can be found in the text files. One contains a result of zero, meaning that the script worked. The other contains a code of 1603, which is a generic "there's a problem captain but I don't know what it is" response from M$ msiexec.
The spaces between the lines of the text are what appear in the actual text file.
Example 1 (0 response)
PsExec v2.33 - Execute processes remotely
Copyright (C) 2001-2021 Mark Russinovich
Sysinternals - www.sysinternals.com
C:\Windows\system32>msiexec /i "\\server\Minstall\Installation.msi" /qn ACCEPTEULA=YES REPORTSERVER=server.domainname USESSL=TRUE REPORTSERVERPORT=443 IGNORESSLERRORS=TRUE InstallCertificates=yes /l*v C:\Windows\TEMP\install_log4258289.txt
Connecting to K190LPRTLV4.iaadom...
Starting PSEXESVC service on K190LPRTLV4.iaadom...
Copying authentication key to K190LPRTLV4.iaadom...
Connecting with PsExec service on K190LPRTLV4.iaadom...
Copying d:\Install425.bat to K190LPRTLV4.iaadom...
Starting d:\Install425.bat on K190LPRTLV4.iaadom...
Install425.bat exited on K190LPRTLV4.iaadom with error code 0.
Example 2 (1603 response)
PsExec v2.33 - Execute processes remotely
Copyright (C) 2001-2021 Mark Russinovich
Sysinternals - www.sysinternals.com
C:\Windows\system32>msiexec /i "\\server\Minstall\Installation.msi" /qn ACCEPTEULA=YES REPORTSERVER=server.domainname USESSL=TRUE REPORTSERVERPORT=443 IGNORESSLERRORS=TRUE InstallCertificates=yes /l*v C:\Windows\TEMP\install_log4258289.txt
Connecting to K190LPRTLV3.iaadom...
Starting PSEXESVC service on K190LPRTLV3.iaadom...
Copying authentication key to K190LPRTLV3.iaadom...
Connecting with PsExec service on K190LPRTLV3.iaadom...
Copying d:\Install425.bat to K190LPRTLV3.iaadom...
Starting d:\Install425.bat on K190LPRTLV3.iaadom...
Install425.bat exited on K190LPRTLV3.iaadom with error code 1603.
The updated code is as follows:
Option Explicit
Sub Check_Files()
Const Col_Names = "A"
Const Col_Result = "B"
Const Row_Text = 4 'first line of text and result
Dim wb As Workbook
Dim wsMain As Worksheet
Dim WSNames() As String 'Will hold all the ws names as an array read from column A
Dim WSResult() 'Will hold result for specific ws
Dim DirectoryListArray() As string
ReDim DirectoryListArray(3000) 'Set the directory listing array size to 3000 as a max count
Dim NumberArray() As Long
Dim lastrow As Long, FileCount As Long, NumberCount As Long, r As Long, i As Long, j As Long
Dim awsname as string, strDir As string, strPath As string
Dim item as variant
Dim ReadFile As String, text As String, textline As String, RetCode As Integer
Set wb = ActiveWorkbook
With wb
Set wsMain = .Sheets("Main")
End With
'Copy ws names into array for speed
With wsMain
lastrow = .Cells(.Rows.Count, Col_Names).End(xlUp).Row
If lastrow < Row_Text Then
MsgBox "No ws names found in column " & Col_Names, vbCritical
Exit Sub
End If
WSNames = .Cells(1, Col_Names).Resize(lastrow).Value2
ReDim WSResult(1 To lastrow)
End With
'Write how many names were read into array
Cells(1,3) = "Number of names read into array is " & lastrow
'loop through all ws names and run the batch file for each one
For r = Row_Text To UBound(WSNames)
awsname = WSNames(r, 1) 'Read in next ws name from array
Runcmd(awsname)
Next r
'Write how many batch files were run into worksheet
Cells(2,3) = "Number of batch files run is " & r
'count how many text files have been created
strDir = "D:\Minstall"
strPath = strDir & "\*.txt"
'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(strPath)
Do While MyFile <> ""
DirectoryListArray(FileCount) = MyFile
MyFile = Dir$
FileCount = FileCount 1
Loop
'Reset the size of the array without losing its values by using Redim Preserve
Redim Preserve DirectoryListArray(FileCount - 1)
'Write how many text files were found
Cells(3,3) = "Number of txt files found is " & FileCount
''Debug.Print writes the results to the Immediate window (press Ctrl G to view it)
'For FileCount = 0 To UBound(DirectoryListArray)
'Debug.Print DirectoryListArray(FileCount)
'Next FileCount
'Sort the arrays so that we have the same order in both arrays
'Since both arrays should in effect have the same amount of elements
'sorting names array from A to Z
For i = LBound(WSNames) To UBound(WSNames)
For j = i 1 To UBound(WSNames)
If UCase(WSNames(i,1)) > UCase(WSNames(j,1)) Then
Temp = WSNames(j,1)
WSNames(j,1) = WSNames(i,1)
WSNames(i,1) = Temp
End If
Next j
Next i
'sorting file array from A to Z
For i = LBound(DirectoryListArray) To UBound(DirectoryListArray)
For j = i 1 To UBound(DirectoryListArray)
If UCase(DirectoryListArray(i,1)) > UCase(DirectoryListArray(j,1)) Then
Temp = DirectoryListArray(j,1)
DirectoryListArray(j,1) = DirectoryListArray(i,1)
DirectoryListArray(i,1) = Temp
End If
Next j
Next i
NumberCount = 0
'Loop through files in directory based on what's in array
For i = LBound(DirectoryListArray) To UBound(DirectoryListArray)
ReadFile = "D:\Minstall" & "\" & DirectoryListArray(NumberCount)
ReadFile = Application.GetOpenFilename()
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
RetCode = InStr(text, "with error code ")
NumFound = Mid(text, posLat 16, 1)
If NumFound > 0 Then
NumFound = Mid(text, posLat 16, 4)
'Write the number found into the number array
NumberArray(NumberCount) = NumFound
NumberCount = NumberCount 1
Else
'Write the number found into the number array
NumberArray(NumberCount) = NumFound
NumberCount = NumberCount 1
End If
Next i
'Write the ws name into the worksheet and write the number found to the cell to the right of the ws name in the worksheet
For i = LBound(WSNames) To UBound(WSNames)
Cells(j, Col_Names) = WSNames(i,1)
Cells(j, Col_Result) = NumberCount(i,1)
j = j 1
Next i
End Sub
Sub Runcmd(awsname)
Dim PathToBatch as string
'Set the path and batch file with the ws name as a parameter for the batch to run
PathToBatch = "D:\min425.cmd" & " " & awsname
Call Shell(PathToBatch, vbNormalFocus)
End Sub
uj5u.com熱心網友回復：
主要更改是使用FileSystemObject讀取文本檔案，使用正則運算式提取錯誤代碼，使用WScript.Shell物件運行批處理檔案，以便宏等待腳本完成。我已注釋掉該RunCmd行并將其替換為RunTest創建文本檔案的 a，以便您對其進行測驗。
Option Explicit
Sub Check_Files()
Const DIR_OUT = "D:\Minstall"
Const COL_NAMES = "A"
Const COL_RESULTS = "B"
Const COL_TS = "C" ' timestamp
Const COL_ERR = "D" ' Shell errors
Const ROW_START = 4 'first line of text and result
Dim wb As Workbook, ws As Worksheet
Dim rng As Range, arNames, awsname As String
Dim result As String, txtfile As String
Dim i As Long, LastRow As Long, n As Long, r As Long, colour As Long
Dim t0 As Single: t0 = Timer
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
With ws
' read names into array
LastRow = .Cells(.Rows.Count, COL_NAMES).End(xlUp).Row
n = LastRow - ROW_START 1
If n < 1 Then
MsgBox "No records found on " & ws.Name, vbCritical
Exit Sub
Else
Set rng = .Cells(ROW_START, COL_NAMES).Resize(n)
arNames = rng.Value2
'Write how many names were read into array
.Cells(1, 3) = "Number of names read into array is " & n
End If
' clear results
With rng.Offset(, 1).Resize(, 3)
.Clear
.Interior.Pattern = xlNone
End With
' run commands with WsSCript
Dim WShell As Object
Set WShell = CreateObject("WScript.Shell")
For i = 1 To UBound(arNames)
awsname = arNames(i, 1)
r = ROW_START i - 1
' RUN COMMANDS
.Cells(r, COL_ERR) = RunTest(awsname, DIR_OUT)
'.Cells(r, COL_ERR) = RunCmd(WShell, awsname, DIR_OUT)
.Cells(r, COL_TS) = Format(Now, "yyyy-mm-dd HH:MM:SS") ' timestamp
Next
Set WShell = Nothing
'Write how many batch files were run into worksheet
.Cells(2, 3) = "Number of batch files run is " & UBound(arNames)
' read text files with FSO, parse with regex
Dim FSO As Object, ts As Object, regex As Object, txt As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Set regex = CreateObject("VBScript.RegExp")
With regex
.Global = False
.MultiLine = True
.IgnoreCase = True
.Pattern = "with error code (\d )"
End With
n = 0
' process text file
For i = 1 To UBound(arNames)
r = ROW_START i - 1
awsname = arNames(i, 1)
txtfile = DIR_OUT & awsname & ".txt"
result = ""
' does file exist for this machine
If FSO.fileExists(txtfile) Then
' read file
n = n 1
Set ts = FSO.openTextfile(txtfile)
txt = ts.readall
ts.Close
' extract error number from text
If regex.test(txt) Then
result = regex.Execute(txt)(0).submatches(0)
End If
' error codes
If result = "0" Then
colour = RGB(0, 255, 0) ' green
Else
colour = RGB(255, 255, 0) ' yellow
End If
Else
result = "No Text File"
colour = RGB(255, 0, 0) ' red
End If
' result
With .Cells(r, COL_RESULTS)
.Value2 = result
.Interior.Color = colour
End With
Next
.Cells(3, 3) = "Number of txt files found is " & n
.Columns.AutoFit
End With
MsgBox "Text files found for " & n, vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
Function RunTest(awsname As String, folder As String) As String
Dim FSO, ts, errno: Set FSO = CreateObject("Scripting.FileSystemObject")
If Rnd() < 0.3 Then errno = 0 Else errno = Int(10000 * Rnd())
Set ts = FSO.createTextFile(folder & awsname & ".txt")
ts.write "This is with error code " & errno & "." & vbCrLf & vbCrLf
ts.Close
RunTest = "Test"
End Function
Function RunCmd(WShell, awsname As String, folder As String) As String
MsgBox "RunCmd DISABLED", vbCritical: End
'Const SCRIPT = "D:\min425.cmd"
'Dim cmd: cmd = SCRIPT & " " & awsname
'RunCmd = WShell.Run(cmd, vbNormal, True) ' waittocomplete
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411631.html
標籤：