October 10, 2008 08:57pm pdt

1. RobSamp... 1,295,450
2. purplepo... 343,863
3. rorya 219,207
4. zorvek 218,779
5. Shift-3 197,336
6. sirbounty 192,710
7. merowinger 182,304
8. sungenw... 151,691
9. saurabh726 126,890
10. weellio 116,460
11. BlueDevilF... 102,060
12. hielo 91,020
13. nutsch 90,100
14. chris_bot... 81,908
15. byundt 73,898
16. jared_luker 71,890
17. matthews... 65,375
18. robberbaron 62,610
19. jeverist 61,396
20. GrahamSkan 58,108
21. Chris-Dent 54,072
22. gregcmcse 52,830
23. Idle_Mind 45,140
24. JesterToo 43,149
25. wildboy85 40,165

1. RobSamp... 2,272,216
2. sirbounty 509,794
3. purplepo... 363,313
4. rorya 298,926
5. zorvek 285,269
6. Shift-3 226,741
7. merowinger 196,879
8. chandru_... 183,713
9. BlueDevilF... 165,410
10. weellio 157,455
11. sungenw... 151,691
12. Chris-Dent 140,122
13. saurabh726 134,810
14. matthews... 131,719
15. GrahamSkan 119,358
16. jeverist 111,596
17. amit_g 110,710
18. hielo 97,620
19. chris_bot... 93,969
20. nutsch 90,100
21. Idle_Mind 88,540
22. byundt 88,006
23. robberbaron 87,806
24. angelIII 78,637
25. jared_luker 74,890

04.07.2008 at 01:45PM PDT, ID: 23302696

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.2

VBScript to make new Excel sheet for each daily changing text files and append data every x time

Asked by danlein in VB Script, Visual Basic Programming, Microsoft Excel Spreadsheet Software

Tags: VBScript, Firefox

Here is the scenario. Forgive me if it is a bit long winded.

I have a production machine that appends data to a delimited text file every 10 seconds. Every night, someone has to change the file name. The name they change it to is never consistent. However, whatever they change it to in the program, will begin appending data. The previous file will not be modified again.

I need to write a script that will scan, every minute or so, for the current file being modified and append that data to an existing Excel spreadsheet.

I have found and appended a script that will scan one particular file name every 10 seconds and append the data that is being written to it to a named excel sheet. The script is below. What I need is for the code to not look for the specified file, in this case 1.txt, but any file in the directory being modified.

The way the script runs currently, is that I have to manually change the 1.txt each day to the new file name, and it overwrites the sheet from the previous day. I would ultimately like the script to find only the file being modified in roughly the last 15 minutes and make a new sheet with the new txt file's data.Start Free Trial

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:

           
           
             strFile = "c:\control\1.txt"
intSeconds = 10
strDataDelimiter = "    "
strExcelFile = "C:\control\data3.xls"
Const intForReading = 1
Const xlUp = -4162
Const xlInsertDeleteCells = 1
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Set objFSO = CreateObject ("Scripting.FileSystemObject")
 
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN " & intSeconds & " WHERE " _
& "TargetInstance ISA 'CIM_DataFile' AND " _
& "TargetInstance.Name='" & Replace(strFile, "\", "\\") & "'")
 
Do
Set objLatestEvent = colMonitoredEvents.NextEvent
      
 
      Set objExcelApp = CreateObject("Excel.Application")
      objExcelApp.Visible = False
      objExcelApp.Workbooks.Open strExcelFile
      
      objExcelApp.ActiveSheet.Range("A1").Select
      On Error Resume Next
      objExcelApp.Selection.QueryTable.Refresh False
    ' Check if no error was raised
    If Err.Number = 0 Then
    '      MsgBox "Existing data refreshed."
    ' Otherwise if there was an error refreshing the querytable, do the whole process again
      Else
            Err.Clear
            On Error GoTo 0
            objExcelApp.ActiveSheet.Cells.Delete
            
            With objExcelApp.ActiveSheet.QueryTables.Add("TEXT;c:\control\1.txt", objExcelApp.ActiveSheet.Range("A1"))
              .Name = "data3"
              .FieldNames = True
              .RowNumbers = False
              .FillAdjacentFormulas = False
              .PreserveFormatting = True
              .RefreshOnFileOpen = False
              .RefreshStyle = xlInsertDeleteCells
              .SavePassword = False
              .SaveData = True
              .AdjustColumnWidth = True
              .RefreshPeriod = 0
              .TextFilePromptOnRefresh = False
              .TextFilePlatform = 850
              .TextFileStartRow = 1
              .TextFileParseType = xlDelimited
              .TextFileTextQualifier = xlTextQualifierDoubleQuote
              .TextFileConsecutiveDelimiter = True
              .TextFileTabDelimiter = True
              .TextFileSemicolonDelimiter = True
              .TextFileCommaDelimiter = False
              .TextFileSpaceDelimiter = True
              .TextFileOtherDelimiter = "<"
              .TextFileOtherDelimiter = ":"
	      .TextFileOtherDelimiter = "\"
              .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
              .TextFileTrailingMinusNumbers = True
              .Refresh False
          End With
      End If
 
      objExcelApp.ActiveWorkbook.Save
      objExcelApp.Quit
      Set objExcelApp = Nothing
      
      'MsgBox "Done"
Loop

           
         

Keywords: VBScript to make new Excel sheet for …

	Title
1	Query an Excel spreadsheet without r…
2	A protected sheet is causing mis-calc…
3	Forgiveness
4	WHERE IS Excel HISTORY SHEET kept?
5	Simple excel sheet to web
6	Filtering info from master sheet to sub …

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_2_20070628

VBScript to make new Excel sheet for each daily changing text files and append data every x time

Asked by danlein in VB Script, Visual Basic Programming, Microsoft Excel Spreadsheet Software

Tags: VBScript, Firefox

About this solution