Prevent duplicate from import of data

[Novicelearner]

Hi. Im quite new to vba excel. Is there a way for me to prevent duplicate from being imported using any code or method. Below is the code that i will be using.

ChDir "Open from directory

sales = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:=sales _
, Origin:=437, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(11, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), _
TrailingMinusNumbers:=True

ActiveSheet.Move Before:=Workbooks("sales Update.xlsm").Sheets(1)
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Thanks.

 

[Joe4]

Welcome to the Board!

The only way I can think of is if all your file names are different, and you keep a running track of the ones that have already been imported.
You could then check to make sure you don't import the same file again.

Otherwise, you may want to approach this from a different angle, and instead clean-up the duplicates after they have been imported.
Excel has a built-in "Remove Duplicates" functionality that may be able to handle this.

 

[Novicelearner]

Hi joe4,
Thanks for your response.

Have use the remove duplicates in excel. Im trying to use vba to help me cross check against my existing data and prompt the user that what they imported a duplicate data and they need to select a new data source

 

[Joe4]

I think it would be quite complex to have Excel check it without importing it (beyond my knowledge level). It would need to read the data without opening it in Excel.
Rather, maybe try an approach like this:
- Import it into a "temporary" sheet or workbook
- Copy over you existing data to the bottom of it
- Get the count of the records
- Run the "Remove Duplicates" functionality on all the data
- Get the count of the records again
- If the count is the same, they are not duplicates, and you can proceed with having the code importing it into your final destination
- If the count has gone done, return message that there are duplicates, close/erase the temporary sheet/workbook, and do not proceed with importing to your final destination