Looking for .VBS script to open excel file and delete duplicate records

Hello all,

I am seeking a script written in Visual Basic (.VBS) that would be used during my automation process (all scripted) to delete duplicates. (Please note, not VBA)

Right now what I am doing is pulling from 8 different independent SQL databases through crystal and exporting each of the results from each db source into its own file. After that I merge them all to 1 single file, in some cases 1 file and multiple tabs (1 for each db source).

After the merge takes place the script I am now asking for would check the following criteria and delete any duplicates.  There are more columns to this but I am listing only the columns I wish to check dupes for:

A. FIRST NAME        B. LAST NAME        C. ADDRESS               D. CITY            E. STATE        F. ZIP       G. PHONE NUMBER

The script should compare the above columns and delete any duplicates that all A-G items are an exact match.  After these have all been deleted I would like it to save the file as deduped.myfile.xls.

Thank you for your help!
LVL 1
smyers051972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tony_angelopoulosCommented:
from your vbs, something like:

Dim objExcel
Dim Sht
Dim Count
Dim FilePath
FilePath = "your path to the excel file here"
Set objExcel = CreateObject("Excel.Application")
Set Sht = objExcel.Worksheet

objExcel.Workbooks.Open FilePath, False, True

For Each Sht In objExcel.Sheets
    Count = objExcel.WorksheetFunction.CountA(Sht.Range("A:A"))
    .Range("A1:G" & Count).AdvancedFilter 1, , , True
Next Sht

-- to use autofilter to remove duplicates.

HOWEVER - if you are familiar with ado, I would use it to load the values into a recordset, do a select distinct, and write a new workbook, 1 page at a time.  sorting, filtering, and eliminating duplicates are extremely fast for a set based language like sql.  this can all be done from within a vbs.
0
smyers051972Author Commented:
ill try your script out see if it works for my application, now as far the select distinct we do that already the problem is, the db servers are all aboard vessels at sea.  We are pulling casino players aboard our cruise ships (8 of them) so what may happen is person X may have sailed on multiple ships and therefore will be included from both selects any ways because they are all independent :)

Dupes are bound to happen you have to think of them all as independent hotels.

Thanks for your help ill follow up tomorrow!


0
smyers051972Author Commented:
Hi

I tested the code and got an error: expected end of statement line 14 char 6
0
tony_angelopoulosCommented:
AH, thanks for keeping me honest!  

sorry about that, try this one.  I tested it and it seems to work without a hitch.  Replace FilePath and FileName with yours.


Dim objExcel
Dim Sht
Dim Count
Dim FilePath
 
FilePath = "c:\test.xls"
FileName = "test.xls"
 
Set objExcel = CreateObject("Excel.Application")
'Set Sht = objExcel.Worksheet
objExcel.visible = true
objExcel.Workbooks.Open FilePath, False, False
 
For Each Sht In objExcel.Sheets
    Sht.Range("A:G").AdvancedFilter 1,,,True
Next
 
objExcel.Workbooks(FileName).Save
objExcel.Workbooks(FileName).Close
objExcel.Quit
 
set objExcel = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smyers051972Author Commented:
Thank you again for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.