Opening VB program from Excel macro

[dubble07]

Can this be done?

I have an existing VB 'parser' program that publishes a whole series of reports from one format (ppr) to another (html). When a user clicks on a button on my Excel macro i want it to fire off this VB program. I've heard (probable erroneously?) that you cant call a VB program from Excel (VBA) - or that your limited to VBA. It would be really nice for my users if this program could be fired off for them transparently, rather than they having to manually go and open VB and then the program.

Does anyone have anything on this??

cheers in advance.

 

[uberpudge]

Try this:

Sub Test()
'Assign me to a Forms button, or use an Active X button which calls me.
Dim varAppToLaunch As Variant
Dim dblProgramTaskID As Double

'Changing path to root of local windows directory for convenience.
FileSystem.ChDir "C:\Winnt\"

'Calling the GetOpenFileName method returns either a Boolean "False" (when
'the user cancels the dialog box) or a String consisting of the path and
'file name. I am limiting this Open Dialog Box to filter out everything
'that is not an application.
'You could simply specify the path and name of the application you want to
'run instead of making an Open Dialog Box call.
varAppToLaunch = Application.GetOpenFilename("Program Files (*.exe), *.exe&quot

'If a string was returned, then we have a valid file name.
If Information.VarType(varAppToLaunch) = VbVarType.vbString Then
'Launch selected application in a normal window and store the program's
'Task ID for possible future use.
'You could also use VbAppWinStyle.vbHide. This way, when you launch the
'program, the window is hidden and focus is passed to the hidden window.
dblProgramTaskID = Interaction.Shell(varAppToLaunch, VbAppWinStyle.vbNormalFocus)
Else
'Must be the Boolean "False". Take appropriate action.
Interaction.MsgBox Prompt:="Action was canceled by the user."
End If
End Sub

It worked when I tested it.

Hope this helps,
Pete

 

[dubble07]

thanks, some neat code - the one problem i'm having, though, is a file not found error. I've got my VB project and forms in the right path though i keep getting a 'error 53 - file not found'. . In fact i've tested this with a simple text file as well and this doesn't work either. The help file suggests that i may be in the wrong path (not the case), that there may be a missing dll file or project file. This shouldn't be the problem either as a simple text file cant even launch. strange?!

Did you run against this when you were doing your testing?

Cheers!!

 

[uberpudge]

Unfortunately I did not run across the problem you are referring to.
Are you using the dialog box to find the file or are you specifying the path to the application?
Also, what version of Excel are you running?
Hope this helps,
Pete

 

[dubble07]

Im using excel 97, and have tried both ways of launching the vb app - directly and through the dialog box. your code appears to work, that is - the dialog box pops up no probs, and it filters on the file type your looking for, but when attempting to launch, the 'file not found' error appears (though the file clearly exists).

I can open this same file no probs through Explorer, which makes me wonder if theres a .dll file or something missing ??

If you can think of anything let me know ...

thx for your help all the same.

 

[uberpudge]

Try launching a different application.
Interaction.Shell will only open executable files.
To launch a text file, you would have to use Interaction.Shell to launch an application and pass the text file as a parameter to the application.

Example:
varAppToLaunch = "C:\winnt\notepad.exe " + varAppToLaunch
dblProgramTaskID = Interaction.Shell(varAppToLaunch, VbAppWinStyle.vbNormalFocus)

The same idea applies if you are not opening a compiled VB program. You would have to use:
varAppToLaunch = "C:\Program Files\Visual Basic\vb.exe " + varAppToLaunch
dblProgramTaskID = Interaction.Shell(varAppToLaunch, VbAppWinStyle.vbNormalFocus)

assuming "C:\Program Files\Visual Basic\vb.exe" is the correct path to the Visual Basic/Studio application.

Also, if this is the route you need to go, don't forget to include a space between the app path and the document path so that the called app will recognize the document path as a parameter.
Hope this helps,
Pete

 

[dubble07]

Uberpudge,

I'm all set, thx for your steadfastness!

S

 

[Paco75]

Hi there,

I want to know how to close a program launched this way... since we got the TaskId in dblProgramTaskID we should be able to end the program after it has been running? If someone know about this one I will be happy to know.

thanks

 

[JustinEzequiel]

see thread707-121221