CODEWITHSUNDEEP
vbaperl
justaguy
justaguy

Reputation: 3022

Invoking PERL script via Shell from VBA

I am hoping that the VB below will run a perl script on a directory that changes based on user input. The issue I am having is I am a syntax error when calling the perl script (in bold). Is this not the right way to call a perl from VB? Thank you :).

VB

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" & vbTab & "SpikeIn Location" & vbTab & "Barcode"
    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 & vbTab & ActiveSheet.Range("B20").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 & vbTab & ActiveSheet.Range("B20").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 & vbTab & ActiveSheet.Range("B20").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 & vbTab & ActiveSheet.Range("B20").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 perl
    Function Chk()
    Dim PathCrnt As String
    Dim RetVal

    PathCrnt = ActiveWorkbook.Path & "\" & MyBarCode & "_" & MyScan
       Chk = Shell("C:\cygwin\bin\perl.exe C:\cygwin\home\cmccabe\get_imagene_spikein_probe_values.pl", 1)
       End Function
    End If**
Else
    MsgBox "Nothing has been done. ", vbExclamation, "Goodbye!"
End If

Application.DisplayAlerts = False
Application.Quit

End Function

Upvotes: 0

Views: 309

Answers (1)

David W
David W

Reputation: 10184

Based on feedback from OP, the "Shell" statement should be adquate for invoking an external procedure.

The issue is with the nested function. Move 'Function Chk()' such that it is at the same level in the source as any other function or sub. As declared, this is a nested function, which is not supported in VBA (or any other VB I'm aware of).

Upvotes: 1

Related Questions