Vista - Read multiple files with script

Hello,

I have a folder that has about 100 text files and I want to be able to take
the information from each file and add them to an excel spreadsheet. I have
a script that does this, however since it wants to open up excel each time
the script runs it causes errors as only one excel session can be open at
one time, what I would like to do is run the excel part seperatly or have
the script wait for excel to close before it can write to the file again...
also needs to append to the existing file each time.... below is a copy of
the script... any help would be appreciated.

strFolder = "c:\Program Files\Backweb"

strTarget = "\\server1\public\KJ59"

strFile = "kj59.ini"

If Right(strTarget, 1) <> "\" Then strTarget = strTarget & "\"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Const intForReading = 1

Set objNetwork = CreateObject("WScript.Network")

strFoundFile = ""

For Each objFile In objFSO.GetFolder(strFolder).Files

If LCase(objFile.Name) = LCase(strFile) Then strFoundFile = objFile.Path

Next

If strFoundFile = "" Then

MsgBox "File not found."

Else

strNewFile = objNetwork.ComputerName & ".txt"

objFSO.CopyFile strFoundFile, strTarget & strNewFile, True

MsgBox strFoundFile & VbCrLf & "copied to" & VbCrLf & strTarget & strNewFile

End If


' Now read the contents of the first line in the file

Set objFile = objFSO.OpenTextFile(strTarget & strNewFile, intForReading,
False)

If Not objFile.AtEndOfStream Then strLine = objFile.ReadLine

objFile.Close

Set objFile = Nothing


' Now put the contents of the file into Excel

Set objExcel = CreateObject("Excel.Application")

Const xlUp = -4162

strExcelFile = "\\server1\public\KJ59\Contents.xls"

If objFSO.FileExists(strExcelFile) = True Then

Set objWB = objExcel.Workbooks.Open(strExcelFile, False, False)

Else

Set objWB = objExcel.Workbooks.Add

End If

Set objSheet = objWB.Sheets(1)

intRow = objSheet.Cells(65536, 1).End(xlUp).Row

If intRow > 1 Then intRow = intRow + 1

objSheet.Cells(intRow, 1).Value = objNetwork.ComputerName

objSheet.Cells(intRow, 2).Value = strLine

objExcel.DisplayAlerts = False

objWB.SaveAs strExcelFile

objWB.Close False

objExcel.DisplayAlerts = True

objExcel.Quit

Next

You must not have copied the entire program. The last statement, "Next", has
no matching "For Each" that I can find. Also, the program reads only the
first line of a file. I don't see where it reads more than one file.

In general, bind to the Excel objects outside any loop, so this happens only
once. Then read the files in a loop, and/or each line of a file in a loop,
and write values to the one instance of the spreadsheet. In your code I
can't see where you read more than one line in more than one file, but
perhaps something is missing.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

"Gavin" <gavin.rossnospam@xxxxxx> wrote in message
news:uRBMc38JJHA.1936@xxxxxx

You are correct, I am only reading the first line of the file as that is the
only line I need. The script runs on each machines and then copies the file
to a new location and renames it. All I am trying to do is put all the
information from each file that is copied into one spreadsheet, not quite
sure how to do this.

Thanks
Gavin...


"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxx> wrote in
message news:O2vZ3W9JJHA.2348@xxxxxx