Unix/Linux Go Back    


Windows & DOS: Issues & Discussions All Windows and DOS questions should go here as well. Discuss UNIX to Windows (Desktop or Server) here!

Update sh file using VBA before perl script is run

Windows & DOS: Issues & Discussions


Tags
vba perl batch file shell

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 08-24-2015
cmccabe cmccabe is offline
Registered User
 
Join Date: Nov 2013
Last Activity: 23 June 2017, 9:42 AM EDT
Location: Chicago
Posts: 1,141
Thanks: 681
Thanked 14 Times in 13 Posts
Update sh file using VBA before perl script is run

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. When the batch file is called I get the error below and not sure how to fix it. I understand the first part of the error, but the directory it uses is the batch, so I am confused. I apologize for the long post, just wanted to be complete. Thank you Linux.

Error:

Code:
'\\path\users\C\CMcCabe'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.
'C:\Users\cmccabe\Desktop\NxClinical.bat' is not recognized as an internal or ex
ternal command,
operable program or batch file.

VBA


Code:
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
        Dim PathCrnt As String
        Dim wsh As Object

        PathCrnt = MyDirectory
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1
        Dim errrCode As Long

        errrCode = wsh.Run("cmd /K ""C:\Users\cmccabe\Desktop\NxClinical.bat""" _
        , windowStyle, waitOnReturn)

        If errrCode = 0 Then
           MsgBox "Done! No error to report."
        Else
           MsgBox "Program exited with error code " & errrCode & "."
        End If

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

Bat that calls perl script


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

Run_probes.sh (the underlined portion of the could is what changes)


Code:
perl ~/get_imagene_spikein_probe_values.pl  .   ./sample_descriptor.txt  <  ~/test_probes8.txt  >  ./output.txt


Last edited by cmccabe; 08-29-2015 at 12:36 PM.. Reason: update path in bold, modified code to include path
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Excel VBA script aware of being opened interatively or from the command line Michael Stora Windows & DOS: Issues & Discussions 1 11-29-2013 04:16 PM
Create a .sh file for an equivalent Excel VBA code bansalpankaj88 Shell Programming and Scripting 7 02-17-2012 06:56 AM
perl script to update a xml file amvarma77 Shell Programming and Scripting 3 04-12-2011 08:49 AM
Run perl script with multiple file arguments ad23 Shell Programming and Scripting 4 07-17-2010 06:55 PM
how to let the perl script to run for each file natalie23 Shell Programming and Scripting 1 10-09-2009 01:21 AM



All times are GMT -4. The time now is 03:07 AM.