Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Writing to Rows in EXCEL via VBScript

How do I setup the script to write new line to next row in single Worksheet, without opening a new workbook in this script?
Option Explicit

Dim strFile, strInputLog, strOutputLog, objFSO, objInput, objOutput
Dim strLine, blnError, intCount, dtmToday, intSummary
Dim objExcel, objWorkbook

Const ForReading = 1

dtmToday = Now()

' Specify input log file.
strInputLog = "D:\Documents and Settings\figueroadaniel\My Documents\ClassPatchInstallation_0792.log"
' Output file.
strOutputLog = "d:\output.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open files.
Set objInput = objFSO.OpenTextfile(strInputLog, ForReading)
Set objOutput = objFSO.CreateTextFile(strOutputLog, True)

' Read each line of input log file.
intCount = 0
blnError = False
Do Until objInput.AtEndOfStream
    strLine = objInput.ReadLine
    intCount = intCount + 1
    If (InStr(strLine, "Class") > 0) Then
    
    	Set objExcel = CreateObject("Excel.Application")
		Set objWorkbook = objExcel.Workbooks.Open("d:\test.xls")

		objExcel.Application.Visible = True
		objWorkbook.WorkSheets(1).Activate
		objWorkbook.WorkSheets(1).Cells(1, 1).Value = strLine
		objExcel.ActiveWorkbook.Close
		objExcel.Application.Quit
   
      
        objOutput.WriteLine intCount
        objOutput.WriteLine strLine
        blnError = True
    Else
        If (blnError = True) Then
            objOutput.WriteLine strLine
            blnError = False
        End If
    End If
Loop

' Close input file.

objInput.Close
objOutput.Close

Open in new window

0
danfiggolf
Asked:
danfiggolf
1 Solution
 
RobSampsonCommented:
Hi, try this.  You will need to modify the strColumns value to specify each column names that your inserting data into.

Regards,

Rob.
Option Explicit

Dim strFile, strInputLog, strOutputLog, objFSO, objInput, objOutput
Dim strLine, blnError, intCount, dtmToday, intSummary
Dim objExcel, objWorkbook

Const ForReading = 1

dtmToday = Now()

' Specify input log file.
strInputLog = "D:\Documents and Settings\figueroadaniel\My Documents\ClassPatchInstallation_0792.log"
' Output file.
strOutputLog = "d:\output.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open files.
Set objInput = objFSO.OpenTextfile(strInputLog, ForReading)
Set objOutput = objFSO.CreateTextFile(strOutputLog, True)

strXLS = "D:\Test.xls"
strSheetName = "data"
strColumns = "Field1,Field2"

Set adoConnection = CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strXLS & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
adoConnection.Open strConnect

' Read each line of input log file.
intCount = 0
blnError = False
Do Until objInput.AtEndOfStream
    strLine = objInput.ReadLine
    intCount = intCount + 1
    If (InStr(strLine, "Class") > 0) Then

		strSQL = "insert into [" & strSheetName & "] (" & strColumns & ") values ('" & strLine & "')"
		adoConnection.execute strSQL

        objOutput.WriteLine intCount
        objOutput.WriteLine strLine
        blnError = True
    Else
        If (blnError = True) Then
            objOutput.WriteLine strLine
            blnError = False
        End If
    End If
Loop

adoConnection.close
Set adoConnection = Nothing

' Close input file.

objInput.Close
objOutput.Close

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now