1

I found the following article that sort of fixed "powershell" execution to process waiting...

Determine when a shelled process ends in VBA

But i also launch macros that have only VBA code, and i would like to use the ExecCMD to wait for VBA code to finish as well, or place it in a process which wait for and only continue once it is finished.

Is it possible to run a VBA macro in Excel, as the process?

I have a "RUN-MACRO" that execute several sections.

For instance first section is to collect login information to authenticate as admin:

Call RUN_LOGIN_SAVE_TO_FILE

It is then able to put the called command into a job as this, and it will wait for user to input info, and then process that info, before it moves on..:

Sub RUN_LOGIN_SAVE_TO_FILE()

ExecCmd "powershell.exe H:\Temp\PublicFolder-powershell\Login.ps1"

End Sub

So all this works well, as long as i call an "external" function to run, like powershell or another program.

But the next section of the script is a macro running a VBA code:

Call BID_1_TextOut

Which is as follows:

Sub BID_1_TextOut()
Dim fso As New FileSystemObject
Dim Stream As TextStream
Dim RowNum As Long

RowNum = 1

' Create a TextStream.
Set Stream = fso.CreateTextFile(Split(Application.ActiveWorkbook.FullName, ".")(0) & "_Merge1.txt", True)

Application.Goto (ActiveWorkbook.Sheets("Merge1").Range("A1"))

'write columns A-E into a file for each line stopping when column A is empty
Do Until IsEmpty(Range("A" & RowNum)) = True
    Stream.WriteLine Range("A" & RowNum).Value & Range("B" & RowNum).Value & _
        Range("C" & RowNum).Value & Range("D" & RowNum).Value & Range("E" & RowNum).Value & _
        Range("F" & RowNum).Value & Range("G" & RowNum).Value & Range("H" & RowNum).Value & _
        Range("I" & RowNum).Value & Range("J" & RowNum).Value & Range("K" & RowNum).Value & _
        Range("L" & RowNum).Value & Range("M" & RowNum).Value & Range("N" & RowNum).Value & _
        Range("O" & RowNum).Value & Range("P" & RowNum).Value & Range("Q" & RowNum).Value & _
        Range("R" & RowNum).Value & Range("S" & RowNum).Value & Range("T" & RowNum).Value & _
        Range("U" & RowNum).Value & Range("V" & RowNum).Value & Range("W" & RowNum).Value & _
        Range("X" & RowNum).Value & Range("Y" & RowNum).Value & Range("Z" & RowNum).Value & _
        Range("AA" & RowNum).Value & Range("AB" & RowNum).Value & Range("AC" & RowNum).Value & _
        Range("AD" & RowNum).Value & Range("AE" & RowNum).Value & Range("AF" & RowNum).Value & _
        Range("AG" & RowNum).Value & Range("AH" & RowNum).Value & Range("AI" & RowNum).Value & _
        Range("AJ" & RowNum).Value & Range("AK" & RowNum).Value & Range("AL" & RowNum).Value & _
        Range("AM" & RowNum).Value & Range("AN" & RowNum).Value & Range("AO" & RowNum).Value & _
        Range("AP" & RowNum).Value & Range("AQ" & RowNum).Value & Range("AR" & RowNum).Value & _
        Range("AS" & RowNum).Value & Range("AT" & RowNum).Value & Range("AU" & RowNum).Value & _
        Range("AV" & RowNum).Value & Range("AW" & RowNum).Value & Range("AX" & RowNum).Value & _
        Range("AY" & RowNum).Value & Range("AZ" & RowNum).Value & Range("BA" & RowNum).Value & _
        Range("BB" & RowNum).Value & Range("BC" & RowNum).Value & Range("BD" & RowNum).Value & _
        Range("BE" & RowNum).Value & Range("BF" & RowNum).Value & Range("BG" & RowNum).Value & _
        Range("BH" & RowNum).Value & Range("BI" & RowNum).Value & Range("BJ" & RowNum).Value & _
        Range("BK" & RowNum).Value & Range("BL" & RowNum).Value & Range("BM" & RowNum).Value & _
        Range("BN" & RowNum).Value & Range("BO" & RowNum).Value & Range("BP" & RowNum).Value & _
        Range("BQ" & RowNum).Value & Range("BR" & RowNum).Value

    RowNum = RowNum + 1
Loop

'close stream
Stream.Close

End Sub

How can i use ExecCMD to run this macro containing VBA code as a process/job as well, where it wait for it to finish before continue?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Boof
  • 45
  • 6
  • If you run `Call BID_1_TextOut` from VBA then execution of any subsequent lines will wait for that call to complete. It's not clear what the problem is here. – Tim Williams Sep 29 '21 at 18:49
  • FYI you could replace that long concatenation with `Stream.WriteLine Join(application.transpose(application.transpose(Cells(RowNum,"A").resize(1,70).value)),"")` – Tim Williams Sep 29 '21 at 18:55
  • Thanks for the suggestion and help. I have 6 such scripts as the last one above running after eachother, I have found that sometimes they are "too fast" and have to use a pause for X number of seconds to make sure txt files are finished writing to disk before moving to the next part. `Application.Wait Now + TimeSerial(0, 0, 1)` ...the next lines are sometimes using the text files that was created previously and the script fails if the files are not finished writing to disk. The script just continue when disk write command is complete, it does not wait for any confirmation... – Boof Sep 30 '21 at 05:04
  • hmm..just realized something...the second the write command has been issued from the script, it has already completed it's job, and there is no confirmation back. Maybe what i need is a function to verify a file has been written to disk, before it moves on. :-O – Boof Sep 30 '21 at 05:11
  • So..this fixed it. Thnx for the tip Tim. – Boof Sep 30 '21 at 05:34
  • Added to end of code: `Do If fso.FileExists("H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch_Merge1.txt") Then Exit Do End If DoEvents 'Prevents Excel from being unresponsive Application.Wait Now + TimeValue("0:00:01") 'wait for one second Loop` – Boof Sep 30 '21 at 05:35

0 Answers0