I have been looking high and low for the answer to this, so any help is
appreciated.

I have thousands of files that I need to list just unique file types in
a sheet. I am trying to find a way to do this that is *very* fast.

What's making it so slow right now is that in order to make sure they
are unique I have to check the current file against every item of an
array array before I add it to the end of the array. This is fast for a
few files, but after 2000 it starts to get to be a bit slow.

One idea I had is if there is a way to create a class that only allows
unique items, but I am not sure how that would work. Does anyone know if
there is a setting for that, besides just going through each item and
checking it.

Basically if I could put in my code

On Error Goto Next

oMyFile.add file.type

that would generate an error and move along, thereby avoiding the need
to go through everything.

Or if anyone has another idea, I am all ears (and eyes)

Heath

Heath

I would approach this as follows:

use the FileScripting Object to list all the files in the folder(s) into
rows in a worksheet
use Text to Columns to separate out the file extension, .xls, etc
use Advanced Filter to copy unique extensions to another area
use CountIf to count the entries for each extension
use AutoFilter to select the extensions with a count = 1

Switch off ScreenUpdating to make the process quicker

Searching the Archives should give you lots of examples of listing files,
etc

Regards

Trevor


"Heath" wrote in message
...
I have been looking high and low for the answer to this, so any help is
appreciated.

I have thousands of files that I need to list just unique file types in
a sheet. I am trying to find a way to do this that is *very* fast.

What's making it so slow right now is that in order to make sure they
are unique I have to check the current file against every item of an
array array before I add it to the end of the array. This is fast for a
few files, but after 2000 it starts to get to be a bit slow.

One idea I had is if there is a way to create a class that only allows
unique items, but I am not sure how that would work. Does anyone know if
there is a setting for that, besides just going through each item and
checking it.

Basically if I could put in my code

On Error Goto Next

oMyFile.add file.type

that would generate an error and move along, thereby avoiding the need
to go through everything.

Or if anyone has another idea, I am all ears (and eyes)

Heath

Hi
Use a collection rather than an array.
Declare it:
Dim ExtensionCollection as New Collection

To add to it use this pseudocode:
On error resume next
For each myExtension in (my list of files)
ExtensionCollection.Add myExtension, CStr(myExtension)
Next myExtension
On error Goto 0

myExtension is whatever you are adding to your array, drawn from "my
list of files" - however you have defined them.

The add method generates an error wnenever an element with the same
name (thats the CStr(myExtension) bit) already exists in the
collection. So you end up with a unique list. This method only
requires one pass through your data.
You can output your collection like this
Dim ExtensionArray() as String
Dim ExtensionVariant as variant
Dim ExtensionCount as Long
ExtensionCount = ExtensionCollection.Count
Redim ExtensionArray(1 to ExtensionCount,1) as String '(?)
For i = 0 to ExtensionCount - 1
ExtensionArray(i+1,1) = ExtensionCollection(i)
next i
ExtensionVariant = ExtensionArray
Worksheets("OutputSheet").Range("A1").Resize(Exten sionCount,1).Value
= _

ExtensionVariant

or one element at a time of course.

regards
Paul
Heath wrote in message ...
I have been looking high and low for the answer to this, so any help is
appreciated.

I have thousands of files that I need to list just unique file types in
a sheet. I am trying to find a way to do this that is *very* fast.

What's making it so slow right now is that in order to make sure they
are unique I have to check the current file against every item of an
array array before I add it to the end of the array. This is fast for a
few files, but after 2000 it starts to get to be a bit slow.

One idea I had is if there is a way to create a class that only allows
unique items, but I am not sure how that would work. Does anyone know if
there is a setting for that, besides just going through each item and
checking it.

Basically if I could put in my code

On Error Goto Next

oMyFile.add file.type

that would generate an error and move along, thereby avoiding the need
to go through everything.

Or if anyone has another idea, I am all ears (and eyes)

Heath

The usual solution to this problem is to use a Collection object in
place of your array. Each time you add to the collection, use the same
value for the Item and Key arguments. Duplicate keys, and therefore
duplicate items, will cause a runtime error, which you can ride over
with On Error Resume Next. You will end up with a collection of unique
items.

--

Heath wrote in message ...
I have been looking high and low for the answer to this, so any help is
appreciated.

I have thousands of files that I need to list just unique file types in
a sheet. I am trying to find a way to do this that is *very* fast.

What's making it so slow right now is that in order to make sure they
are unique I have to check the current file against every item of an
array array before I add it to the end of the array. This is fast for a
few files, but after 2000 it starts to get to be a bit slow.

One idea I had is if there is a way to create a class that only allows
unique items, but I am not sure how that would work. Does anyone know if
there is a setting for that, besides just going through each item and
checking it.

Basically if I could put in my code

On Error Goto Next

oMyFile.add file.type

that would generate an error and move along, thereby avoiding the need
to go through everything.

Or if anyone has another idea, I am all ears (and eyes)

Heath