Sub Import_Data()
' Imports .csv files that have been exported from VBOX Test Suite
' Requires knowledge of Test Number (Ex. TT18188)
' Requires .csv files with the naming convention -> test code_spec_test method_test type_# in series
'
' test code = 111,115,117, etc.
' spec = D1892M, D1867M, etc.
' test method = Breakin, wet, or dry
' test type = Speed or Trigger (111 uses speed and trigger combination, all others are trigger)
'
' Example for 111: 111_D1896M_Breakin_Speed_1.csv
' Example for 115: 115_D1896M_Breakin_Trigger_1.csv
Dim TestNum As String
Dim basePath As String, fileName As String
Dim projectFolder As String
Dim Breakin1, Breakin1T, Breakin2, Breakin2T, Breakin3, Breakin3T, Breakin4, Breakin4T, Breakin5, Breakin5T, Breakin6, Breakin6T As String
Dim Wet1, Wet1T, Wet2, Wet2T, Wet3, Wet3T, Wet4, Wet4T, Wet5, Wet5T, Wet6, Wet6T As String
Dim Dry1, Dry1T, Dry2, Dry2T, Dry3, Dry3T, Dry4, Dry4T, Dry5, Dry5T, Dry6, Dry6T As String
'User inputs test number
TestNum = InputBox("Please input Test Number")
If TestNum = "" Then Exit Sub
'Base folder path for stopping distance .csv export files
basePath = "C:\Users\nathan\Documents\SD Improvements\SD Examples\"
If Right(basePath, 1) <> "\" Then basePath = basePath & "\"
'Finds folder within Base folder path from test number input
projectFolder = ""
fileName = Dir(basePath, vbDirectory)
While fileName <> vbNullString And projectFolder = ""
If (GetAttr(basePath & fileName) And vbDirectory) = vbDirectory Then
If fileName Like "*" & TestNum Then projectFolder = basePath & fileName & "\"
End If
fileName = Dir
Wend
If projectFolder <> "" Then
Call Spec1
Call Spec2
Call Spec3
Call Spec4
Call Spec5
Call Spec6
End If
End Sub
Sub Spec1()
Dim TestNum As String
Dim basePath As String, fileName As String
Dim projectFolder As String
Dim Breakin1, Breakin1T As String
Dim Wet1, Wet1T As String
Dim Dry1, Dry1T As String
Breakin1 = Dir(projectFolder & "*Breakin_Speed_1.csv")
Breakin1T = Dir(projectFolder & "*Breakin_Trigger_1.csv")
Wet1 = Dir(projectFolder & "*Wet_Speed_1.csv")
Wet1T = Dir(projectFolder & "*Wet_Trigger_1.csv")
Dry1 = Dir(projectFolder & "*Dry_Speed_1.csv")
Dry1T = Dir(projectFolder & "*Dry_Trigger_1.csv")
If Breakin1T <> "" Then
'For 111 test code - Break in
If InStr(Breakin1T, "111") <> 0 Then
'Hides sheets that arent used for 111 test code
Sheets("Results").Visible = True
Sheets("Results ").Visible = False
Sheets("111 Raw Data").Visible = True
Sheets("Raw Data").Visible = False
'Opens Breakin speed file and pastes into universal template
Workbooks.Open fileName:=Breakin1
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("B2").Value = Breakin1
Range("B2").Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("B3").Select
Selection.TextToColumns Destination:=Range("B3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Breakin1
ActiveWindow.Close False
'Opens Breakin trigger file and pastes into universal report
Workbooks.Open fileName:=Breakin1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("K2").Value = Breakin1T
Range("K2").Select
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("K3").Select
Selection.TextToColumns Destination:=Range("K3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Breakin1T
ActiveWindow.Close False
End If
Else
'For test codes other than 111 - Break in
If InStr(Breakin1T, "115") <> 0 Then
Sheets("Results").Visible = False
Sheets("Results ").Visible = True
Sheets("111 Raw Data").Visible = False
Sheets("Raw Data").Visible = True
Workbooks.Open fileName:=Breakin1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("Raw Data").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("B2").Value = Breakin1T
Range("B2").Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("B3").Select
Selection.TextToColumns Destination:=Range("B3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Breakin1T
ActiveWindow.Close False
End If
End If
If Wet1T <> "" Then
'For 111 test code - Wet
If InStr(Wet1, "111") <> 0 Then
'Hides sheets that arent used for 111 test code
Sheets("Results").Visible = True
Sheets("Results ").Visible = False
Sheets("111 Raw Data").Visible = True
Sheets("Raw Data").Visible = False
'Opens Wet speed file and pastes into universal template
Workbooks.Open fileName:=Wet1
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("T2").Value = Wet1
Range("T2").Select
Selection.TextToColumns Destination:=Range("T2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("T3").Select
Selection.TextToColumns Destination:=Range("T3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Wet1
ActiveWindow.Close False
'Opens Breakin trigger file and pastes into universal report
Workbooks.Open fileName:=Wet1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("AC3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("AC2").Value = Wet1T
Range("AC2").Select
Selection.TextToColumns Destination:=Range("AC2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("AC3").Select
Selection.TextToColumns Destination:=Range("AC3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Wet1T
ActiveWindow.Close False
End If
Else
'For test codes other than 111
If InStr(Wet1T, "115") <> 0 Then
Sheets("Results").Visible = False
Sheets("Results ").Visible = True
Sheets("111 Raw Data").Visible = False
Sheets("Raw Data").Visible = True
Workbooks.Open fileName:=Wet1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("K2").Value = Wet1T
Range("K2").Select
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("K3").Select
Selection.TextToColumns Destination:=Range("K3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Wet1T
ActiveWindow.Close False
End If
End If
If Dry1T <> "" Then
'For 111 test code - Dry
If InStr(Dry1, "111") <> 0 Then
'Hides sheets that arent used for 111 test code
Sheets("Results").Visible = True
Sheets("Results ").Visible = False
Sheets("111 Raw Data").Visible = True
Sheets("Raw Data").Visible = False
'Opens Wet speed file and pastes into universal template
Workbooks.Open fileName:=Dry1
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("AL3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("AL2").Value = Dry1
Range("AL2").Select
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("AL3").Select
Selection.TextToColumns Destination:=Range("AL3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Dry1
ActiveWindow.Close False
'Opens Breakin trigger file and pastes into universal report
Workbooks.Open fileName:=Dry1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("AL3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("AL2").Value = Dry1T
Range("AL2").Select
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("AL3").Select
Selection.TextToColumns Destination:=Range("AL3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Dry1T
ActiveWindow.Close False
End If
Else
'For test codes other than 111
If InStr(Dry1T, "115") <> 0 Then
Sheets("Results").Visible = False
Sheets("Results ").Visible = True
Sheets("111 Raw Data").Visible = False
Sheets("Raw Data").Visible = True
Workbooks.Open fileName:=Dry1T
ActiveSheet.UsedRange.Copy
Windows("Universal SD Template.xlsm").Activate
Sheets("111 Raw Data").Select
Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("T2").Value = Dry1T
Range("T2").Select
Selection.TextToColumns Destination:=Range("T2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("T3").Select
Selection.TextToColumns Destination:=Range("T3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 1)), TrailingMinusNumbers _
:=True
Workbooks.Open fileName:=Dry1T
ActiveWindow.Close False
End If
End If
End Sub