LinkBack URL
About LinkBacks
I am having an issue with a macro I created. Essentially I have a bunch of text files that dump memory data from a server every 15 minutes. I am trying to create a macro that will open those text files into a "import" sheet, run a text to columns to clean it up, go to another sheet "data_log" in the same workbook, insert a column, do a vlook up to match data in column A in Data_log to data in column F (bytes) in the import sheet.
What I am running into is that the macro I've created will import all of the data in the text files to the import sheet, but will put all that data in the same columns, running down the rows, so I am trying to make the macro import one file at a time, do the vlookup, then clear the file contents on the "import" sheet, open the next file, rinse repeat. Its opening the files, but not displaying data, and its not doing the vlookup correctly either. It's only importing one line.
Columns A and B, and rows 1-2 will be static in Data_log, and the data imported to import will always have the same format and be the same columns as far as A for the memory tag and F for the bytes it uses.
Here is the Full Code:
I am also trying to get it to use the name of the text file as the heading for each column. I tried using 'Range("C2").Select = oFile.Name but its not working correctly.
Sub Read_Text_Files() Dim sPath As String, sLine As String Dim oPath As Object, oFile As Object, oFSO As Object Dim r As Long ' 'Files location sPath = "C:\Test\" ' r = 1 Set oFSO = CreateObject("Scripting.FileSystemObject") Set oPath = oFSO.GetFolder(sPath) Application.ScreenUpdating = True For Each oFile In oPath.Files If LCase(Right(oFile.Name, 4)) = ".txt" Then ' Open oFile For Input As #1 ' Open file for input. Do While Not EOF(1) ' Loop until end of file. Input #1, sLine ' Read data If Left(sLine, 1) = "=" Then sLine = "'" & sLine 'If line starts with an equal sign, add a single quote at the start sLine = Replace(sLine, Chr(2), "") 'Strip funky characters sLine = Replace(sLine, Chr(3), "") sLine = Replace(sLine, Chr(10), "") Range("A" & r).Formula = sLine ' Write data line 'Text to Columns Range("A1", Range("A" & Cells.Rows.Count).End(xlUp)).Select Selection.TextToColumns DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False Application.ScreenUpdating = True r = r + 1 Loop Close #1 ' Close file. ' Sheets("Data_log").Select Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("D:D").Select Selection.Copy Columns("C:C").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("D1").Select Application.CutCopyMode = False Selection.Copy Range("C1").Select ActiveSheet.Paste Range("C5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Import!RC[-2]:R[1088]C[4],6,FALSE)" Range("C5").Select Selection.AutoFill Destination:=Range("C5:C1093"), Type:=xlFillDefault Range("C5:C1093").Select Calculate Range("C5").Select Calculate Columns("C:C").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Range("C2").Select = oFile.Name ' Sheets("Import").Select Range("A1:H2000").Select Selection.ClearContents Sheets("Data_log").Select End If Next oFile
Here is one of the text files. The formats will be the same just the data will change. The way it appears below is copied directly from the text file. It doesn't import that way into excel which is why I have the text to columns part.
Code:
I had to shorten the text file data since its more then 10000 characters, but the data takes 1093 roles.09:06 AM Tag Type Allocs Frees Diff Bytes Per Alloc TSQ Nonp 90 89 1 56 56 waR Nonp 8031 8031 0 0 0 8042 Nonp 4 0 4 3944 986 ACPI Nonp 43 43 0 0 0 AGP Nonp 1 0 1 200 200 ARFT Paged 3 0 3 96 32 AcdN Nonp 2 0 2 1072 536 AcpA Nonp 360 357 3 192 64 AcpB Paged 38 38 0 0 0 AcpD Nonp 937 559 378 134960 357 AcpF Nonp 8175 8138 37 1480 40 AcpI Nonp 3 3 0 0 0 AcpM Nonp 15774 15774 0 0 0 AcpO Nonp 4 0 4 672 168 AcpP Nonp 356 352 4 352 88 AcpR Nonp 9 6 3 3536 1178 AcpS Nonp 991 786 205 5208 25 Acpg Nonp 1 0 1 48 48 Acpi Nonp 725 724 1 136 136 Acpt Nonp 2 1 1 120 120 Adap Nonp 7 0 7 3024 432 AfdB Nonp 938555 938513 42 56400 1342 AfdC Nonp 165467 158069 7398 1183680 160 AfdE Nonp 174622 173356 1266 354480 280 AfdI Nonp 25309 25309 0 0 0 AfdL Nonp 26006 25520 486 19440 40 AfdP Nonp 132717629 132716615 1014 130064 128 AfdR Nonp 39 29 10 320 32 AfdS Paged 2 2 0 0 0 AfdT Nonp 3 0 3 304 101 AfdX Paged 174472 173332 1140 319200 280 Afdh Nonp 3 0 3 144 48 Afdr Nonp 4 0 4 824 206 Afdt Paged 2 0 2 320 160 AgpG Paged 1 0 1 128 128 AmlC Nonp 2130 2126 4 32768 8192 AmlH Nonp 6 0 6 393216 65536 AmlI Nonp 2 0 2 392 196 AmlR Nonp 1 0 1 32 32 AmlT Nonp 1785 1785 0 0 0 ApcR Paged 34 34 0 0 0 ApcS Nonp 1 0 1 24 24 ArbA Paged 108 0 108 442368 4096 ArbL Paged 621 341 280 73920 264 ArbM Nonp 108 0 108 2592 24 ArbR Paged 216 0 216 6912 32 AtmA Paged 3350 2229 1121 74344 66 AtmT Paged 16 4 12 7392 616 BBdB Nonp 1 0 1 2120 2120 BBdC Nonp 112 0 112 8064 72 BBdD Nonp 2 0 2 96 48 BBdF Nonp 1 0 1 56 56 BBdG Nonp 23 0 23 1656 72 BBdJ Nonp 1 0 1 72 72 BBdK Paged 6 3 3 48 16 BBdL Nonp 1 0 1 216 216 BBdM Nonp 1 0 1 2120 2120 BBdO Paged 1 1 0 0 0 BBdP Paged 2 2 0 0 0 BBdQ Paged 1 1 0 0 0 BBdR Paged 8387 8387 0 0 0 BBdT Paged 8389 8389 0 0 0 BBdU Paged 67107 67107 0 0 0 BBdV Nonp 1 0 1 136 136 BBdZ Paged 2 2 0 0 0 BBda Paged 1 1 0 0 0 BBdc Paged 455 451 4 896 224 BBdd Paged 2 0 2 16384 8192 BBdf Paged 67107 67107 0 0 0 BBdg Paged 2 0 2 264 132 BBdh Nonp 1 1 0 0 0 BBdi Paged 8482347 8482347 0 0 0 BBdj Paged 5305 5305 0 0 0 BBdk Paged 1 1 0 0 0 BBdq Paged 1 1 0 0 0 BBds Paged 36673 36673 0 0 0 BBdu Paged 1 0 1 56 56 BBdv Paged 2 2 0 0 0 BBdw Paged 10 10 0 0 0 BBdx Paged 2 2 0 0 0
I am ok with formulas, and some scripting, but am just getting into VBA.
Register To Reply
Bookmarks