Sign in to follow this  
Followers 0
Pook

Excel file Search, then Delete Row based on INI/Txt file

3 posts in this topic

Okay,

I’m not sure if I using the right terms when searching the Internet or this site. What I’m trying to do is this, (hope I’m explaining it right) I just looking for some direction on how to find an example. Any help would be great, or Ideas!!

1) I have an Excel file that I create using script that pulls Add/Remove programs for a PC using SCCM web report. (using VB)

2) I want to take this Excel file that looks like the example below, and search for everything listed in the INI file (Example below)

3) If it finds that entry, then removes that row. This way I can remove all things like standard patches, and programs to get a list (excel file) of applications for my techs to install for replacement hardware. I have to do this for over 3000 workstations that is why I’m trying to clean up the SCCM export.

<< EXCEL FILE EXAMPLE >> (columns B, and C not shown)

Display Name Microsoft .NET Framework 4 Extended

2007 Microsoft Office Suite Service Pack 1 (SP1)

Hotfix for Visual C++ Standard 2010 Beta 1 - ENU (KB2284668)

Microsoft Visual J# 2.0 Redistributable Package - SE

<<INI File EXAMPLE >>

Microsoft .NET Framework 4 Extended

Microsoft Visual J# 2.0 Redistributable Package – SE

Security Update for Microsoft .NET Framework 3.5 SP1

Security Update for Windows Media Player

Share this post


Link to post
Share on other sites



If you want to store your information in a regular Excel file, you will need to use the Excel UDF:

However, it will have to open the Excel file in MS Excel to perform tasks, which will slow you down. If you save your file as a CSV, then you can view it in Excel and still be able to work with it as if it were a plain text file. I believe the CSV method would work much faster for you. The only drawback is you cannot do formatting such as custom column sizes, bold text, colors, etc. It would be just simple text in a grid.


#include <ByteMe.au3>

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

.I change the script to save it as a CSV file instead of Xls file. I was looking at the at using Function “ExcelReadSheetToArray” in this way. Does this sound right?

  • Use “ExcelReadSheetToArray” to pull the data (Software list) out of Colum A and save it to a Txt file.
  • Then loop/split all the entries out of that txt file out (using code below), and check it against another Txt file that I keep the list of entries I don’t want in the final excel file.
  • Save a new Txt file with the entries I want to keep in to a new txt file, and then covert that to Excel and format.

Or am I just making this harder then I need to??

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set WSHShell = wscript.createObject("wscript.shell")
Set oTextStream = oFSO.OpenTextFile("templist.txt")
Looklist = Split(oTextStream.ReadAll, vbNewLine)
oTextStream.Close
For Each StrItem In Looklist 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Edited by Pook

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0