Open find file dialog multiple files

[Guest]

When opening multiple files with the find file dialog (can't re,ber exact
dialog as on wrong computer). The only problem i get is I am wanting to selct
around 200-300 files to work on. The method i have works fine for about 20
files but any more than that and the string it stores the files as gets to
big. I was wondering if anyone had ideas to a way around this or to get the
dialog to store it as an array from the start. Any soultion either for ofiice
2003 or office 97 would be great help.

 

[Nick]

WOW 200 - 300 files... what the heck are you working on?? )

In tha past I have used a list of file names in a worksheet (as the files
stay the same each time).
Then I run a macro which loops through the list and opens the file. So each
time you are opening one one file at a time.
That way you don't run out of space in your filename string.

Nick

 

[Guest]

Aye have been thinking along the same lines. What i'm working on is the data
from a system called ip accounting it makes a text file every 5 mins with
what ip address's. As you can imagine it makes a lot of files over 24 hours
(288).

As the user needs to be able to browse to the files as the name's change a
lot i was wondering if there was anyway to keep this as a string or array
within the Code but if i can get it to cyccle through whats in the folder and
add the file name which can then be used to generate thew file details i'll
give it a go.

Of course if anyone has any other ideas on how to do this i'd be very
greatful.

 

[Nick]

If you want to open all of the files in a specified directory you can use
the Dir function.

You can loop until the Dir function returns an empty string. For example

dim strFile as string

strFile = Dir(c:\temp\*.txt) ' Returns the first .txt file in the
directory

do while strFile <>"" ' Do until an empty string is returned - last file
retrieved
Workbooks.Open strFile
strFile = Dir ' Not specifying any arguments returns the next .txt
file in the directory
loop

The files are not retrieved in any particular order so if you want them
opened in a particular order save the names to a range or an array and sort
them accordingly.

I hope this helps.


Nick

 

[Guest]

Do you know the only really stupid thing. I've only just relaised i can put
an array in there. i've beeing using a similar bit of code to open them all
but never thought of just using an artray while opening them like that. I now
feal so stupid it's almost sickening. Still if only i could now find a way
past the hard coded date format in text strings (csv files) for dates in
excel 2003 i'd be happy.

Thanks very much Nick .. now to go play WoW as it's finaly arrived here at
home.

 

[Nick]

Yes an array can be used, never really thought of that myself either.

You are going to play what?? WoW what's that?
Nick