Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with My Macros Program
Hello All,
Please be patient and read through since its a long message Here Is the Macros i am using right now. 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 ----------------- i have used this to parse data in the text files. here is the data from the text file 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=E 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 Example of what is to be done: the sequence number may be repeted but not the condidence level 00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D 00:02:38 DBG OCR crane=5 seq=5007365 camera=1 result=NO OCR conf=C 00:02:38 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=B I dont care if its camera 1 or camera 2, can anyone out there help me to update my marcos, so that i can capture the conf=" " value depending on priority as A,B,C,D,E Choose "B" in the above case. Count all the Conf=A with are obtained from the unique "seq" Number. i am using the following Functions in my "General Summary" sheet 1.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Discharge")--to count the number of Discharges 2.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Load")--to count the number of Loads I am able to count the total Number Of Conf=A, Conf=B, Etc but because the sequence Numbers are Duplicated the count Isnt right. Need more information I can send it thru personal mail. Please Help Me out as soon as you can -- Thanks a lot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with My Macros Program
Hi, I have a query. Why are the sequence numbers duplicated ? You cannot
achieve this using countif function alone. You have to write a VBA code snippet to eliminate duplicates and then count. I hope this helps you. "Please help in building a Vba in excel" wrote: Hello All, Please be patient and read through since its a long message Here Is the Macros i am using right now. 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 ----------------- i have used this to parse data in the text files. here is the data from the text file 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=E 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 Example of what is to be done: the sequence number may be repeted but not the condidence level 00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D 00:02:38 DBG OCR crane=5 seq=5007365 camera=1 result=NO OCR conf=C 00:02:38 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=B I dont care if its camera 1 or camera 2, can anyone out there help me to update my marcos, so that i can capture the conf=" " value depending on priority as A,B,C,D,E Choose "B" in the above case. Count all the Conf=A with are obtained from the unique "seq" Number. i am using the following Functions in my "General Summary" sheet 1.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Discharge")--to count the number of Discharges 2.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Load")--to count the number of Loads I am able to count the total Number Of Conf=A, Conf=B, Etc but because the sequence Numbers are Duplicated the count Isnt right. Need more information I can send it thru personal mail. Please Help Me out as soon as you can -- Thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macros program | Excel Discussion (Misc queries) | |||
Repeat Macros Function/Program | Excel Programming | |||
Mass update VBA macros with batch program | Excel Programming | |||
Can I bring up a seperate program with macros? Hit a button and b. | Excel Programming | |||
Books on "how to" program macros in Excel using VBA | Excel Programming |