0

I am trying to fetch CR(change request) status from PTC(MKS) using "im exportissues 123456" query in VBA. but it does open status sheet(A new excel sheet which opens up by query) unless my code ends. below is the snippet of my code.

Sub Query_CR_Status()

    shell_output = Shell("im exportissues 123456", 1)     'this should open up a new excel sheet containing CRs)

    Application.Wait (Now + TimeValue("0:00:20"))         'waiting 20sec

   'here: My code which will read information from the above generated excel sheet
End Sub

But My problem here is the new excel sheet donot open unless it reaches to "End Sub"

Community
  • 1
  • 1
bikash
  • 1
  • 2
  • get rid of the `Application.Wait` then... – SierraOscar May 27 '16 at 09:30
  • yes I have removed the delay. But I don't know what is the issue.. Sub Query_CR_Status() shell_output = Shell("im exportissues 123456", 1) 'this should open up a new excel sheet containing CRs) 'here: My code which will read information from the above generated excel sheet End Sub – bikash May 27 '16 at 11:22
  • But the delay is necessary as the process takes time. – bikash May 27 '16 at 12:17
  • see my answer below, that's how you should try and delay an application. Using the wait method is unreliable. – SierraOscar May 27 '16 at 12:34

1 Answers1

0

Application.Wait() will prevent the application from doing anything for the specified amount of time. So if you shell a command that opens a workbook, but the application is in wait mode then it will be delayed.

You can try and get around this by using something like:

Sub Query_CR_Status()

Dim wbC As byte
wbC = Workbooks.Count

    shell_output = Shell("im exportissues 123456", 1)

    While Workbooks.Count = wbC
        DoEvents '// Process pending messages until workbook count changes
    Wend

   '// Rest of code

End Sub

Alternatively, Chip Pearson created a ShellAndWait function that might be better suited to this.

Community
  • 1
  • 1
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • Thanks for the reply, I have already implemented the explanation you have given. Regarding Chip Pearson ShellAndWait function, I tried to implement it but when I use ShellAndWait() function in my code, it is unable to recognize the function as it is in other module. – bikash May 27 '16 at 13:18
  • above solution did not help. – bikash May 29 '16 at 06:37
  • Why? What exactly didn't work? Did you try using the `DoEvents` code I posted? – SierraOscar May 29 '16 at 06:38
  • Hello Macroman, Yes, I tried the above posted code. But the expected workbook do not open till I reach End Sub or I put a breakpoint just before End Sub. – bikash May 30 '16 at 04:32