2

I have created a vba script which creates two files. A .vbs file is used to open an external program and send a keystroke. This program is called via a .bat file. The external program (Mathcad) opens, but it won't carry out the later operations (sendkeys). How could I debug the .vbs file unless anyone can spot the error? 

Public Sub Execute_Mathcad()

Dim ExportPath As String
Dim iFileNum As Long
Dim iFileNumvbs As Long
Dim wsActive As Worksheet
Dim sTempFileName As String
Dim sTempFileNamevbs As String

Set wsActive = ActiveSheet

ThisWorkbook.Save

'Create batch file
With wsActive

    'create .vbs file for keystrokes (called in .bat file)
    ExportPath = "C:\Temp\"
    sTempFileNamevbs = ExportPath & Trim(.Name) & ".vbs"
    iFileNumvbs = FreeFile
    Open sTempFileNamevbs For Output As #iFileNumvbs
    Print #iFileNumvbs, "Set WshShell = WScript.CreateObject(" & Chr(34) & "WScript.Shell" & Chr(34) & ")"
    Print #iFileNumvbs, "WshShell.Run(" & Chr(34) & "C:\Users\blah.xmcd" & Chr(34) & ")"
    Print #iFileNumvbs, "WScript.Sleep 10000"
    Print #iFileNumvbs, "WshShell.AppActivate"; Spc(1); "WshShell"
    Print #iFileNumvbs, "WScript.Sleep 5000"
    Print #iFileNumvbs, "WshShell.SendKeys"; Spc(1); """^{F9}"""

    'create .bat file
    sTempFileName = ExportPath & Trim(.Name) & ".bat"
    iFileNum = FreeFile
    Open sTempFileName For Output As #iFileNum
    Print #iFileNum, "@Echo off"
    Print #iFileNum, "wscript"; Spc(1); """C:\Temp\Results.vbs"""
End With

Close #iFileNum
Close #iFileNumvbs

'run batch file
retVal = Shell(sTempFileName, vbHide)

'this returns an error if sTempFileName is incorrect
If retVal = 0 Then
    MsgBox "An Error Occured"
    Close #FileNumber
    End
End If

'exit Excel using VBA command
Application.Quit

End Sub
Pascal Galloway
  • 39
  • 1
  • 4
  • Can you post the BAT file it creates too? – Mark Setchell Sep 01 '14 at 14:00
  • Hi Mark, it is included in the above script unless I have misunderstood?`Print #iFileNum, "@Echo off" Print #iFileNum, "wscript"; Spc(1); """C:\Temp\Results.vbs"""`. The resulting batch file code looks like this `@Echo off wscript "C:\Temp\Results.vbs"` – Pascal Galloway Sep 01 '14 at 14:05
  • You have posted the code that creates the BAT, not the BAT itself. – Mark Setchell Sep 01 '14 at 14:07
  • In addition the .vbs file looks like this: `Set WshShell = WScript.CreateObject("WScript.Shell") WshShell.Run("C:\Users\blah.xmcd") WScript.Sleep 10000 WshShell.AppActivate "Mathcad" WScript.Sleep 5000 WshShell.SendKeys "^{F9}" ` – Pascal Galloway Sep 01 '14 at 14:11
  • What happens if you run the VBS from the Command Prompt, using `CSCRIPT /NOLOGO C:\Temp\Results.vbs` – Mark Setchell Sep 01 '14 at 14:28
  • when you run this is your "MathCad" being activated? also for such blind automations i've used "autoIt" fairly simple coding like vba and really nice tool for automations – Krish Sep 01 '14 at 14:40
  • @MarkSetchell Hi Mark. I'm sure this problem is either related to sendkeys or appactivate. I tried running from the command line and I get the same problem. – Pascal Galloway Sep 01 '14 at 14:53
  • @krishKM Hi Krish, how do I check if the program has been activated? – Pascal Galloway Sep 01 '14 at 14:57
  • @PascalGalloway: if MathCad is opened and activated it will get the focus and be in front of all other applications. your send key method will only then received when MathCad has the focus. – Krish Sep 01 '14 at 15:08
  • @krishKM Is there not more to it? I mean when Mathcad opens it has the focus as you put it (it is in front of the other windows), however I don't think it is activated e.g. If I open the program manually and issue the command CTRL+F9 it works. When I run the .bat file and Mathcad opens, even when manually inputting CTRL+F9, nothing happens. Presumably this means my problem is with the line `WshShell.AppActivate "Mathcad"`. I have tried changing the title, can I use the ProcessID, if yes where do I find this? – Pascal Galloway Sep 01 '14 at 15:22
  • @PascalGalloway: check this: http://stackoverflow.com/questions/8296037/find-my-own-process-id-in-vbscript – Krish Sep 01 '14 at 15:33
  • Code corrected. It is better to use the processID that the title, less prone to error! – Pascal Galloway Sep 01 '14 at 19:32
  • Maybe better to try to achieve what you want using Mathcad Object Model, something like `Set MathCAD = CreateObject("Mathcad.Application") ... `? And `.SendKeys` is not the best practice 'cause it's too unreliable. – omegastripes Sep 01 '14 at 19:39
  • I'm so confused. Why are you creating a VBS file, then a BAT file to call the VBS file, all to do something that you can just do in VBA to begin with? VBScript is a subset of VBA. Why not just do your `SendKeys` calls from your VBA macro? – Bond Sep 02 '14 at 00:11
  • @bond because Excel needs to exit completely prior to Mathcad openning (the problem is with Mathcad not Excel). I then want to re-open Excel and issue a further Macro. Either way this now works fine, just need to make it more robust with the filenames. – Pascal Galloway Sep 02 '14 at 08:13
  • I've seen some dodgy stuff in my time but this is about the most horrific thing I've ever seen. You name the script after the Active Sheet, so maybe something like `c:\temp\sheet 1.vbs`, but then hard code `c:\temp\Results.vbs`. I take it this isn't the problem though? What is the actual problem you're trying to solve? Maybe there is a better way? – Jobbo Sep 09 '14 at 13:24

1 Answers1

-1

Code corrected and now works. Thanks for everyone's input. I'm sure there is a neater solution but this does the job!

Pascal Galloway
  • 39
  • 1
  • 4