We help IT Professionals succeed at work.

VBScript to read a list of excel files and output to text file

Medium Priority
774 Views
Last Modified: 2012-05-06
Hi,

I have a list of of standard excel output files with TWO worksheets. Each worksheets contains 2 columns. I need to capture standard usage result for 9:00:00 AM & 10:00:00 AM from 2 worksheets and output to a text file. The file names and date maybe vary, but output format will be standard.

Scenario:
I have 100 of excel output files. I need to execute a scripts that capture information for all excels files and output to a single text file.

Sample expected output result.
FileName - Worksheet - Timing - Usage
machineA100.xls - worksheet(1) - 9:00:00 - 8.12
machineA100.xls - worksheet(1) - 10:00:00 - 7.59
machineA100.xls - worksheet(2) - 9:00:00 - 2.32
machineA100.xls - worksheet(2) - 10:00:00 - 3.08
machineXYZ.xls - worksheet(1) - 9:00:00 - ...
machineXYZ.xls - worksheet(1) - 10:00:00 - ...
machineXYZ.xls - worksheet(2) - 9:00:00 - ...
machineXYZ.xls - worksheet(2) - 10:00:00 - ...

Sample Data for standard excel files.
Worksheet(1)-2 columns
Time                  Usage (Average/Rate)
2/18/2009 8:55:00 AM      7.00
2/18/2009 9:00:00 AM      8.12
2/18/2009 9:05:00 AM      6.28
2/18/2009 9:10:00 AM      6.27
2/18/2009 9:15:00 AM      5.29
2/18/2009 9:20:00 AM      7.13
2/18/2009 9:25:00 AM      7.23
2/18/2009 9:30:00 AM      7.17
2/18/2009 9:35:00 AM      6.23
2/18/2009 9:40:00 AM      6.33
2/18/2009 9:45:00 AM      6.49
2/18/2009 9:50:00 AM      6.81
2/18/2009 9:55:00 AM      7.28
2/18/2009 10:00:00 AM      7.59

Worksheet(2)-2 columns
Time                  Usage (Average/Absolute)
2/18/2009 8:55:00 AM      1.59
2/18/2009 9:00:00 AM      2.32
2/18/2009 9:05:00 AM      2.45
2/18/2009 9:10:00 AM      4.39
2/18/2009 9:15:00 AM      2.65
2/18/2009 9:20:00 AM      3.05
2/18/2009 9:25:00 AM      3.12
2/18/2009 9:30:00 AM      3.19
2/18/2009 9:35:00 AM      3.59
2/18/2009 9:40:00 AM      3.19
2/18/2009 9:45:00 AM      4.99
2/18/2009 9:50:00 AM      3.19
2/18/2009 9:55:00 AM      2.79
2/18/2009 10:00:00 AM      3.08

Thanks for any suggestion given.

Best regards,
kelvin Tiong
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
The following seems to be working for me.  It evaluates all Excel workbooks in a specified folder.








Dim fso, fld, fil, ts, xlApp, xlWb, xlWs
Dim WsCount, At9, At10, CelCount

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("c:\Test")
Set ts = fso.CreateTextFile("c:\Test\Results.txt", True)
Set xlApp = CreateObject("Excel.Application")

ts.WriteLine "FileName - Worksheet - Timing - Usage"

For Each fil In fld.Files
    If UCase(Right(fil.Name, 3)) = "XLS" Then
        Set xlWb = xlApp.Workbooks.Open(fil.Path)
        For WsCount = 1 To 2
            Set xlWs = xlWb.Worksheets(WsCount)
            At9 = "x"
            At10 = "x"
            With xlWs
                For CelCount = 2 To .Cells(.Rows.Count, 1).End(-4162).Row
                    If TimeValue(.Cells(CelCount, 1)) = #9:00 AM# Then
                        At9 = .Cells(CelCount, 2)
                    ElseIf TimeValue(.Cells(CelCount, 1)) = #10:00 AM# Then
                        At10 = .Cells(CelCount, 2)
                    End If
                    If IsNumeric(At9) And IsNumeric(At10) Then Exit For
                Next
            End With
            If IsNumeric(At9) Then
                ts.WriteLine xlWb.Name & " - " & xlWs.Name & " - 9:00:00 - " & At9
            Else
                ts.WriteLine xlWb.Name & " - " & xlWs.Name & " - 9:00:00 - MISSING"
            End If
            If IsNumeric(At10) Then
                ts.WriteLine xlWb.Name & " - " & xlWs.Name & " - 10:00:00 - " & At10
            Else
                ts.WriteLine xlWb.Name & " - " & xlWs.Name & " - 10:00:00 - MISSING"
            End If
        Next
        xlWb.Close False
    End If
Next

Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
ts.Close
Set ts = Nothing
Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hi matthewspatrick,
Thanks a lot for your code, I only manage to come out with coding that read one worksheet.  Yours is much clean and short.

I just enhance a bit so that i can dump in any directory and execute it.

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(MyFileDir)
MyFileDir=fso.GetAbsolutePathName("")
FilePath=MyFileDir & "\Results.txt"
Set ts = fso.CreateTextFile(FilePath, True)

Thanks for your help!;) Really appreciated!

Best regards,
Kelvin Tiong
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.