Solved

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

Posted on 2008-10-25
5
839 Views
Last Modified: 2013-11-10
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!
0
Comment
Question by:smyers051972
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:tony_angelopoulos
ID: 22806269
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
 
LVL 1

Author Comment

by:smyers051972
ID: 22806292
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
 
LVL 1

Author Comment

by:smyers051972
ID: 22812384
Hi

I tested the code and got an error: expected end of statement line 14 char 6
0
 
LVL 8

Accepted Solution

by:
tony_angelopoulos earned 500 total points
ID: 22816478
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
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31510039
Thank you again for your help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A short article about problems I had with the new location API and permissions in Marshmallow
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now