write variable in vba to text file

[cmccabe1]

The below excel 2010 vba runs perfectly (well used to) except I can not seem to get the variable process (time elapsed) to print from the Call Perl section to the analysis.txt, which captures some information regarding the analysis. I tried to use a function (in bold) to accomplish this, but am not using it correct? I hope the below is a good start. Thank you .

VB

Private Sub CommandButton3_Click()
       
    Dim MyBarCode   As String      ' Enter Barcode
    Dim MyScan      As String      ' Enter ScanDate
    Dim MyDirectory As String
    
'GET USER INPUT '
Line1:
    MyBarCode = Application.InputBox("Please enter the last 5 digits of 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 - 1, 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
        
'CREATE NEXUS DIRECTORY AND VERIFY FOLDER '
    MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
    If Dir(MyDirectory, vbDirectory) = "" Then
        MkDir MyDirectory
    Else
        MsgBox ("Already exsists! Please enter again")
        GoTo Line1
    End If


'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 & "Location" & vbTab & "Barcode" & vbTab & "Medical Record" & vbTab & "Date of Birth" & vbTab & "Order Date"
        Print #1, "2571683" & MyBarCode & "_532Block1.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode & vbTab & ActiveSheet.Range("C201").Value & vbTab & ActiveSheet.Range("D201").Value & vbTab & ActiveSheet.Range("E201").Value
        Print #1, "2571683" & MyBarCode & "_532Block2.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode & vbTab & ActiveSheet.Range("C202").Value & vbTab & ActiveSheet.Range("D202").Value & vbTab & ActiveSheet.Range("E202").Value
        Print #1, "2571683" & MyBarCode & "_532Block3.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode & vbTab & ActiveSheet.Range("C203").Value & vbTab & ActiveSheet.Range("D203").Value & vbTab & ActiveSheet.Range("E203").Value
        Print #1, "2571683" & MyBarCode & "_532Block4.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode & vbTab & ActiveSheet.Range("C201").Value & vbTab & ActiveSheet.Range("D204").Value & vbTab & ActiveSheet.Range("E204").Value
        Close #1


'CREATE ANALYSIS LOG '
      
     Open MyDirectory & "analysis.txt" For Output As #2
     Print #2, "Analysis done by" & " " & Environ("UserName") & " " & "on" & " " & Date & " " & "at" & " " & Time & " " & "and took" & ret & "to complete"
     Close #2


'CONFIRM ENTRIES '
Dim Patient As String
Dim Barcode As String
Dim Directory As String
Dim MyFile As Variant
Dim MyFolder As String
Dim i As Long


Directory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")
 
Patient = ActiveSheet.Range("B9").Value & " " & ActiveSheet.Range("C9").Value & " " & ActiveSheet.Range("D9").Value & " " & ActiveSheet.Range("E9").Value
Barcode = "2571683" & MyBarCode
MsgBox "The patients that will be analyzed are:" + Patient & "The barcode is:" + Barcode




     iYesNo = MsgBox("Do the patients and barcode match the setup sheet?", vbYesNoCancel)
          Select Case iYesNo
                 Case vbYes
                 GoTo Line2
                 Case vbNo
                 MsgBox ("Doesn't match! Please enter again")
                 Call DeleteFolder(Directory)
                 GoTo Line1
           End Select


'ADD VBA REFERENCE: MICROSOFT XML, v3.0 or v6.0 '
Line2:
    Dim oXMLFile As New MSXML2.DOMDocument
    Dim imgNode As MSXML2.IXMLDOMNodeList, destNode As MSXML2.IXMLDOMNodeList
    Dim XML*File*Name As String


    XML*File*Name = "C:\Users\cmccabe\Desktop\EmArray\Design\imagene.bch"
    oXMLFile.Load (XML*File*Name)
    
'EXTRACT NODES INTO LIST AND REWRITE NODES '
    Set imgNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Image")
    imgNode(0).Text = "I:\" & "2571683" & MyBarCode & "_532.tif"
    imgNode(1).Text = "I:\" & "2571683" & MyBarCode & "_635.tif"


    Set destNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Destination")
    destNode(0).Text = MyDirectory
    destNode(1).Text = MyDirectory
    
'SAVE UPDATED XML '
     oXMLFile.Save XML*File*Name
   
'UNINTIALIZE OBJECTS '
    Set imgNode = Nothing
    Set destNode = Nothing
    Set oXMLFile = Nothing
    
'CALCULATE TIME FOR PROCESS '
    Function Process() As String
    
    Dim StartTime As Double
    Dim MinutesElapsed As String
    
    StartTime = Timer ' define start time
'CALL JAVA PROGRAM USING SHELL AND COMPLETE PROCESS '
    Dim wshell As Object
    Set wshell = CreateObject("wscript.shell")
    
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    wshell.Run Chr(34) & "C:\Users\cmccabe\Desktop\EmArray\Design\java.bat", windowsStyle, waitOnReturn
    
'UPDATE PERL VARIABLES USING SHELL '
    Dim PerlCommand As String, PerlParameters As String, VarDirectory As String
    Dim var As String, var1 As String, var2 As String, var3 As String
    
    MsgBox ("Verifying spike-ins, please wait")
    
    VarDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")
     
    var = VarDirectory
    var1 = "sample_descriptor.txt"
    var2 = "C:\cygwin\home\cmccabe\test_probes8.txt"
    var3 = var & "\" & "output.txt"
     
'CALL PERL '
    PerlCommand = """C:\Users\cmccabe\Desktop\EmArray\Design\perl.bat"""
    PerlParameters = """" & var & """" & " " & _
    """" & var1 & """" & " " & _
    """" & var2 & """" & " " & _
    """" & var3 & """"
    CreateObject("wscript.shell").Run PerlCommand & "  " & PerlParameters, windowsStyle, waitOnReturn
    
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss") 'elapsed seconds
    Process = MinutesElapsed & " minutes"
    ret = Process()
    Write #1, ret
    End Function
    
     iYesNo = MsgBox("ImaGene and spike-in anlysis complete, do you want to run NxClinical?", vbYesNoCancel)


    Select Case iYesNo


           Case vbYes
'CALL AND EXECUTE NXCLINICAL '
    Dim x As Variant
    Dim Path As String


'SET INSTALLATION PATH '
    Path = "C:\Program Files\BioDiscovery\NxClinical Client\NxClinical.exe"


    x = Shell(Path, vbNormalFocus)
    
            Case vbNo
 'CLOSE AND EXIT '
    Application.DisplayAlerts = False
    Application.Quit
  End Select
  
  End Function

[Leith Ross]

Hello cmccabe1,

Deja vu! You must be experiencing the same problem I am with the Excel Forum. I have been locked out for over 24 hours. Always seems to happen this time of year.

I don't see any bold text in your post. Maybe you can post just that section of the code.

[Bob Phillips]

If you look Leith, you can see the markup characters around the inline function declaration

    Function Process() As String

and the lines

   Process = MinutesElapsed & " minutes"
    ret = Process()
    Write #1, ret
    End Function

[Leith Ross]

Hello xld,

Thanks for pointing that out. If I have to refer to the source code to determine what is supposed to be "highlighted" on the screen then why bother displaying the code in a window that should highlight the code's text?

[cmccabe1]

Thank you XLD and I apologize for the confusion Leith. Yes those are lines that do not seem to work. Thank you both very much .

[Bob Phillips]

Hello xld,

Thanks for pointing that out. If I have to refer to the source code to determine what is supposed to be "highlighted" on the screen then why bother displaying the code in a window that should highlight the code's text?

I understand that, but the OP quite reasonably trid to highlight them with bold markups, but the VBA tags seem to override/ignore that. Anyway, ultimately you have to refer to the code, that is where it is documented