How to convert this excel script to read from a text file

[arlinda]

Hi,

I would like to know how to I re-write this script to read and write this script in a text file format. Please advice. Thank you.

call myMacro1()


Sub mymacro1()
'
' mymacro1 Macro
' Macro recorded 2
'

'
const xlDown=&HFFFFEFE7

Set objXL = CreateObject("Excel.Application")
'objXL.visible=true
Set objWB = objXL.WorkBooks.Open("C:\data\abc.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("abc")

With objWS

.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Expiration Date"
.Range("M1").value = "ModificationDate"

end with

dim iRow
iRow=2
Do Until objWS.Cells(iRow,"C").Value = ""
objWS.Cells(iRow, "M") = Now()

iRow = iRow + 1
Loop

'objXL.DisplayAlerts=false
objWB.save
objWB.Close
objXL.Quit

End Sub

 

[EBGreen]

Search the forum for FileSystemObject and OpenTextFIle for examples of reading and writing from and to text files. Alternatively you can also search and find examples of using Excel to import a text file then you may be able to use the excel code that you already have. The last possibility that I would suggest is that if you look in the FAQ section, you will find an excellent FAQ on using ADO to access text files.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[arlinda]

Ok,

This is what I was able to do. Still researching the rest. Any comments


Const ForReading = 1
Const ForWriting = 2


'open the data file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\data\abc.csv", ForReading)


sDo Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If InStr(strLine, "f0") = 0 Then
strNewContents = "First Name"
End If
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.Write strNewContents

objFile.Close

 

[arlinda]

Please read this one not the one above

This is what I was able to do. Still researching the rest. Any comments


Const ForReading = 1
Const ForWriting = 2


'open the data file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\data\abc.csv", ForReading)


sDo Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If InStr(strLine, "f0") = 0 Then
strNewContents = "First Name"
End If
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\data\abc.csv", ForWriting)
objFile.Write strNewContents

objFile.Close

 

[EBGreen]

If the file is csv I would really recommend using ADO or less desireable Excel rather than reading the raw text.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[arlinda]

The thing is that the pc does not have Ms OFFICE and when I frst compiled the script I got an error on, "Excel.Application"

So far I am able to add a row on the first line and add a heading for every column. I am working on adding the current date on teh Modification column. Please advice.

'On Error Resume Next

Const ForReading = 1
Const ForWriting = 2


'open the data file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\data\abc.csv", ForReading)


strContents = objFile.ReadAll
objFile.Close

strFirstLine = "First Name, Middel int, Last name, Start Date, ModificationDate"

strNewContents = strFirstLine & vbCrLf & strContents

Set objFile = objFSO.OpenTextFile("C:\data\abc.csv", ForWriting)
objFile.WriteLine strNewContents

objFile.Close

 

[EBGreen]

Have you tried using ADO. The reason I am pushing this is it is much, much more robust. Especially if the data might have commas in it. You can write a parser by hand in VBScript to handle this. As a matter of fact you could probably find the code to parse a csv file if you poke around either in this forum or on google. The problem is that you would have to hope that whoever wrote the code really knew what they were doing. If you use ADO you can pretty much take it as a given that it will handle the CSV file in a proper fashion.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[arlinda]

I am not familiar with ADO so I am researching it write now.

 

[arlinda]

I am not sure about ADO. Need advice?

 

[EBGreen]

Did you read the FAQ here in this forum?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[arlinda]

Not a lot of examples

 

[EBGreen]

Ok, well show us the ADO code that you tried and explain how it is not working for you and we can help you iron out the wrinkles.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[arlinda]

Ok,
So far I have used ADO to open up my file. Let me know if this is correct.

Private Sub myTest()
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

con.Open "provider=sampprov;data
source=C:\data\teletick1.csv"
Set rs = con.Execute("teletick.csv")

with rs

.Rows("1:1").Insert // still researching this part
.Application.Selection.End(xlDown).Select()
.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Expiration Date"
.Range("F1").value = "Dept Num"
.Range("G1").value = "Employee Numb"
.Range("H1").value = "SSN"
.Range("I1").value = "Hire Date"
.Range("J1").value = "Birth Date"
.Range("K1").value = "Dept name"
.Range("L1").value = "Job Desc"
.Range("M1").value = "ModificationDate"

end with