Update sh file using VBA before perl script is run


 
Thread Tools Search this Thread
Special Forums Windows & DOS: Issues & Discussions Update sh file using VBA before perl script is run
# 1  
Old 08-24-2015
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 Smilie.

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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script run in a case statement call to run a php file, also Perl

Linux System having all Perl, Python, PHP (and Ruby) installed From a Shell script, can call a Perl, Python, PHP (or Ruby ?) file eg eg a Shell script run in a case statement call to run a php file, also Perl or/and Python file??? Like #!/usr/bin/bash .... .... case $INPUT_STRING... (1 Reply)
Discussion started by: hoyanet
1 Replies

2. Windows & DOS: Issues & Discussions

Excel VBA script aware of being opened interatively or from the command line

I have a Excel VBA script that automatically runs when opened, extracts one table from a Microsoft Access Database, saves it as CSV, closes it, extracts another, saves it as CSV, closes it, then terminates itself. Is is called from a short .bat file using the /e option so that Excel does not... (1 Reply)
Discussion started by: Michael Stora
1 Replies

3. Shell Programming and Scripting

How to write a update query in perl script?

can any one suggest me on how to write a update query in perl script for Oracle database and also tell me abt how we can write a code for sending mails with report as attachment to appropriate persons? (1 Reply)
Discussion started by: Ramesh V Kumar
1 Replies

4. Shell Programming and Scripting

Create a .sh file for an equivalent Excel VBA code

Hi guys, I am new to Unix, Need your help here. I have installed cygwin software (Unix) in my computer (Windows vista). Now i want to create a shell script (.sh file)/other script which is equivalent of VBA code (at the bottom) and then put this .sh file into bin directory of c:/cygwin. so... (7 Replies)
Discussion started by: bansalpankaj88
7 Replies

5. Shell Programming and Scripting

perl script to update a xml file

Hi experts, I have a set of xml files in folder which has the below field. <mm:sessionID>157.235.206.12900397BE4:A</mm:sessionID>, I need to update this field regularly with new session id, which I have it from a login file. Can anyone tell me how to add a new value in <mm:sessionID>... (3 Replies)
Discussion started by: amvarma77
3 Replies

6. Shell Programming and Scripting

Run perl script with multiple file arguments

Hello everyone, I have two types of files in a directory: *.txt *.info I have a perl script that uses these two files as arguments, and produces a result file: perl myScript.pl abc.txt abc.xml How can I run this script (in a "for" loop , looping through both types of files)... (4 Replies)
Discussion started by: ad23
4 Replies

7. Shell Programming and Scripting

perl script to check if empty files are created and delete them and run a shell script

I have a local linux machine in which the files are dumped by a remote ubuntu server. If the process in remote server has any problem then empty files are created in local machine. Is there any way using perl script to check if the empty files are being created and delete them and then run a shell... (2 Replies)
Discussion started by: hussa1n
2 Replies

8. Shell Programming and Scripting

how to let the perl script to run for each file

Hi, I have more than 1 files in the directory. In bash, I can use cd /work for x in `ls` do : : done to run for each file in the directory. How about in perl script? filepath="ABC1" open(FILE, $filepath) or die "$filepath cannot be opened."; while(<FILE>) { : (1 Reply)
Discussion started by: natalie23
1 Replies

9. Linux

perl update while script is loaded

Hi All, What will happen to the perl script loaded on the memory if I do perl update? Thanks. (1 Reply)
Discussion started by: itik
1 Replies

10. Shell Programming and Scripting

Perl: Run perl script in the current process

I have a question regarding running perl in the current process. I shall demonstrate with an example. Look at this. sh-2.05b$ pwd /tmp sh-2.05b$ cat test.sh #! /bin/sh cd /etc sh-2.05b$ ./test.sh sh-2.05b$ pwd /tmp sh-2.05b$ . ./test.sh sh-2.05b$ pwd /etc sh-2.05b$ So... (10 Replies)
Discussion started by: vino
10 Replies
Login or Register to Ask a Question