Close MessageBox on closing Excel file

[Ciupaz]

Hello,
I'm getting a messagebox on running excel macros.
I need to close that messagebox using VBscript in-order to close the Excel .
Is it possible?

Thanks a lot in advance.

Luis

 

[nemmi69]

what message?

 

[Ciupaz]

Just an informative message, nothing important.

 

[nemmi69]

Did you create the message or its an excel message?

 

[Ciupaz]

It was created by an old macro. Unfortunately I don't know VBScript well, so I'm not able to investigate much.

 

[nemmi69]

This is a timed message box set to clear after 5 seconds

Sub MessageBoxTimer()
    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    'Set the message box to close after 10 seconds
    AckTime = 5
    Select Case InfoBox.Popup("Click OK (Message closes automatically after " & AckTime & " seconds).", _
    AckTime, "This is your Message Box", 0)
        Case 1, -1
            Exit Sub
    End Select
End Sub

 

[Ciupaz]

In this way I have to check where this MessageBox is created, and modifying it accordingly, right?

 

[nemmi69]

yes, you could replace any msgbox with a call to this routine

 

[Ciupaz]

This is my code:

Dim args, objExcel, seqNum, ermsNum, homePage, driverDetl, customInput, excelName, noOfRecords, noOfFiles, rarlNotcCount, notcType, vehList, noOfMultiErpOfn, env, noticeNumber, offenderIDno, courtDate
Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")

excelName = args(0)
noOfRecords = args(1)
noticeNumber = args(2)
offenderIDno = args(3)
courtDate = args(4)
noOfFiles = 1

On Error Resume Next

objExcel.workbooks.open excelName
objExcel.visible = True
objExcel.DisplayAlerts = False

Set homePage = objExcel.Worksheets("HomePage")
Set driverDetl = objExcel.Worksheets("DriverDetails")
Set customInput = objExcel.Worksheets("CustomInput")
Set objShell = CreateObject("Wscript.Shell")

' ============================= Notice Generation STARTS ============================
' On Error Resume Next
WScript.Echo "========================================================================"
''''' ROMS - Mention Generation Files '''''
      WScript.Echo "ROMS - Mention Generation Files"
      call copyTheTable("ROMS - Mention Generation Files")
      homePage.Cells(20, 7).Value = noticeNumber
      homePage.Cells(24, 7).Value = offenderIDno
      homePage.Cells(30, 7).Value = courtDate
      call generateTestData(noOfFiles)
      WScript.Echo "File Generation Complete"
      call endTheScript()
' ============================= COMPLETE ============================

Sub endTheScript()
    objExcel.Activeworkbook.Save
    objExcel.Activeworkbook.Close(0)
    objExcel.Quit
    WScript.Echo "Completed..!!"
    WScript.Quit
End Sub


' ============================= Custom Functions ============================

Function createTestData(fileCount)
    WScript.Sleep 1000
    for a = 1 to fileCount
        if notcType = "RFDP" or notcType = "NTO" then 
            Randomize
            randNum = Int((max-min+1)*Rnd+min)
            homePage.Cells(21, 7).Value = "T-" & randNum
        end if
        WScript.Echo "  " & "Test File No.: " & a
        objExcel.Run "Sheet1.CommandButton1_Click"
        WScript.Sleep 1000
    next 
    ' Run the batch job 
    if env = "QA" then 
        objExcel.Run "Sheet1.CommandButton5_Click"
        WScript.Sleep 1000
    end if
End Function

Function createCustomTestData(filePath, batName, noOfFiles)
    ' Generate Test Files
    call generateTestData(noOfFiles)

    ' Run the batch job 
    if env = "QA" then 
        call runTheBatch(filePath, batName, noOfFiles, False)
    end if
End Function


Function copyFileToTemp(path1, path2, destFldr)
    command = "cmd.exe /k move " & path1 & destFldr
    objShell.Run command,1,True
End Function

Function copyTheTable(schemaName)
    homePage.ComboBox3.value = schemaName
    objExcel.Run "Sheet1.CommandButton2_Click"
    WScript.Sleep 1000
End Function

Function generateTestData(loopCounter)
    for l = 1 to loopCounter
        WScript.Echo "  " & "Test File No.: " & l
        objExcel.Run "Sheet1.CommandButton1_Click"
        WScript.Sleep 1000
    next
End Function

Function runTheBatch(filePath, batName, noOfBatRun, mode)
    if env = "QA" then 
        command = "cmd.exe /k cd " & filePath 
        for m = 1 to noOfBatRun
            command = command & " & " & batName
        next 
        WScript.Echo "  " & "No.of Batch files to run: " & noOfBatRun
        command = command & " & EXIT"
        objShell.Run command,1,mode
        WScript.Sleep 2000
    end if 
End Function

Function generateTestData(loopCounter)
    for l = 1 to loopCounter
        WScript.Echo "  " & "Test File No.: " & l
        objExcel.Run "Sheet1.CommandButton1_Click"
        WScript.Sleep 2000
    next
End Function

So basically I'm passing three values and generate test data for it.
After I click generatetestdata, the mesage box appears saying that completed.

 

[nemmi69]

just a note the Case -1,1 checks for -1, time out and 1, OK was pressed