Open find file dialog multiple files

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.

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

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

"Magius00" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" wrote:

> WOW 200 - 300 files... what the heck are you working on?? :o)
>
> 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
>
> "Magius00" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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

"Magius00" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> WOW 200 - 300 files... what the heck are you working on?? :o)
>>
>> 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
>>
>> "Magius00" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

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.

It sounds like you may already have a solution, but I do something fairly similar. I have a macro that I wrote that takes opens individual process logs from a piece of semiconductor machinery (gas flows, pressure, RF power, etc.) and fills out a master log for easy referance and monitoring. The logs are saved in a CSV format, which I open and re-format. It takes a couple of minutes to run through ~100 or so logs, but sense they are all individual files, I don't know of another way to get the data. I use this line to open the files:

If Len(Dir$(MyPath & "\" & MyFile)) > 0 Then

Workbooks.OpenText Filename:=MyPath & "\" & MyFile 'Opens the file

Then I store the data I need in an array, close that file, open the next file, so on and so forth, and the put all the data in the master log. It looks like this is pretty much the same as the previous suggestion only with an error check that won't stop your macro if the file isn't there. Enjoy

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

You are going to play what?? WoW what's that?
Nick
"Magius00" <[email protected]> wrote in message
news:[email protected]...
> 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.