Excel and VB

[Nihita Goel]

Hi,
I need to call Excel Application from my VB Code, Update it, then wait for the user to close it. This is how I have started my application, and written to the sheet.
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet


Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open ("d:logidule\cap_road.xls")
Set xlSheet = xlBook.Worksheets(1)


xlSheet.Cells(5, 4) = "S05" 'Write to cell
xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True

xlApp.DisplayAlerts = False

' Here I want to wait for the user to close Excel 'Application

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

I want to wait for the user to Close Excel Application ( as given in comments ). How do I accomplish it?

[leary]

why not put your closing statements in a command button? That way it will closeat the users choosing. if i'm clear on what you are asking.

[Sinu]

Hi Nihita,

Have the following code in the Form Unload Event.

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing



Also, this code would work only when the user closes the Excel Sheet after the VB application is closed.

When u make changes to the Excel Sheet, save it, close the Excel sheet, and then close the VB Application u will get an error.

Regards,
Sinu

[Nihita Goel]

Thanks for the reply.
But what I am looking for is a way to stop the user to access the VB Form before closing the Excel Application. Basically a code in VB which waits for a particular application to stop before going further...
Would be great if you could help in this
Thanks
Nihita

[Iouri]

Try to use windows script host to wait when the program finished execution

Private Sub Form_Load()

Dim wshShell
Set wshShell = CreateObject("WScript.Shell")

MsgBox "About To Launch The Windows Calculator"

wshShell.run "c:\windows\calc.exe", 1, True

MsgBox "Windows Calculator Has Run Successfully"

End Sub

'-----------------------------------------------
The calling syntax for the Windows Scripting Host .Run method is as follow:

Dim wshShell
Set wshShell = CreateObject("WScript.Shell")
wshShell.Run ProgramFilePath, WindowType, WaitForProgramToEnd

Where:

ProgramFilePath:
The file path and program you want to execute. Eg. c:\windows\calc.exe

ScreenType:
The type of window to use when the program is loaded as per this list of constants:
Const SW_HIDE = 0
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWMAXIMIZED = 3
Const SW_SHOWNOACTIVE = 4
Const SW_SHOW = 5
Const SW_MINIMIZE = 6
Const SW_SHOWMINNOACTIVE = 7
Const SW_SHOWNA = 8
Const SW_RESTORE = 9

WaitForProgramToEnd (True/False):

False (default) = Start the program and then immediately return back to the calling statement so that the
calling statement's program can continue running. (ie. the other program runs at the same time as
the Calling program does)

True = Start the program and then WAIT until it is done BEFORE returning to the calling statement's program.
(ie. Single Thread)





Iouri Boutchkine
[email protected]