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
Quote:
> 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
>