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