0

I have an excel file titled "MBGR". This is my main file. Now, through this file, i executed a batch file and as a result, some data is extracted from a database application and gets pasted in a newly generated excel file titled "Volla".

So, now i have two workbooks open at the same time. Now, i want to copy this newly generated data back to my original workbook "MGBR". The problem is that the database extraction takes like 5 seconds and VBA code is moving forward. It commands excel to:

  • Activate the newly generated workbook "Volla"

  • Copy the data

  • Paste back to originial workbook "MGBR"

The data base extraction is still in process when excel recives the command to activate the "Volla" sheet which havent been generated yet, so the code crashes. Obviously, if i break the two processes in two indendent modules, it works fine but how can i add a pause so that the "Volla" sheet gets generated and then the copying command starts. Pause or Sleep does not work as it halts all excel activtiies. For your review, i am attaching the code

Sub Routine()

batch = Sheets("input").Range("p3") & "\" & "Script.bat"


    Open batch For Output As #1
    Close #1

    Open batch For Output As #1
    Print #1, """" & "C:\Program Files (x86)\HEC\HEC-DSSVue\HEC-DSSVue.exe" & """" & " " & """" & path & """"
    Close #1

''Executing batch file - The batch file gets executed here and the New excel _

sheet will be generated

retval = Shell(batch, vbNormalFocus)

Windows("volla.xls").Activate '' This command gives error as workbook "Volla" 
                               '  generation was still under process

ActiveSheet.Range("a6:z1000").Copy

Windows("MGBR").Activate

With Sheets("test")

.Range("a1").PasteSpecial Paste:=xlPasteValues

End With

Application.DisplayAlerts = False

Windows("volla.xls").Close

Any help????

Community
  • 1
  • 1
Xain Cheema
  • 53
  • 2
  • 4
  • You can wait for the command to finish: http://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete – Tim Williams Oct 27 '16 at 06:20
  • I checked the attached page and used the Wscript.shell function but it didnt work. Rather it gives me an error: Run-time error '2147024894 - Method Run of object IWshShell3 failed – Xain Cheema Oct 27 '16 at 06:37

1 Answers1

0

Suggest to split the procedure as follows:

  1. Run batch file to generate data (excel file), with estimated time of 5 secs. (Routine)
  2. Run copy paste of data in new File (volla.xls) into main file, etc. (Routine_Cont)

In the first procedure, schedule the second procedure to start after the expected time it takes the batch process to complete +2 secs as margin for error. 

Sub Routine()
    batch = Sheets("input").Range("p3") & "\" & "Script.bat"
    Open batch For Output As #1
    Close #1

    Open batch For Output As #1
    Print #1, """" & "C:\Program Files (x86)\HEC\HEC-DSSVue\HEC-DSSVue.exe" & """" & " " & """" & Path & """"
    Close #1

    ''Executing batch file - The batch file gets executed here and the New excel _
        sheet will be generated
    retval = Shell(batch, vbNormalFocus)

    Rem Schedule the 2nd part of this procedure to start in 5 seconds
    Application.OnTime _
        EarliestTime:=Now + TimeSerial(0, 0, 5 + 2), _
        Procedure:="Routine_Cont", Schedule:=True

    End Sub


Sub Routine_Cont()
    Windows("volla.xls").Activate
    ActiveSheet.Range("a6:z1000").Copy
    Windows("MGBR").Activate
    Sheets("test").Range("a1").PasteSpecial Paste:=xlPasteValues
    Application.DisplayAlerts = False
    Windows("volla.xls").Close
    End Sub
EEM
  • 6,601
  • 2
  • 18
  • 33