/build/static/layout/Breadcrumb_cap_w.png

VBScript, Find Text File, Read Contents Into Excel Spreadsheet

Trying to learn VBScript on the fly for my job. Need help getting this to work. This is a combination of scripts that I have found on the web and cobbled together into something that almost works.

Here's the story... We are working to perform a mass upgrade of an application to about 300 users running Windows XP. Before we can start we need to determine what version(s) of the application is installed on each system. Unfortunately, the software vendor decided to make this as difficult as possible. We are able to determine who has the application installed but the only real way to accurately determine the app version and level (Std or Pro, Oracle or SQL, etc.) is to extract the data from a plain-text key file. What I'm hoping to do is create a VBScript that I can run against a collection of computers. It needs to open the key file and enter the contents (2 lines of text) and the computer name into an Excel spreadsheet. To make things even more interesting users may have multiple versions of the app installed AND the key file is not always located in the same place. However the key file does always have the same file name and it will always be somewhere under Program Files.

Here's what I've come up with so far:


i = 0
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile where FileName = 'test' and Extension = 'key' and Drive = 'c:' ")
For Each objFile in colFiles
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(objFile.Name)
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close
For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1

Next

Set appexcel = WScript.CreateObject("Excel.Application")
With appexcel
'.Visible = True
Set wb=.Workbooks.Open("c:\temp\test.xls")
r = 1
Do Until Len(.Cells(r, 1).Value) = 0
r = r + 1
Loop
.Cells(r, 1).Value = arrFileLines
.Cells(r, 2).Value = arrFileLines(1)

wb.Save
Set wb = Nothing
.Quit
End With
Set appexcel = Nothing
Next


It works almost perfectly on my test system except for one strange issue... If I place multiple copies of the key file in different places it will recognize all of them but will only enter the data from the first file it finds; but it enters the same data from the first file the same number of times as files that it locates. To clarify, if I have 3 copies of the file, it will enter the key data 3 times, but all 3 will be from the first file it located. If I have 2 copies, it enters two rows of data, but only from the first file it located, etc.

I'm sure it's something simple that I'm overlooking, but I'm at a loss.

The other problem I'm having is how to restrict the search to 'C:\Program Files\' instead of the entire 'c:\' drive. I've tried changing the Drive = 'c:' to Drive = 'c:\program files\' but this kicks back an error.


Once I have that sorted out I will need to figure out a way to run it against a list of computer names and how to add the computer name to the row next to the key file info but I think I can handle that.

Any assistance would be greatly appreciated.

0 Comments   [ + ] Show comments

Answers (13)

Posted by: Jsaylor 14 years ago
Second Degree Blue Belt
2
The function you're looking for is Ubound, I believe. Your line should be something like:

If ubound(arrfilelines) = 2 Then
......
End If
Posted by: anonymous_9363 14 years ago
Red Belt
0
Not in the least. What would be tedious would be to recreate the same code over and over. Any developer worthy of the name has a set of favourite routines which are designed to be used on a "plug in" basis.
Posted by: mturman 14 years ago
Orange Belt
0
Glad to see that my thread has sparked such intriguing rhetoric on the merits of 'good' scripting versus whatever it is that I am (and the rest of the coding newbies are) doing.

Anyhow, turns out my original script works fine. The issue was with my test system, but that's all been sorted out.

I made a few minor changes and added the Computer Name information as well. Here's my updated code:


Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile where FileName = 'test' and Extension = 'key' and Drive = 'c:' ")
For Each objFile in colFiles
i = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(objFile.Name, 1)
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close
For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1

Next
Set appexcel = WScript.CreateObject("Excel.Application")
With appexcel
'.Visible = True
Set wb=.Workbooks.Open("C:\Temp\Test.xls")
r = 1
Do Until Len(.Cells(r, 1).Value) = 0
r = r + 1
Loop
.Cells(r, 1).Value = strComputerName
.Cells(r, 2).Value = arrFileLines
.Cells(r, 3).Value = arrFileLines(1)
'.Cells(r, 4).Value = arrFileLines(2)
wb.Save
Set wb = Nothing
.Quit
End With
Set appexcel = Nothing
Next


The problem I'm facing now is variations in the key file format. Some key files will have 2 lines of text and others may have 3. If I enable the '.Cells(r, 4).Value = arrFileLines(2) ' line, which tells the script to write line 3 of the key file, it works fine if the file acutally has 3 lines of text. If the key file does not have 3 lines I get a 'Out of Range' error. I tried getting around this with an 'If Exists Then' statement, but I'm stuck on what to look for.

I tried:

If Exists arrFileLines(2) Then
.Cells(r, 4).Value = arrFileLines(2)


With arrFileLines(2) in quotes, parenthesis, both parethesis and quotes, and with neither (as above) but I always get an 'expected Then' error.

What am I doing wrong?
Posted by: mturman 14 years ago
Orange Belt
0
That was it! Thank you, sir.
Posted by: punit 13 years ago
Yellow Belt
0
below is the script which reads all the lines of text file, and echo.

Option Explicit
Dim objFSO, strTextFile, strData, strLine, arrLines
CONST ForReading = 1
'name of the text file
strTextFile = "INFORMATION_DATA_INFO1.csv"
'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll
'Split the text file into lines
arrLines = Split(strData,vbCrLf)
'Step through the lines
For Each strLine in arrLines
wscript.echo strLine
Next
'Cleanup
Set objFSO = Nothing


Plese please please,
can anybody modify the above script, to read user define no of rows, and copy that row into new text file with same name as source file on same location where source file is present
i m using following syntex to run the above mention script from command line (windows),
cscript "file location\script.vbs" (Above script)
which is working perfectly fine,
But what i want is, with above syntex now i will enter No.of raw which i wants to read, and please dont make this script hard code i.e i have to change name of source.file in to the script. which also i want user will pass with .vbs script, so the final syntex that i will enter in command line (windows)is as follows;
cscript "Script_location\Script.vbs" "File_location\File_Name.txt" "Row_count"
Where:-
"Script_location\Script.vbs" = Our script.vbs location
"File_location\File_Name.txt" = source file which user wants to read
"Row_Count" = No of row of source.txt which user wants to read

After running above line in command line the new text file which is created by the script which contain only user specified no. of rows on the same loacation where source file is present.
ya and 1 last thing is, while running above script it should not give any popup on screen, new script should do its work quitly.
Please i want this thing urgently, as i m new with vbscripting and i m not able to write vbscript on my own, need help please.......
Posted by: Aruna 9 years ago
White Belt
0

Hello Sir !! i want to create one text file,then to read an excel file and then putting data into text file......similarly i want Auotfill forms code i.e 1st to create text file,then reading excel sheet and then by using  if loop entering data into text file ...all this in vbscript...plssss help!!!!!

 

Posted by: Aruna 9 years ago
White Belt
0

are you getting my point???

Posted by: kapil.kathuria 14 years ago
Orange Belt
0
looks like you hav ejust placed below line at wrong place.
For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1

Try below code:

i = 0
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile where FileName = 'test' and Extension = 'key' and Drive = 'c:' ")
For Each objFile in colFiles
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(objFile.Name)
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close
Next

Set appexcel = WScript.CreateObject("Excel.Application")
For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1
With appexcel
'.Visible = True
Set wb=.Workbooks.Open("c:\temp\test.xls")
r = 1
Do Until Len(.Cells(r, 1).Value) = 0
r = r + 1
Loop
.Cells(r, 1).Value = arrFileLines
.Cells(r, 2).Value = arrFileLines(1)

wb.Save
Set wb = Nothing
.Quit
End With
Set appexcel = Nothing
Next


For multiple computers, you can either use input text file that conatins comupter names or you can put compuer names in excel file. Read text fiile or excel file and then write in excel file.

Also, notice that formating is important in VBScripting. If you are using text based editor for VBS, without formatting it is very complicated to understand script.
Posted by: mturman 14 years ago
Orange Belt
0
Thanks, kapil.kathuria, but no joy.
I now recieve a WSH error 800A01A8, 'Object Required'. What's interesting is that if I enable the '.Visible = True' line, which allows me to see the spreadsheet opening and closing, I get the 1A8 error on it. If I clear that line out I get the 1A8 error on the 'Set wb=.Workbooks.Open("c:\temp\test.xls")
' line. With either cofiguration the script is still able to open the spreadsheet and write the info from the first .key file. The error appears just after that. Any suggestions? I've tried adding Option Explicit and dimming all my variables but that didn't help. Also tried moving the 'Set wb=.Workbooks.Open("c:\temp\test.xls") to different locations but no luck there either.


Also, notice that formating is important in VBScripting. If you are using text based editor for VBS, without formatting it is very complicated to understand script.

Currently I am using Notepad++ to edit my script files. Can you suggest something better? (Preferably for free.) VBS is definitely hard to understand. I purchased a MS VBScript book and I'm taking a training class later this month. Hopefully that will help to expediate my learning curve.
Posted by: anonymous_9363 14 years ago
Red Belt
0
Currently I am using Notepad++ to edit my script files.It's not so much the editor, more the formatting of the code. Use TAB or mulitple spaces to ident the code, e.g.For x = 0 To UBound(arrWhatever)
If arrWhatever(x) = "A test string" Then
MsgBox "Gotcha"
End If
Next
Also, if you have MS Office installed, set up the Microsoft Script Editor as your script debugger. Insert the 'STOP' statement somewhere in the code and run it. When the script reaches the STOP statement, you'll be prompted to start the debugger. Click through and use either F5 to execute the script as-is or F11 to step through each line at a time. That makes debugging script much easier.

So, to the problem....If you're getting 'Object Required' then clearly the script thinks there's no object for it to use. At which line is it presenting that error? The debugger will show you. I suspect it's the AppExcel object, in which case you need to determine why it's unable to create it.

Next up, you must, must, MUST add error-trapping to ANY code. Always assume that NOTHING will work. So, for example, immediately after the execution of a line which creates an object, the next line should check to see if the object got created:If Not IsObject(objExcel) Then
'// Present an error message and exit
End If
Do that for EVERY object, even the common ones like, say, FileSystemObject. Similarly, error-trap ALL operations. Right after the call to .Save method, did the file get saved? At the moment, the script assumes it did. There's no handling for failure.

As I believe I may have mentioned once or twice, scripting is easy (in spite of what you say, VBScript IS easy). Good scripting is hard.
Posted by: Jsaylor 14 years ago
Second Degree Blue Belt
0
Good scripting is tedious

Totally appropriate FTFY. ;)
Posted by: anonymous_9363 14 years ago
Red Belt
0
Not really. Once you've been doing it for a while, you build up a set of scripts containing functions/subs which you use over and over again. Equally, the satisfaction comes from knowing that you have - to th ebest of your ability - catered for any scenario.

I was asked by my last client's server team to combine 2 scripts into one. I asked "What error message do you want to put into the log for an absent/offline server?"
"Well," came the response, "There won't *BE* any absent servers."
"Er, what question did I ask you 6 hours ago, Mr [name removed to avoid embarrassment]?"
"Ah," came the response. "Your packaging server was down, wasn't it?"

But none of that ever happens in real lfe, of course....
Posted by: Jsaylor 14 years ago
Second Degree Blue Belt
0
Good scripting is tedious

...which you use over and over again.

Looks like my assertion stands!
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ