| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
Hi,
File is at link: http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. the imported file got split into two columns which make the macro harder to write. I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link: http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 8, 4:24*pm, Joel wrote:
Angela: Can you post you log file on savefile.com? *In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. *the imported file got split into two columns which make the macro harder to write. *I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 000429183300 000429283299 100000 01-Sep-08 V0963001 000429182200 000429282199 100000 01-Sep-08 Thx. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 8, 4:24*pm, Joel wrote:
Angela: Can you post you log file on savefile.com? *In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. *the imported file got split into two columns which make the macro harder to write. *I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 000429183300 000429283299 100000 01-Sep-08 V0963001 000429182200 000429282199 100000 01-Sep-08 Thx. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 8, 4:24*pm, Joel wrote:
Angela: Can you post you log file on savefile.com? *In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. *the imported file got split into two columns which make the macro harder to write. *I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 000429183300 000429283299 100000 01-Sep-08 V0963001 000429182200 000429282199 100000 01-Sep-08 Thx. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
I waiting for savefile to come back up so I can get the format correct for
the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path 'format column E Columns("E").NumberFormat = "DD-MMM-YY" LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = ID Range("B" & RowCount) = Num1 Range("C" & RowCount) = Num2 Range("D" & RowCount) = Vou Range("E" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. the imported file got split into two columns which make the macro harder to write. I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 000429183300 000429283299 100000 01-Sep-08 V0963001 000429182200 000429282199 100000 01-Sep-08 Thx. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 8, 9:26*pm, Joel wrote:
I waiting for savefile to come back up so I can get the format correct for the spreadsheet. *Here is what I havew so far. *I didn't remove the duplicates. *thought that should be a sepertate macro. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *'format column E * *Columns("E").NumberFormat = "DD-MMM-YY" * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = ID * * * * *Range("B" & RowCount) = Num1 * * * * *Range("C" & RowCount) = Num2 * * * * *Range("D" & RowCount) = Vou * * * * *Range("E" & RowCount) = FileDate * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? *In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. *the imported file got split into two columns which make the macro harder to write. *I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file athttp://www.ziddu.com/download/3784205/FILE2.txt.html Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 * *000429183300 * * * *000429283299 * *100000 *01-Sep-08 V0963001 * *000429182200 * * * *000429282199 * *100000 *01-Sep-08 Thx.- Hide quoted text - - Show quoted text - Dear Joel, I have uploaded the excel file at http://www.ziddu.com/download/378604...FILES.xls.html Hope you can check the format there. Let me check the above code, will get back to you soon. Thx alot. Waiting for reply. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
Don't use ziddu. there is too much adware at this site. I made the
formatting changes. Try this new code. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path 'format column E Columns("E").NumberFormat = "DD-MMM-YY" LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = ID Range("B" & RowCount) = Num1 Range("C" & RowCount) = Num2 Range("D" & RowCount) = Vou Range("E" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. the imported file got split into two columns which make the macro harder to write. I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid points Joel. Sorry but savefile is down. I have uploaded the *.txt log file athttp://www.ziddu.com/download/3784205/FILE2.txt.html Also Joel I was thinking that if two text files are same inside(not the file names out side being same, I'm referring to the content & that too the 15 digits file#V0963001 being same & ranges being different, it should be populated in another sheet named "Duplicate Entry" with all details. Example: V0963001 000429183300 000429283299 100000 01-Sep-08 V0963001 000429182200 000429282199 100000 01-Sep-08 Thx.- Hide quoted text - - Show quoted text - Dear Joel, I have uploaded the excel file at http://www.ziddu.com/download/378604...FILES.xls.html Hope you can check the format there. Let me check the above code, will get back to you soon. Thx alot. Waiting for reply. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 8, 10:23*pm, Joel wrote:
Don't use ziddu. *there is too much adware at this site. *I made the formatting changes. *Try this new code. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = FileDate * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. *Here is what I havew so far. *I didn't remove the duplicates. *thought that should be a sepertate macro. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *'format column E * *Columns("E").NumberFormat = "DD-MMM-YY" * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = ID * * * * *Range("B" & RowCount) = Num1 * * * * *Range("C" & RowCount) = Num2 * * * * *Range("D" & RowCount) = Vou * * * * *Range("E" & RowCount) = FileDate * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? *In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. *the imported file got split into two columns which make the macro harder to write. *I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
Easy
Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim VG As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) StartDate = Trim(StartDate) StartDay = Left(StartDate, 2) StartMonth = Mid(StartDate, 4, 2) StartYear = Mid(StartDate, 7, 4) StartDate = DateSerial(StartYear, StartMonth, StartDay) End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'VG is the difference between Num1 and Num2 VG = Val(Num2) - Val(Num1) + 1 End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = StartDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. there is too much adware at this site. I made the formatting changes. Try this new code. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path 'format column E Columns("E").NumberFormat = "DD-MMM-YY" LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = ID Range("B" & RowCount) = Num1 Range("C" & RowCount) = Num2 Range("D" & RowCount) = Vou Range("E" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. the imported file got split into two columns which make the macro harder to write. I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right.. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid ... |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
I forgot the filename
Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim VG As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" Range("H1") = "Filename" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) StartDate = Trim(StartDate) StartDay = Left(StartDate, 2) StartMonth = Mid(StartDate, 4, 2) StartYear = Mid(StartDate, 7, 4) StartDate = DateSerial(StartYear, StartMonth, StartDay) End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'VG is the difference between Num1 and Num2 VG = Val(Num2) - Val(Num1) + 1 End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = StartDate Range("H" & RowCount) = FName RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. there is too much adware at this site. I made the formatting changes. Try this new code. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path 'format column E Columns("E").NumberFormat = "DD-MMM-YY" LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = ID Range("B" & RowCount) = Num1 Range("C" & RowCount) = Num2 Range("D" & RowCount) = Vou Range("E" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 4:24 pm, Joel wrote: Angela: Can you post you log file on savefile.com? In this case it will be easier to write a macro that opens the log file and place the data into the worksheet rather than take the imported data from the worksheet and convert it to your format. the imported file got split into two columns which make the macro harder to write. I also need to see the column spacing and any tabs (or other special characters) that may of been changed when the file got imported into excel. "Angela" wrote: Hi, File is at link:http://www.savefile.com/files/2029405 File contains sample of below required and also sample of text file since I was unable to align column headers here & things were looking wierd. I have a folder containing system generated log files around 10000. Each has a detail file also & it is tiresome to complie all from detail in one sheet. I think I can get same from these log files instead. These are five line files starting at start of file & ending at ---- end----. Sample is in sheet "Sample Text File". I want to compile a log sheet in excel of such multiple files placed in a folder. I should be able to browse to the folder & select all the files. The script will cycle through all the files and get the below required in an excel file. Below are the requirement: In second line, after "LOG."date of file is mentioned [Example: 20080901]. Required format of date is dd-mmm-yyyy (01-SEP-2008) In third line, detect fifteen len number [Example: 000001096300001] Required is V0963001 (remove first 6 from left, add alphabet "V"(upper case) to the left, remove 2 zeros at 11th & 12th position and combine remaining two from right forming V0963001 In third line, detect two ten len numbers [Example: 0004291833 to 0004292832] Required is "000429183300" and "000429283299". Add "00" to first number & add "99" to second number from right.. Excel log sheet format is in sheet "Log Sheet". (Sort ascending "Start#") A macro solution would be much appreciated. Thx.- Hide quoted text - - Show quoted text - Dear Joel, Valid ... |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 9, 12:03*am, Joel wrote:
I forgot the filename Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim VG As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * * * Range("H1") = "Filename" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) * * * * * * * * * StartDate = Trim(StartDate) * * * * * * * * * StartDay = Left(StartDate, 2) * * * * * * * * * StartMonth = Mid(StartDate, 4, 2) * * * * * * * * * StartYear = Mid(StartDate, 7, 4) * * * * * * * * * StartDate = DateSerial(StartYear, StartMonth, StartDay) * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'VG is the difference between Num1 and Num2 * * * * * * * * * VG = Val(Num2) - Val(Num1) + 1 * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = StartDate * * * * *Range("H" & RowCount) = FName * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. *there is too much adware at this site. *I made the formatting changes. *Try this new code. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = FileDate * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. *Here is what I havew so far. *I didn't remove the duplicates. *thought that should be a sepertate macro. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *'format column E * *Columns("E").NumberFormat = "DD-MMM-YY" * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) ... read more »- Hide quoted text - - Show quoted text - Hey Joel, The code worked fine. However I would appreciate if you can get the browse to file just like when one opens file in excel, with My recent documents, Desktop, My Documents, My Computer, My Network Places on left, files/folder view on right, bottom we have file name: & file type: where I can give file name & location(address to file). In this case would just want this window so that I can easily copy/paste address of folder and get to it instead of browsing through existing window, clicking + to get to the folder each time I have to. Thanks once again for all the help. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
I change the line below from 4001 to 4071. The new options will remeber the
last folder selected and also contain a box so you can paste in an address rather than always have to select a path. See if you like this better. If not I will do more research tomorrow. Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&, &H8&) "Angela" wrote: On Mar 9, 12:03 am, Joel wrote: I forgot the filename Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim VG As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" Range("H1") = "Filename" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) StartDate = Trim(StartDate) StartDay = Left(StartDate, 2) StartMonth = Mid(StartDate, 4, 2) StartYear = Mid(StartDate, 7, 4) StartDate = DateSerial(StartYear, StartMonth, StartDay) End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'VG is the difference between Num1 and Num2 VG = Val(Num2) - Val(Num1) + 1 End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = StartDate Range("H" & RowCount) = FName RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. there is too much adware at this site. I made the formatting changes. Try this new code. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path 'format column E Columns("E").NumberFormat = "DD-MMM-YY" LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 9, 6:06*am, Joel wrote:
I change the line below from 4001 to 4071. *The new options will remeber the last folder selected and also contain a box so you can paste in an address rather than always have to select a path. *See if you like this better. *If not I will do more research tomorrow. Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&, &H8&) "Angela" wrote: On Mar 9, 12:03 am, Joel wrote: I forgot the filename Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim VG As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * * * Range("H1") = "Filename" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) * * * * * * * * * StartDate = Trim(StartDate) * * * * * * * * * StartDay = Left(StartDate, 2) * * * * * * * * * StartMonth = Mid(StartDate, 4, 2) * * * * * * * * * StartYear = Mid(StartDate, 7, 4) * * * * * * * * * StartDate = DateSerial(StartYear, StartMonth, StartDay) * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'VG is the difference between Num1 and Num2 * * * * * * * * * VG = Val(Num2) - Val(Num1) + 1 * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = StartDate * * * * *Range("H" & RowCount) = FName * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. *there is too much adware at this site. *I made the formatting changes. *Try this new code. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = FileDate * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. *Here is what I havew so far. *I didn't remove the duplicates. *thought that should be a sepertate macro. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If ... read more »- Hide quoted text - - Show quoted text - Thx Jeol for your patience and help. I have uploaded the file at http://www.savefile.com/files/2032417 This is what I'm looking for. Thx once again. I'm grateful. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
I have had your concerns previously with the options for Browse for Folders.
I didn't like the solutions I found so I went looking again today for a better solution. The Browse for Folders (common for all windows functions - WIN32) doesn't have an option to give you sizes and dates. Browse for files doesn't give you an option to pick folders. My solution in the past is to Use GETOPENFILENAME and select a file. Then in VBA code is to extract the folder Name from the file. I can easily do this if you want. "Angela" wrote: On Mar 9, 6:06 am, Joel wrote: I change the line below from 4001 to 4071. The new options will remeber the last folder selected and also contain a box so you can paste in an address rather than always have to select a path. See if you like this better. If not I will do more research tomorrow. Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&, &H8&) "Angela" wrote: On Mar 9, 12:03 am, Joel wrote: I forgot the filename Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim VG As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" Range("H1") = "Filename" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) StartDate = Trim(StartDate) StartDay = Left(StartDate, 2) StartMonth = Mid(StartDate, 4, 2) StartYear = Mid(StartDate, 7, 4) StartDate = DateSerial(StartYear, StartMonth, StartDay) End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'VG is the difference between Num1 and Num2 VG = Val(Num2) - Val(Num1) + 1 End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = StartDate Range("H" & RowCount) = FName RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. there is too much adware at this site. I made the formatting changes. Try this new code. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If objFolder Is Nothing Then MsgBox ("Cannot open directory -xit Macro") Exit Sub End If Set oFolderItem = objFolder.Items.Item Folder = oFolderItem.Path If Range("A1") = "" Then 'format column E Columns("A").NumberFormat = "#." Columns("G").NumberFormat = "DD-MMM-YYYY" Range("A1") = "S#" Range("B1") = "File#" Range("C1") = "Base#" Range("D1") = "Start" Range("E1") = "End" Range("F1") = "VG#" Range("G1") = "Date" End If LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = LastRow + 1 FName = Dir(Folder & "\" & "*.txt") Do While FName < "" Set fin = fs.OpenTextFile(Folder & "\" & FName, _ ForReading, TristateFalse) FileErr = False LineNumber = 0 Do While fin.AtEndOfStream < True ReadData = fin.readline LineNumber = LineNumber + 1 Select Case LineNumber Case 2 If InStr(ReadData, "Start:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) FileDate = Trim(FileDate) FileDate = Replace(FileDate, ".", "/") End If Case 3 If InStr(ReadData, "Order:") = 0 Then MsgBox ("Bad Log File : " & FName) FileErr = True Exit Do Else ID = Left(ReadData, 15) ID = Mid(ID, 7) ID = "V" & Left(ID, 4) & Mid(ID, 7) 'remove everything up to and including the 2nd Pack ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) ReadData = Trim(Replace(ReadData, TABCh, "")) Num1 = Left(ReadData, 10) & "00" 'read past the word "TO" ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) Num2 = Left(ReadData, 10) & "99" 'get the number after the colon VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) End If Case 4 Exit Do End Select Loop If FileErr = False Then Range("A" & RowCount) = (RowCount - 1) & "." Range("B" & RowCount) = ID Range("D" & RowCount) = Num1 Range("E" & RowCount) = Num2 Range("F" & RowCount) = VG Range("G" & RowCount) = FileDate RowCount = RowCount + 1 End If fin.Close FName = Dir() Loop End Sub "Angela" wrote: On Mar 8, 9:26 pm, Joel wrote: I waiting for savefile to come back up so I can get the format correct for the spreadsheet. Here is what I havew so far. I didn't remove the duplicates. thought that should be a sepertate macro. Sub GetLogs() Dim objShell As Object, objFolder As Object Dim ID As String Dim Num1 As String Dim Num2 As String Dim Vou As String Const ForReading = 1, ForWriting = -2, ForAppending = 3 Const Start = "Start:" TABCh = Chr(9) StartLen = Len(Start) Set objShell = CreateObject("Shell.Application") Set fs = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) On Error GoTo 0 If ... read more »- Hide quoted text - - Show quoted text - Thx Jeol for your patience and help. I have uploaded the file at http://www.savefile.com/files/2032417 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES
On Mar 9, 5:17*pm, Joel wrote:
I have had your concerns previously with the options for Browse for Folders. * I didn't like the solutions I found so I went looking again today for a better solution. The Browse for Folders (common for all windows functions - WIN32) doesn't have an option to give you sizes and dates. *Browse for files doesn't give you an option to pick folders. *My solution in the past is to Use GETOPENFILENAME and select a file. *Then in VBA code is to extract the folder Name from the file. *I can easily do this if you want. "Angela" wrote: On Mar 9, 6:06 am, Joel wrote: I change the line below from 4001 to 4071. *The new options will remeber the last folder selected and also contain a box so you can paste in an address rather than always have to select a path. *See if you like this better. *If not I will do more research tomorrow. Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&, &H8&) "Angela" wrote: On Mar 9, 12:03 am, Joel wrote: I forgot the filename Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim VG As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * * * Range("H1") = "Filename" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * StartDate = Left(ReadData, InStr(ReadData, "End:") - 1) * * * * * * * * * StartDate = Trim(StartDate) * * * * * * * * * StartDay = Left(StartDate, 2) * * * * * * * * * StartMonth = Mid(StartDate, 4, 2) * * * * * * * * * StartYear = Mid(StartDate, 7, 4) * * * * * * * * * StartDate = DateSerial(StartYear, StartMonth, StartDay) * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'VG is the difference between Num1 and Num2 * * * * * * * * * VG = Val(Num2) - Val(Num1) + 1 * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID * * * * *Range("D" & RowCount) = Num1 * * * * *Range("E" & RowCount) = Num2 * * * * *Range("F" & RowCount) = VG * * * * *Range("G" & RowCount) = StartDate * * * * *Range("H" & RowCount) = FName * * * * *RowCount = RowCount + 1 * * * End If * * * fin.Close * * * FName = Dir() * *Loop End Sub "Angela" wrote: On Mar 8, 10:23 pm, Joel wrote: Don't use ziddu. *there is too much adware at this site. *I made the formatting changes. *Try this new code. Sub GetLogs() * *Dim objShell As Object, objFolder As Object * *Dim ID As String * *Dim Num1 As String * *Dim Num2 As String * *Dim Vou As String * *Const ForReading = 1, ForWriting = -2, ForAppending = 3 * *Const Start = "Start:" * *TABCh = Chr(9) * *StartLen = Len(Start) * *Set objShell = CreateObject("Shell.Application") * *Set fs = CreateObject("Scripting.FileSystemObject") * *On Error Resume Next * *Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&) * *On Error GoTo 0 * *If objFolder Is Nothing Then * * * MsgBox ("Cannot open directory -xit Macro") * * * Exit Sub * *End If * *Set oFolderItem = objFolder.Items.Item * *Folder = oFolderItem.Path * *If Range("A1") = "" Then * * * 'format column E * * * Columns("A").NumberFormat = "#." * * * Columns("G").NumberFormat = "DD-MMM-YYYY" * * * Range("A1") = "S#" * * * Range("B1") = "File#" * * * Range("C1") = "Base#" * * * Range("D1") = "Start" * * * Range("E1") = "End" * * * Range("F1") = "VG#" * * * Range("G1") = "Date" * *End If * *LastRow = Range("A" & Rows.Count).End(xlUp).Row * *RowCount = LastRow + 1 * *FName = Dir(Folder & "\" & "*.txt") * *Do While FName < "" * * * Set fin = fs.OpenTextFile(Folder & "\" & FName, _ * * * * *ForReading, TristateFalse) * * * FileErr = False * * * LineNumber = 0 * * * Do While fin.AtEndOfStream < True * * * * *ReadData = fin.readline * * * * *LineNumber = LineNumber + 1 * * * * *Select Case LineNumber * * * * * * Case 2 * * * * * * * *If InStr(ReadData, "Start:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * FileDate = Mid(ReadData, InStr(ReadData, "Start") + StartLen) * * * * * * * * * FileDate = Left(FileDate, InStr(FileDate, "End:") - 1) * * * * * * * * * FileDate = Trim(FileDate) * * * * * * * * * FileDate = Replace(FileDate, ".", "/") * * * * * * * *End If * * * * * * Case 3 * * * * * * * *If InStr(ReadData, "Order:") = 0 Then * * * * * * * * * MsgBox ("Bad Log File : " & FName) * * * * * * * * * FileErr = True * * * * * * * * * Exit Do * * * * * * * *Else * * * * * * * * * ID = Left(ReadData, 15) * * * * * * * * * ID = Mid(ID, 7) * * * * * * * * * ID = "V" & Left(ID, 4) & Mid(ID, 7) * * * * * * * * * 'remove everything up to and including the 2nd Pack * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4) * * * * * * * * * ReadData = Trim(Replace(ReadData, TABCh, "")) * * * * * * * * * Num1 = Left(ReadData, 10) & "00" * * * * * * * * * 'read past the word "TO" * * * * * * * * * ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2)) * * * * * * * * * Num2 = Left(ReadData, 10) & "99" * * * * * * * * * 'get the number after the colon * * * * * * * * * VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1)) * * * * * * * *End If * * * * * * Case 4 * * * * * * * *Exit Do * * * * *End Select * * * Loop * * * If FileErr = False Then * * * * *Range("A" & RowCount) = (RowCount - 1) & "." * * * * *Range("B" & RowCount) = ID ... read more »- Hide quoted text - - Show quoted text - Thx for the reply Joel. Joel I think this will do since I just have to browse to the folder. I will stick to your existing code. No addition required. Since then you would also need to add multiple file select I think which I dnt want right now. Sure I will post incase I need that. For now this has saved me almost a 3/4 days work since these are over 2GB logs which first I download & then compile the details afterward. Thanks a million. God bless. - been great help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automated multiple text files into multiple sheets in one workbook | Excel Discussion (Misc queries) | |||
| Extract Info from Multiple files | Excel Worksheet Functions | |||
| I'm trying to compile text from multiple worksheets... | Excel Discussion (Misc queries) | |||
| Opening Multiple files and Copying the info all to one other sheet | Excel Discussion (Misc queries) | |||
| How can I use an "IF" command to compile different info? | Excel Worksheet Functions | |||
![Angela[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
