[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-18
2
Medium Priority
?
752 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
0
Comment
Question by:kunghui80
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 23682956
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
0
 
LVL 2

Author Comment

by:kunghui80
ID: 23688822
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question