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: 18 January 2017, 6:33 PM EST
Location: Chicago
Posts: 1,004
Thanks: 561
Thanked 11 Times in 11 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 01: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 05:16 PM
Create a .sh file for an equivalent Excel VBA code bansalpankaj88 Shell Programming and Scripting 7 02-17-2012 07:56 AM
perl script to update a xml file amvarma77 Shell Programming and Scripting 3 04-12-2011 09:49 AM
Run perl script with multiple file arguments ad23 Shell Programming and Scripting 4 07-17-2010 07:55 PM
how to let the perl script to run for each file natalie23 Shell Programming and Scripting 1 10-09-2009 02:21 AM



All times are GMT -4. The time now is 02:23 AM.