P
Please help in building a Vba in excel
Hello All, please help me ..
Here is the marco ...i am using
Sub ImportData()
'
' ImportData Macro Ctrl+I
'
Dim sTargetDate, sTargetPath, sTargetFile, sSaicID, sCraneID As String
sTargetPath = "C:\Documents and Settings\Ajay Tummala\Desktop\logs1\"
Dim rCrane As Range
TargetDate = Format(Range("'General Summary'!c2"), "yyyy-mm-dd")
'For every crane load the data
For Each rCrane In Range("'General Summary'!B5:B24")
If rCrane.Offset(0, -1).Value < 10 Then
sSaicID = "0" & rCrane.Offset(0, -1).Text
Else:
sSaicID = rCrane.Offset(0, -1).Text
End If
sCraneID = rCrane.Text
sTargetFile = "Crane-" & sSaicID & "_" & TargetDate & ".LOG"
Sheets(sCraneID).Select
'If the file exists then update the data otherwise delete the data
If Dir(sTargetPath & sTargetFile) <> "" Then
Range("'" & sCraneID & "'!A:I").Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Ajay
Tummala\Desktop\logs1\Crane-" & sSaicID & "_" & TargetDate & ".LOG",
Destination:=Range("A1"))
.Name = "Crane-" & sSaicID & "_" & TargetDate
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Else
Range("'" & sCraneID & "'!A:I").Delete
End If
Next rCrane
Sheets("General Summary").Select
End Sub
This import data from text files to the excel sheet,and i am using certain
formulas in the General Summary sheet
1. =MIN(INDIRECT("'"&$B8&"'!A:A"))
2.=MIN(INDIRECT("'"&$B8&"'!A:A"))
Etc
here Is the text data from the text files..
00:00:22 DBG OCR crane=5 seq=5007364 camera=1 result=TRLU8631313 conf=B
00:00:22 DBG Container 1 OCR
00:00:22 DBG Discharge
00:00:31 DBG UNLOCK crane=5 seq=5007364 trolley=-4950 hoist=1340
00:00:31 DBG Discharge
00:00:31 DBG New OCR alert 39976
00:01:14 DBG LOCK crane=5 seq=5007365 type=2 trolley=9140 hoist=2850
00:01:14 DBG Lock seq 5007365 assumed to be a DISCHARGE
00:01:14 DBG GetStack ship=12 bay=3 trolley=1096 portsideto=True
00:01:14 WRN GetStack() ship not found
00:01:14 DBG Lock seq 5007365 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:01:14 DBG OCR crane=5 seq=5007364 camera=2 result=NO OCR conf=D
00:01:48 DBG OCR crane=5 seq=5007365 camera=1 result=NYKU8328124 conf=B
00:01:48 DBG Container 1 OCR
00:01:48 DBG Discharge
00:01:50 DBG UNLOCK crane=5 seq=5007365 trolley=-4900 hoist=1320
00:01:50 DBG Discharge
00:01:50 DBG New OCR alert 39982
00:02:35 DBG LOCK crane=5 seq=5007366 type=2 trolley=9220 hoist=1890
00:02:35 DBG Lock seq 5007366 assumed to be a DISCHARGE
00:02:35 DBG GetStack ship=12 bay=3 trolley=1106 portsideto=True
00:02:35 WRN GetStack() ship not found
00:02:35 DBG Lock seq 5007366 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D
the sequence number may repeat but it has different conf=" " value,
Example: seq=1234567 camera=2 result=NO OCR conf=D and also
seq=1234567 camera=2 result=NO OCR conf=D
so i need one to help me to parse through all data and chose that conf
number that
Here is the marco ...i am using
Sub ImportData()
'
' ImportData Macro Ctrl+I
'
Dim sTargetDate, sTargetPath, sTargetFile, sSaicID, sCraneID As String
sTargetPath = "C:\Documents and Settings\Ajay Tummala\Desktop\logs1\"
Dim rCrane As Range
TargetDate = Format(Range("'General Summary'!c2"), "yyyy-mm-dd")
'For every crane load the data
For Each rCrane In Range("'General Summary'!B5:B24")
If rCrane.Offset(0, -1).Value < 10 Then
sSaicID = "0" & rCrane.Offset(0, -1).Text
Else:
sSaicID = rCrane.Offset(0, -1).Text
End If
sCraneID = rCrane.Text
sTargetFile = "Crane-" & sSaicID & "_" & TargetDate & ".LOG"
Sheets(sCraneID).Select
'If the file exists then update the data otherwise delete the data
If Dir(sTargetPath & sTargetFile) <> "" Then
Range("'" & sCraneID & "'!A:I").Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Ajay
Tummala\Desktop\logs1\Crane-" & sSaicID & "_" & TargetDate & ".LOG",
Destination:=Range("A1"))
.Name = "Crane-" & sSaicID & "_" & TargetDate
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Else
Range("'" & sCraneID & "'!A:I").Delete
End If
Next rCrane
Sheets("General Summary").Select
End Sub
This import data from text files to the excel sheet,and i am using certain
formulas in the General Summary sheet
1. =MIN(INDIRECT("'"&$B8&"'!A:A"))
2.=MIN(INDIRECT("'"&$B8&"'!A:A"))
Etc
here Is the text data from the text files..
00:00:22 DBG OCR crane=5 seq=5007364 camera=1 result=TRLU8631313 conf=B
00:00:22 DBG Container 1 OCR
00:00:22 DBG Discharge
00:00:31 DBG UNLOCK crane=5 seq=5007364 trolley=-4950 hoist=1340
00:00:31 DBG Discharge
00:00:31 DBG New OCR alert 39976
00:01:14 DBG LOCK crane=5 seq=5007365 type=2 trolley=9140 hoist=2850
00:01:14 DBG Lock seq 5007365 assumed to be a DISCHARGE
00:01:14 DBG GetStack ship=12 bay=3 trolley=1096 portsideto=True
00:01:14 WRN GetStack() ship not found
00:01:14 DBG Lock seq 5007365 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:01:14 DBG OCR crane=5 seq=5007364 camera=2 result=NO OCR conf=D
00:01:48 DBG OCR crane=5 seq=5007365 camera=1 result=NYKU8328124 conf=B
00:01:48 DBG Container 1 OCR
00:01:48 DBG Discharge
00:01:50 DBG UNLOCK crane=5 seq=5007365 trolley=-4900 hoist=1320
00:01:50 DBG Discharge
00:01:50 DBG New OCR alert 39982
00:02:35 DBG LOCK crane=5 seq=5007366 type=2 trolley=9220 hoist=1890
00:02:35 DBG Lock seq 5007366 assumed to be a DISCHARGE
00:02:35 DBG GetStack ship=12 bay=3 trolley=1106 portsideto=True
00:02:35 WRN GetStack() ship not found
00:02:35 DBG Lock seq 5007366 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D
the sequence number may repeat but it has different conf=" " value,
Example: seq=1234567 camera=2 result=NO OCR conf=D and also
seq=1234567 camera=2 result=NO OCR conf=D
so i need one to help me to parse through all data and chose that conf
number that