Loop through files

  • Thread starter Thread starter David
  • Start date Start date
D

David

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,

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
 
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.
Click to expand...

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,


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
 
Charles < wrote
Ok next suggestion. Create a Userform to open selected files.
On file 4 create a CommandButton to start the Userform.
Click to expand...

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
Click to expand...

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.
Click to expand...

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,

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
 
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
Click to expand...

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.
 
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
 
Charles < wrote
Ok, lets do it this way.

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

Undesired side effect of this method is that file3 has Workbook_Open() code
that prompts the user to answer a question via MsgBox, so the user has to
answer it before the other two files open :(

I didn't think there was an easy way to accomplish what I want when I first
decided to pose the question.
 
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
 
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.
Click to expand...

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.
 
Charles < wrote
my suggestions should work.
Click to expand...

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.
 
Back
Top