Loop through files
 

XL2000
Each day a user is required to work on 3 files. I would like to set up a
4th file to open each one separately, work on it and when it is closed,
open the next until work on all 3 is done. Each of the files already
contains a 'Done' button that triggers code to save and close it when
through working on it.

--
David

Loop through files
 

David,

If I'm right you do not need another file.
File 1 is open set the Done button to open file 2.
set file 2 to open fille 3 when done.
set file 3 to save and close.

The macro below was recorded. You can change the format to suit you
needs. Post it before your save and close command in file 1 and fil
2.

HTH

Charles


Workbooks.Open Filename:= _
"C:\Documents and Settings\Charles Harmon\M
Documents\Odoms.xls

--
Message posted from http://www.ExcelForum.com

Loop through files
 

Charles < wrote

If I'm right you do not need another file.
File 1 is open set the Done button to open file 2.
set file 2 to open fille 3 when done.
set file 3 to save and close.

Thought of that, but don't like files 1 or 2 open after opening the next.
That would also require returning focus to 1 or 2 to save/close.

Plus I have occasion to open these files individually, so I planned on
using the 4th file only when working on all 3 in tandem.

Thanks, anyway.

Anyone else?

--
David

Loop through files
 

David,


Ok next suggestion. Create a Userform to open selected files.
On file 4 create a CommandButton to start the Userform.

Sub Show_Userform()'this is in the Code module for sheet code
Userform1.Show
End Sub


With this you need to set your Done button to show the Userform agai
after the Save, Close. The code is userform1.show
Have the Userform with OptionButtons and 2 CommandButtons. Th
following sample is something you can work with.


Private Sub CommandButton1_Click()''Loops thru to chk value
With Userform1
If .OptionButton1.Value = True Then
Workbooks.Open Filename:="G:\Users\CC\File1"
''' Now hide the Userform''
Me.Hide
ElseIf .OptionButton2.Value = True Then
Workbooks.Open Filename:="G:\Users\CC\File2"
''' Now hide the Userform''
Me.Hide
ElseIf .OptionButton3.Value = True Then
Workbooks.Open Filename:="G:\Users\CC\File3"
''' Now hide the Userform''
Me.Hide
End If
End With
End Sub

Private Sub CommandButton2_Click()'' the Done or exit button
Unload Userform1
'' Save file''
" Close file4''
End Sub


Once again I HTH

Charle

--
Message posted from http://www.ExcelForum.com

Loop through files
 

Charles < wrote

Ok next suggestion. Create a Userform to open selected files.
On file 4 create a CommandButton to start the Userform.

I already have a custom menu loading when I start Excel that contains
entries for the individual files, so that wouldn't add to my present
capability. What I want to do is add a 4th entry to that menu that will
open a file that loads the other 3 sequentially. Interesting idea, though.

With this you need to set your Done button to show the Userform again
after the Save, Close. The code is userform1.show

I don't think a line would execute after the file was closed.

Have the Userform with OptionButtons and 2 CommandButtons. The
following sample is something you can work with.

You've put a lot of effort into trying to come up with a solution for me
and I appreciate it. Your suggestion would indeed work for what it is, but
again, it doesn't do anything beyond my present menu system.

I guess there is no batch method available to do what I want:

1) open file 1 for data entry
2) wait for user to save & close
3) open file 2 for data entry
4) wait for user to save & close
5) open file 3 for data entry

I would add this file to the top of my existing menu to process the files
in succession, or I could elect to open the files individually from the
other single menu entries.

If our systems were more powerful (more memory & faster processors), I
would write code that would just open all three at once and allow the user
operate on them. Sadly, the head organization is cheap, and such a method
would be constantly writing to the page file.

--
David

Loop through files
 

David,

One more time.

1) open file 1 for data entry

in your code for the done command try something like this

Workbooks("test1.xls").Save
Workbooks.Open Filename:= _
"C:\Documents and Settings\Charles Harmon\M
Documents\test2.xls"
Workbooks("test1.xls").Close

Do something like this for each of your Done except possibly for fil
3.

You can also do something similar to file 4

If something like this doesn't work the I'm not understanding you
delima.

HTH

Charle

--
Message posted from http://www.ExcelForum.com

Loop through files
 

Charles < wrote

in your code for the done command try something like this

Workbooks("test1.xls").Save
Workbooks.Open Filename:= _
"C:\Documents and Settings\Charles Harmon\My
Documents\test2.xls"
Workbooks("test1.xls").Close

This would happen each time Done was clicked in test1.xls, right?

My dilemma is that I wouldn't necessarily want the next file opened if user
had to (often does) open/work on files individually.

I want TWO options: 1) open/work on files in succession without having to
select the next file when Done or 2) open/work on files individually

New custom menu structure would be:
Item 1 = 'all 3'
Item 2 = 'file 1'
Item 3 = 'file 2'
Item 4 = 'file 3'

I apologize if I hadn't made this clear in prior posts.

--
David

Loop through files
 

Ok, lets do it this way.

Item 1 to open all three files open all workbooks in reverse order.

Workbooks.Open Filename:="G:\Users\CC\File3"
Workbooks.Open Filename:="G:\Users\CC\File2"
Workbooks.Open Filename:="G:\Users\CC\File1"

This was file 1 is on top user completes file1 click Done it's save
and closed. Then file 2 is on top. Ect..Ect.

Now the user can select from the Custom Menu what they want to do. I
open all 3 on diffrent workbooks.


HTH

Charle

--
Message posted from http://www.ExcelForum.com

Loop through files
 

David,

Is it necessary for the msgbox? Can the same reminder or what ever b
on the spreadsheet? If it can then my suggestions should work.


Charle

--
Message posted from http://www.ExcelForum.com

Loop through files
 

Charles < wrote

Is it necessary for the msgbox? Can the same reminder or what ever be
on the spreadsheet? If it can then my suggestions should work.

The Yes/No answer to the prompt determines which of two sheets will be
activated.

For resource reasons on low horsepower machines, I didn't want all 3 files
open at the same time.

What was once a pipe dream has turned into a nightmare <g.

Oh, well, thanks for all your help anyway.

--
David

Loop through files
 

Charles < wrote

my suggestions should work.

Without discounting all your ideas, I feel I need to say there was a reason
for asking what I asked in my original post. I had played out most if not
all of these scenarios in my mind, and 'Loop through files' was an accurate
description of what I was after given the structure of the files.

--
David