Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

text files to excel document

5 views
Skip to first unread message

fredri...@gmail.com

unread,
Jun 19, 2006, 7:45:26 AM6/19/06
to
We have som info collected at logon in one text file per computer.
Every file is overwritten at logon. Now i would like to have these
files nicely arranged in an Excel document.

* The text files is in the same folder.
* The text files is in the same format.
PCName~Type~Requester~IP~Mac~Date~Time~User~User
name~Telephone~Department~Admin~OS~SP~OS Type~Processor~Mhz~Mem~free
C:~free D:~Serial~Manufacturer~Model~Monitor model~Monitor S/N~DT /
LT~CD-Rom~HD type and size
* The naming format is COMPUTERNAME-hw.txt
* There is also other text files in the same folder named
COMPUTERNAME-sw.txt that i'm not interested in and there is also html
documents in the same folder.
* There is about 150 text files i want in the Excel document

I found a script by: Fredrik Wall, fredri...@skripta.se,
www.skripta.se that should list the local printers installed and put it
in an excel document.

I have tried to modify Fredriks script but my scripting knowledge is
very very limited.

This is what i have so far, the excel document is perfect for me, but i
can not get the text files in the document.

Can someone please help me?

Option Explicit
On Error Resume Next

' Deklarationer av variabler
Dim objExcel, objWMIService, objPrinter, objRange
Dim strComputer
Dim intRow, colInstalledPrinters

' Anrop av Excel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

' Skapa översta raden med information av vilken information
' som vi tar fram

objExcel.Cells(1, 1).Value = "PCName"
objExcel.Cells(1, 1).Font.Bold = True
objExcel.Cells(1, 1).Interior.ColorIndex = 30
objExcel.Cells(1, 1).Font.ColorIndex = 2

objExcel.Cells(1, 2).Value = "Type"
objExcel.Cells(1, 2).Font.Bold = True
objExcel.Cells(1, 2).Interior.ColorIndex = 30
objExcel.Cells(1, 2).Font.ColorIndex = 2

objExcel.Cells(1, 3).Value = "Requester"
objExcel.Cells(1, 3).Font.Bold = True
objExcel.Cells(1, 3).Interior.ColorIndex = 30
objExcel.Cells(1, 3).Font.ColorIndex = 2

objExcel.Cells(1, 4).Value = "IP"
objExcel.Cells(1, 4).Font.Bold = True
objExcel.Cells(1, 4).Interior.ColorIndex = 30
objExcel.Cells(1, 4).Font.ColorIndex = 2

objExcel.Cells(1, 5).Value = "Mac"
objExcel.Cells(1, 5).Font.Bold = True
objExcel.Cells(1, 5).Interior.ColorIndex = 30
objExcel.Cells(1, 5).Font.ColorIndex = 2

objExcel.Cells(1, 6).Value = "Date"
objExcel.Cells(1, 6).Font.Bold = True
objExcel.Cells(1, 6).Interior.ColorIndex = 30
objExcel.Cells(1, 6).Font.ColorIndex = 2

objExcel.Cells(1, 7).Value = "Time"
objExcel.Cells(1, 7).Font.Bold = True
objExcel.Cells(1, 7).Interior.ColorIndex = 30
objExcel.Cells(1, 7).Font.ColorIndex = 2

objExcel.Cells(1, 8).Value = "User"
objExcel.Cells(1, 8).Font.Bold = True
objExcel.Cells(1, 8).Interior.ColorIndex = 30
objExcel.Cells(1, 8).Font.ColorIndex = 2

objExcel.Cells(1, 9).Value = "Username"
objExcel.Cells(1, 9).Font.Bold = True
objExcel.Cells(1, 9).Interior.ColorIndex = 30
objExcel.Cells(1, 9).Font.ColorIndex = 2

objExcel.Cells(1, 10).Value = "Telephone"
objExcel.Cells(1, 10).Font.Bold = True
objExcel.Cells(1, 10).Interior.ColorIndex = 30
objExcel.Cells(1, 10).Font.ColorIndex = 2

objExcel.Cells(1, 11).Value = "Department"
objExcel.Cells(1, 11).Font.Bold = True
objExcel.Cells(1, 11).Interior.ColorIndex = 30
objExcel.Cells(1, 11).Font.ColorIndex = 2

objExcel.Cells(1, 12).Value = "Admin"
objExcel.Cells(1, 12).Font.Bold = True
objExcel.Cells(1, 12).Interior.ColorIndex = 30
objExcel.Cells(1, 12).Font.ColorIndex = 2

objExcel.Cells(1, 13).Value = "OS"
objExcel.Cells(1, 13).Font.Bold = True
objExcel.Cells(1, 13).Interior.ColorIndex = 30
objExcel.Cells(1, 13).Font.ColorIndex = 2

objExcel.Cells(1, 14).Value = "SP"
objExcel.Cells(1, 14).Font.Bold = True
objExcel.Cells(1, 14).Interior.ColorIndex = 30
objExcel.Cells(1, 14).Font.ColorIndex = 2

objExcel.Cells(1, 15).Value = "OS Type"
objExcel.Cells(1, 15).Font.Bold = True
objExcel.Cells(1, 15).Interior.ColorIndex = 30
objExcel.Cells(1, 15).Font.ColorIndex = 2

objExcel.Cells(1, 16).Value = "Processor"
objExcel.Cells(1, 16).Font.Bold = True
objExcel.Cells(1, 16).Interior.ColorIndex = 30
objExcel.Cells(1, 16).Font.ColorIndex = 2

objExcel.Cells(1, 17).Value = "Mhz"
objExcel.Cells(1, 17).Font.Bold = True
objExcel.Cells(1, 17).Interior.ColorIndex = 30
objExcel.Cells(1, 17).Font.ColorIndex = 2

objExcel.Cells(1, 18).Value = "Mem"
objExcel.Cells(1, 18).Font.Bold = True
objExcel.Cells(1, 18).Interior.ColorIndex = 30
objExcel.Cells(1, 18).Font.ColorIndex = 2

objExcel.Cells(1, 19).Value = "free C:"
objExcel.Cells(1, 19).Font.Bold = True
objExcel.Cells(1, 19).Interior.ColorIndex = 30
objExcel.Cells(1, 19).Font.ColorIndex = 2

objExcel.Cells(1, 20).Value = "free D:"
objExcel.Cells(1, 20).Font.Bold = True
objExcel.Cells(1, 20).Interior.ColorIndex = 30
objExcel.Cells(1, 20).Font.ColorIndex = 2

objExcel.Cells(1, 21).Value = "Serial"
objExcel.Cells(1, 21).Font.Bold = True
objExcel.Cells(1, 21).Interior.ColorIndex = 30
objExcel.Cells(1, 21).Font.ColorIndex = 2

objExcel.Cells(1, 22).Value = "Manufacturer"
objExcel.Cells(1, 22).Font.Bold = True
objExcel.Cells(1, 22).Interior.ColorIndex = 30
objExcel.Cells(1, 22).Font.ColorIndex = 2

objExcel.Cells(1, 23).Value = "Model"
objExcel.Cells(1, 23).Font.Bold = True
objExcel.Cells(1, 23).Interior.ColorIndex = 30
objExcel.Cells(1, 23).Font.ColorIndex = 2

objExcel.Cells(1, 24).Value = "Monitor model"
objExcel.Cells(1, 24).Font.Bold = True
objExcel.Cells(1, 24).Interior.ColorIndex = 30
objExcel.Cells(1, 24).Font.ColorIndex = 2

objExcel.Cells(1, 25).Value = "Monitor S/N"
objExcel.Cells(1, 25).Font.Bold = True
objExcel.Cells(1, 25).Interior.ColorIndex = 30
objExcel.Cells(1, 25).Font.ColorIndex = 2

objExcel.Cells(1, 26).Value = "DT / LT"
objExcel.Cells(1, 26).Font.Bold = True
objExcel.Cells(1, 26).Interior.ColorIndex = 30
objExcel.Cells(1, 26).Font.ColorIndex = 2

objExcel.Cells(1, 27).Value = "HD type and size"
objExcel.Cells(1, 27).Font.Bold = True
objExcel.Cells(1, 27).Interior.ColorIndex = 30
objExcel.Cells(1, 27).Font.ColorIndex = 2

objExcel.Cells(1, 28).Value = "CD-Rom"
objExcel.Cells(1, 28).Font.Bold = True
objExcel.Cells(1, 28).Interior.ColorIndex = 30
objExcel.Cells(1, 28).Font.ColorIndex = 2


' Denna variabel sätts till 2 för att starta insamlingen av
information
' på rad 2.
intRow = 2

' Punkten står för den lokala datorn.
strComputer = "."

*******************************************************************************
This part i know is wrong.

Dim objFile, All, strFilePath
strFilePath = "*.*"
Set objFile = ReadObject("Scripting.FileSystemObject")
Set All = objFile.ReadTextFile(strFilePath, True)
All.ReadLine("*.*")

********************************************************************************
' Här plockar vi fram alla installerade skrivare
' En efter en.
For Each Line in strFilePath

'Och presenterar informationen per skrivare
objExcel.Cells(intRow, 1).Value =
objExcel.Cells(intColumn, 2).Value = objPrinter.PortName
objExcel.Cells(intColumn, 3).Value = objPrinter.Status
objExcel.Cells(intColumn, 4).Value = objPrinter.Location
objExcel.Cells(intColumn, 5).Value = objPrinter.DriverName

' Denna behövs för att vi inte ska få alla skrivare på samma rad
intRow = intRow + 1

Next

**********************************************************************************************

This part works but i guess i can make all columns autofit without
specify every column?

' Det sista vi gör är att vidga alla kolumner så att all information
' syns som den ska.

'Skrivare
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Port
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Status
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("E1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Skrivare
Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Port
Set objRange = objExcel.Range("G1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Status
Set objRange = objExcel.Range("H1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("I1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("J1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
' Status
Set objRange = objExcel.Range("K1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("L1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("M1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Skrivare
Set objRange = objExcel.Range("N1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Port
Set objRange = objExcel.Range("O1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Status
Set objRange = objExcel.Range("P1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("Q1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("R1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
' Status
Set objRange = objExcel.Range("S1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("T1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("U1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Skrivare
Set objRange = objExcel.Range("V1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Port
Set objRange = objExcel.Range("W1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Status
Set objRange = objExcel.Range("X1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

'Plats
Set objRange = objExcel.Range("Y1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("Z1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("AA1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Drivrutin
Set objRange = objExcel.Range("AB1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

' Snyggar upp lite
Set objExcel = Nothing
Set objRange = Nothing
Set objWMIService = Nothing
Set colInstalledPrinters = Nothing

fredri...@gmail.com

unread,
Jun 19, 2006, 4:16:29 PM6/19/06
to
I manage to solve it myself.
First i made a batch file copying all the files to one text file and
run this script

On Error Resume Next

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("full.txt", ForReading)

Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = TRUE
objXL.Workbooks.Add


objXL.Cells(1,1).Value = "PcName"
objXL.Cells(1, 1).Font.Bold = True
objXL.Cells(1, 1).Interior.ColorIndex = 30
objXL.Cells(1, 1).Font.ColorIndex = 2
objXL.Cells(1,2).Value = "type"
objXL.Cells(1, 2).Font.Bold = True
objXL.Cells(1, 2).Interior.ColorIndex = 30
objXL.Cells(1, 2).Font.ColorIndex = 2
objXL.Cells(1,3).Value = "Requester"
objXL.Cells(1, 3).Font.Bold = True
objXL.Cells(1, 3).Interior.ColorIndex = 30
objXL.Cells(1, 3).Font.ColorIndex = 2
objXL.Cells(1,4).Value = "User"
objXL.Cells(1, 4).Font.Bold = True
objXL.Cells(1, 4).Interior.ColorIndex = 30
objXL.Cells(1, 4).Font.ColorIndex = 2
objXL.Cells(1,5).Value = "Telephone"
objXL.Cells(1, 5).Font.Bold = True
objXL.Cells(1, 5).Interior.ColorIndex = 30
objXL.Cells(1, 5).Font.ColorIndex = 2
objXL.Cells(1,6).Value = "Department"
objXL.Cells(1, 6).Font.Bold = True
objXL.Cells(1, 6).Interior.ColorIndex = 30
objXL.Cells(1, 6).Font.ColorIndex = 2
objXL.Cells(1,7).Value = "IP Address"
objXL.Cells(1, 7).Font.Bold = True
objXL.Cells(1, 7).Interior.ColorIndex = 30
objXL.Cells(1, 7).Font.ColorIndex = 2
objXL.cells(1,8).Value = "username"
objXL.Cells(1, 8).Font.Bold = True
objXL.Cells(1, 8).Interior.ColorIndex = 30
objXL.Cells(1, 8).Font.ColorIndex = 2

intRow = intRow + 1

count = 2
Do While objTextFile.AtEndOfStream <> True
strLine = objtextFile.Readline
If inStr(strLine, "~") Then
arrRecord = split(strLine, "~")
objXL.Cells(count,1).Value = arrRecord(0)
objXL.Cells(count,2).Value = arrRecord(1)
objXL.Cells(count,3).Value = arrRecord(2)
objXL.Cells(count,4).Value = arrRecord(3)
objXL.Cells(count,5).Value = arrRecord(4)
objXL.Cells(count,6).Value = arrRecord(5)
objXL.Cells(count,7).Value = arrRecord(6)
objXL.Cells(count,8).Value = arrRecord(7)
End If
count = count + 1
Loop

objTextFile.Close

Set objRange = objXL.Range("A1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objXL.Range("B1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objXL.Range("C1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objXL.Range("D1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objXL.Range("E1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objXL.Range("F1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objXL.Range("G1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objXL.Range("H1")
objRange.Activate
Set objRange = objXL.ActiveCell.EntireColumn
objRange.Autofit()

0 new messages