Solved

Writing to Rows in EXCEL via VBScript

Posted on 2011-02-23
1
370 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now