Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help removing duplicates for mail merge
Hello
I am populating an Excel table from records based in another software package. After I populate the Excel table I use the mail merge function programmitically to create envelopes based on the records in excel. What I need to do is delete duplicate records in Excel before the mail merge so only 1 envelope is being created for 1 person rather than several. I have found a bit of code which works fine in the Excel VBA screen but when I try to migrate this code over to ArcGIS VBA I recieve a Complie Error: Wrong number of arguements or invalid property assignment at the : "V = Rng.Cells(r, 1).value " line with "Cells" being hightlighted. Any suggestions?? I have attached the code below. Thanks very much Private Sub CommandButton1_Click() Dim wsheet As Worksheet Dim wbook As Workbook Dim appExcel As New Excel.Application Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range '************************************************* ******** '* Open a new excel sheet and set the workbook information '************************************************* ******** ' Set wbook = appExcel.Workbooks.Add 'Set wsheet = appExcel.Sheets(1) Set wbook = appExcel.Workbooks.Open("C:\Documents and Settings\quigleyj\Desktop\jeff2.xls") Set wsheet = appExcel.Sheets("Sheet1") ' ' This macro deletes duplicate rows in the selection. Duplicates are ' counted in the COLUMN of the active cell. Range("F3:F300,G3:G300").Select On Error GoTo EndMacro appExcel.ScreenUpdating = False appExcel.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).value If appExcel.WorksheetFunction.CountIf(Rng.Columns(1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: appExcel.ScreenUpdating = False appExcel.Calculation = xlCalculationManual 'wbook.SaveAs "C:\Documents and Settings\quigleyj\Desktop\jeff3.xls" appExcel.Visible = True -- quigleyj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different graphic for each record in mail merge document | Charts and Charting in Excel | |||
How do I merge two spreadsheets so it removes the duplicates? | Excel Worksheet Functions | |||
How do I do an excel merge like a word mailmerge with another exc. | Excel Discussion (Misc queries) | |||
Serious Mail Merge Problem | Excel Discussion (Misc queries) | |||
removing duplicates testing in 2 coloms | Excel Worksheet Functions |