write variable in vba to text file
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
Code:
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
