Solved

Writing to Rows in EXCEL via VBScript

Posted on 2011-02-23
1
380 Views
Last Modified: 2012-05-11
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
Comment
Question by:danfiggolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34966410
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 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