Results 1 to 2 of 2

Call perl from VB

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Call perl from VB

    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. 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 Sub
    Last edited by cmccabe; 08-25-2015 at 04:29 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. call shell from VBA
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2014, 03:52 PM
  2. [SOLVED] Shell function gives File not Found error
    By jewelsharma in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-04-2014, 09:38 PM
  3. Call Shell - return code?
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 09:18 AM
  4. File Not Found error when opening User Form - need help tracing root of error
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2013, 03:48 PM
  5. Problem using shell to call batch file or cmd prompt
    By nickf829 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2011, 10:27 PM
  6. CALL SHELL doesn't recognise the directory
    By plancast in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2009, 12:00 PM
  7. Shell Invalid Procedure call or argument
    By rgans in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-05-2005, 12:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1