update sh file using VBA before perl script is run

Question

I have a VBA that creates a new directory folder and creates a new text file in that directory. I am trying to run a perl script from the VBA and have created a batch file that gets called from the VBA. That bat file uses a shell file to run a script. The directory in the script is dynamic and changes each time based on user input. My question is can the .sh file be updated before it is run? I apologize for the long post, just wanted to be complete. Thank you :).

VBA

Private Sub CommandButton3_Click()

Dim MyBarCode   As String      ' Enter Barcode
Dim MyScan      As String      ' Enter ScanDate
Dim MyDirectory As String

MyBarCode = Application.InputBox("Please enter the barcode", "Bar Code", Type:=2)
If MyBarCode = "False" Then Exit Sub   'user canceled
Do
    MyScan = Application.InputBox("Please enter scan date", "Scan Date", Date, Type:=2)
    If MyScan = "False" Then Exit Sub   'user canceled
    If IsDate(MyScan) Then Exit Do
    MsgBox "Please enter a valid date format. ", vbExclamation, "Invalid Date Entry"
Loop

Range("B20").Value = MyBarCode
Range("B21").Value = CDate(MyScan)

MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
' Create nexus directory and folder
If Dir(MyDirectory, vbDirectory) = "" Then MkDir MyDirectory

If MsgBox("The project file has been created. " & _
          "Do you want to create a template for analysis now?", _
          vbQuestion + vbYesNo) = vbYes Then

    'Write to text file
    Open MyDirectory & "sample_descriptor.txt" For Output As #1
    Print #1, "Experiment Sample" & vbTab & "Control Sample" & vbTab & "Display Name" & vbTab & "Gender" & vbTab & "Control Gender" & vbTab & "SpikeIn Location"
    Print #1, MyBarCode & "_532Block1.txt" & vbTab & MyBarCode & "_635Block1.txt" & vbTab & ActiveSheet.Range("B8").Value & " " & ActiveSheet.Range("B9").Value & vbTab & ActiveSheet.Range("B10").Value & vbTab & ActiveSheet.Range("B5").Value & vbTab & ActiveSheet.Range("B11").Value & vbTab & ActiveSheet.Range("B12").Value
    Print #1, MyBarCode & "_532Block2.txt" & vbTab & MyBarCode & "_635Block2.txt" & vbTab & ActiveSheet.Range("C8").Value & " " & ActiveSheet.Range("C9").Value & vbTab & ActiveSheet.Range("C10").Value & vbTab & ActiveSheet.Range("C5").Value & vbTab & ActiveSheet.Range("C11").Value & vbTab & ActiveSheet.Range("C12").Value
    Print #1, MyBarCode & "_532Block3.txt" & vbTab & MyBarCode & "_635Block3.txt" & vbTab & ActiveSheet.Range("D8").Value & " " & ActiveSheet.Range("D9").Value & vbTab & ActiveSheet.Range("D10").Value & vbTab & ActiveSheet.Range("D5").Value & vbTab & ActiveSheet.Range("D11").Value & vbTab & ActiveSheet.Range("D12").Value
    Print #1, MyBarCode & "_532Block4.txt" & vbTab & MyBarCode & "_635Block4.txt" & vbTab & ActiveSheet.Range("E8").Value & " " & ActiveSheet.Range("E9").Value & vbTab & ActiveSheet.Range("E10").Value & vbTab & ActiveSheet.Range("E5").Value & vbTab & ActiveSheet.Range("E11").Value & vbTab & ActiveSheet.Range("E12").Value
    Close #1

    'Run ImaGene
    If MsgBox("Please run the ImaGene analysis. " & _
          "and click yes after it completes to verify the spike-ins.", _
          vbQuestion + vbYesNo) = vbYes Then

    **'Update folder structure and call .bat
    Dim PathCrnt As String
    Dim FN As Long

    FN = FreeFile 'FreeFile gets an available file number'
    Open "C:\cygwin\home\cmccabe\Run_probes.sh" For Output As FN

    PathCrnt = ActiveWorkbook.Path
    *system.diagnostics.process.Start ("C:\Users\cmccabe\Desktop\NxClinical.bat") & PathCrnt*
    Close FN
    End If**
End Sub

Else
    MsgBox "Nothing has been done. ", vbExclamation, "Goodbye!"
End If
Application.DisplayAlerts = False
Application.Quit

End Sub

Bat with that calls perl script:

C:\cygwin\bin\bash --login -i ./Run_probes.sh

Run_probes.sh

perl "C:\cygwin\home\cmccabe\get_imagene_spikein_probe_values.pl" "N:\1_DATA\MicroArray\NexusData\*257168310045_8-18-2015*" "ImaGene EmArray- Template.txt" < test_probes8.txt > "N:\1_DATA\MicroArray\NexusData\*257168310045_8-18-2015*\output.txt"

Why call bash at all? You can invoke Perl instead, right?

reinierpost
– reinierpost

2015-08-24 18:25:29 +00:00
Commented Aug 24, 2015 at 18:25 — reinierpost
– reinierpost, Commented Aug 24, 2015 at 18:25

Tim · Accepted Answer · 2015-08-24 14:38:35Z

2

Nice post! Very detailed. :)

Certainly the Perl file can be updated before it is run. Just write the update path into the file like you write the data into sample_descriptor.txt. eg:

Dim FN as Long
FN = FreeFile 'FreeFile gets an available file number'
Open PathToShFile For Output As FN
Print FN, "perl " & chr(34) & PathCrnt & _
  & chr(34) & "\get_imagene_spikein_probe_values.pl" & chr(34) &  _
  & chr(34) & "N:\1_DATA\MicroArray\NexusData\*257168310045_8-18-2015*" & _
  chr(34) & " " & chr(34) & _
  " ImaGene EmArray- Template.txt" & chr(34) & _ 
  " < test_probes8.txt > " & chr(34) & _
  "N:\1_DATA\MicroArray\NexusData\*257168310045_8-18-2015*\output.txt"  & chr(34)
Close FN

I'm not sure I got all the " marks replaced with chr(34) correctly so make sure to echo out that string before trying to execute any code. You might also make it more readable (and configurable) by using variables to store paths. eg:

strNPath = "N:\1_DATA\MicroArray\NexusData\*257168310045_8-18-2015*\"

answered Aug 24, 2015 at 14:38

Tim

2,9123 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

justaguy Over a year ago

Thank you very much for your help and I am glad the details helped, it is much appreciated :)

justaguy Over a year ago

I thought I understood, but I edited the code and not sure it looks right. Thank you :).

Tim Over a year ago

For clarity, try assigning all your paths to variables (like I did with strNPath). Then find the path you want changed and see if it is correct. If not and if you can't figure out how to get it corrected, post the value of that variable and what it should be (generally, since its dynamic).

Tim Over a year ago

Also, if you can optionally use single quotes ' instead of double quotes " in your bi long perl line, use them Its more readable than all my chr(34) changes and less confusing than trying to use 2 or 3 double quotes to escape. (its been a few years since I did any Perl coding so I don't remember...sorry)

justaguy Over a year ago

I made the change in the 'Update folder structure and call .bat section. I am getting a bad file name with the strNPath highlighted. Thank you:).

|

Collectives™ on Stack Overflow

update sh file using VBA before perl script is run

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related