I have an extremely complex template, its ancestry traced to another author,
that I need to test extensively, often, as I add features to it. It can
handle up to 60 projects and, when I test it, I would like to test it with
all 60 used. I will use 60 imported data files called "Project 1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and process the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow populated
more) is presented. It already was designed by another long before I was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says "Select
File", which opens up a dialog box allowing me to manually navigate to and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume it is
the default (last used) folder), I click the open button on that dialog box,
which causes it to import the file and do its business, and then the dialog
box goes away.

(4) Next, I click a rectangular button on the form titled "Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60 using
files named Project 2test.xls thru Project 60test.xls", all in the same
folder.



Can someone give me VB code that would cycle thru this for all 60 projects?



Thanks much!

Dean



PS For what it's worth, I tried recording the process of clicking on a
macro button to bring up the "form" and doing these 4 steps but all that was
recorded was:



Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/20/2007 by Dean


Application.Run "'CPT-10-19-07 DM-3.xls'!Load_Consolidate_Dialog"
End Sub

The option buttons are the 60 blank little circles. When you select one of
these buttons it is running a macro that is using GetOpenFilename.

What you need to do is create a table of project numbers and filenames.
This can easily be done using an Array statement in VBA

Filenames = Array("Name1","Name2","Name3").

You could also have a base name for each file with a diffferent number added
for each input.

What you really need to do is to post the old code at this website because
you need a new macro. You can't just have a new macro call the old macro
because the method of selecting the filenames will be different.

I think from your description only minor changes will be needed, but it will
make the operation fully automatic.

"Dean" wrote:

I have an extremely complex template, its ancestry traced to another author,
that I need to test extensively, often, as I add features to it. It can
handle up to 60 projects and, when I test it, I would like to test it with
all 60 used. I will use 60 imported data files called "Project 1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and process the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow populated
more) is presented. It already was designed by another long before I was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says "Select
File", which opens up a dialog box allowing me to manually navigate to and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume it is
the default (last used) folder), I click the open button on that dialog box,
which causes it to import the file and do its business, and then the dialog
box goes away.

(4) Next, I click a rectangular button on the form titled "Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60 using
files named Project 2test.xls thru Project 60test.xls", all in the same
folder.



Can someone give me VB code that would cycle thru this for all 60 projects?



Thanks much!

Dean



PS For what it's worth, I tried recording the process of clicking on a
macro button to bring up the "form" and doing these 4 steps but all that was
recorded was:



Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/20/2007 by Dean


Application.Run "'CPT-10-19-07 DM-3.xls'!Load_Consolidate_Dialog"
End Sub

Thanks so much for your help. I'm not sure I know everything that goes on
behind the scenes, so I may err on the side of giving you more than you
need. The first subroutine loads the dialog screen. From looking at the
form, this seems to simply populate the labels beside the 60 buttons. I
have a hunch you don't need it but here that is:

Sub Load_Consolidate_Dialog()
'If this bombs, you may have to clear out the names in the hidden "utility"
worksheet
Dim n As Integer
For n = 1 To 60
If Range("projectName" & (n)).Value "" Then
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Else
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= "Project " & (n)
End If
Next n
Consolidate_Dialog.Show
End Sub

It has several buttons on it, such as clear selected projects but I don't
use them, so I only care about the two buttons I mentioned.

From what you said, I assume that the select file button invokes this macro
(but I may be mistaken - it's always an EXCEL file):

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Lastly, the consolidate button uses this macro (which may be a nightmare to
follow, as even I have had my hand in adding to it):

Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
ConFile = FileNameOnly(FileName)
'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

'
'Perform the Consolidation
'
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

Let me know if you need more. And thanks again, so much.

Dean





"Joel" wrote in message
...
The option buttons are the 60 blank little circles. When you select one
of
these buttons it is running a macro that is using GetOpenFilename.

What you need to do is create a table of project numbers and filenames.
This can easily be done using an Array statement in VBA

Filenames = Array("Name1","Name2","Name3").

You could also have a base name for each file with a diffferent number
added
for each input.

What you really need to do is to post the old code at this website because
you need a new macro. You can't just have a new macro call the old macro
because the method of selecting the filenames will be different.

I think from your description only minor changes will be needed, but it
will
make the operation fully automatic.

"Dean" wrote:

I have an extremely complex template, its ancestry traced to another
author,
that I need to test extensively, often, as I add features to it. It can
handle up to 60 projects and, when I test it, I would like to test it
with
all 60 used. I will use 60 imported data files called "Project
1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and process
the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like
some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow
populated
more) is presented. It already was designed by another long before I was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says
"Select
File", which opens up a dialog box allowing me to manually navigate to
and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume it
is
the default (last used) folder), I click the open button on that dialog
box,
which causes it to import the file and do its business, and then the
dialog
box goes away.

(4) Next, I click a rectangular button on the form titled "Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60
using
files named Project 2test.xls thru Project 60test.xls", all in the same
folder.



Can someone give me VB code that would cycle thru this for all 60
projects?



Thanks much!

Dean



PS For what it's worth, I tried recording the process of clicking on a
macro button to bring up the "form" and doing these 4 steps but all that
was
recorded was:



Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/20/2007 by Dean


Application.Run "'CPT-10-19-07 DM-3.xls'!Load_Consolidate_Dialog"
End Sub

Dean: I split Consololidate Project into two subroutines. Just moved around
a little bit of the code and created a new subroutine consolidate. My plan
is to just call consolidate for testing.

You will need a new routine that will look something like Consolidate_All.
Notice I put a blank string at the beginning of the Array to skip the first
array member which has an index of zero.

I think you may want to pass the new function the Path as a third parameter.
I will make the ProjectFile Array easier to enter the names (they will be
shorter).

Let me know if you need additional help.

Sub Consolidate_All()
Dim n As Integer
ProjectFile = Array("","Filename1","Filename2",.........)
For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName,ProjectFile(n))
Next n
End Sub



Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False

'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

call Consolidate(FileName, Prj_Sht)
end sub


Sub Consolidate(ByVal FileName as String, ByVal Prj_Sht as String)
'
'Perform the Consolidation
'
'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

ConFile = FileNameOnly(FileName)
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

"Dean" wrote:

Thanks so much for your help. I'm not sure I know everything that goes on
behind the scenes, so I may err on the side of giving you more than you
need. The first subroutine loads the dialog screen. From looking at the
form, this seems to simply populate the labels beside the 60 buttons. I
have a hunch you don't need it but here that is:

Sub Load_Consolidate_Dialog()
'If this bombs, you may have to clear out the names in the hidden "utility"
worksheet
Dim n As Integer
For n = 1 To 60
If Range("projectName" & (n)).Value "" Then
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Else
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= "Project " & (n)
End If
Next n
Consolidate_Dialog.Show
End Sub

It has several buttons on it, such as clear selected projects but I don't
use them, so I only care about the two buttons I mentioned.

From what you said, I assume that the select file button invokes this macro
(but I may be mistaken - it's always an EXCEL file):

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Lastly, the consolidate button uses this macro (which may be a nightmare to
follow, as even I have had my hand in adding to it):

Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
ConFile = FileNameOnly(FileName)
'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

'
'Perform the Consolidation
'
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

Let me know if you need more. And thanks again, so much.

Dean





"Joel" wrote in message
...
The option buttons are the 60 blank little circles. When you select one
of
these buttons it is running a macro that is using GetOpenFilename.

What you need to do is create a table of project numbers and filenames.
This can easily be done using an Array statement in VBA

Filenames = Array("Name1","Name2","Name3").

You could also have a base name for each file with a diffferent number
added
for each input.

What you really need to do is to post the old code at this website because
you need a new macro. You can't just have a new macro call the old macro
because the method of selecting the filenames will be different.

I think from your description only minor changes will be needed, but it
will
make the operation fully automatic.

"Dean" wrote:

I have an extremely complex template, its ancestry traced to another
author,
that I need to test extensively, often, as I add features to it. It can
handle up to 60 projects and, when I test it, I would like to test it
with
all 60 used. I will use 60 imported data files called "Project
1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and process
the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like
some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow
populated
more) is presented. It already was designed by another long before I was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says
"Select
File", which opens up a dialog box allowing me to manually navigate to
and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume it
is
the default (last used) folder), I click the open button on that dialog
box,
which causes it to import the file and do its business, and then the
dialog
box goes away.

(4) Next, I click a rectangular button on the form titled "Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60
using
files named Project 2test.xls thru Project 60test.xls", all in the same
folder.

Unfortunately, I am not too swift with macros, so I'm a tad confused.

Within the array, do the filenames need the .xls extension? If the names of
the file really were test1 thru test60, would the array statement need them
all listed or is it smart enough for a ... to work? By path, I assume you
mean the path to the folder where the files will be. If so, I think we can
ignore that for now, i.e., as long as the last time it was queried, we
supplied the same folder, it will use that folder as a default, right?

When you say:

I will make the ProjectFile Array easier to enter the names (they will be
shorter).

I don't know what you mean. Are you planning on doing some more work?

I tried copying your macro into my file, just using, test1, test2, and test3
as the three project names in the array and I notice that, every time the

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

command shows up, it is in red font. What could be the problem there?

Also, in red font is these two msgbox commands:

MsgBox ("The selected project was successfully consolidated."),

MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical

Thanks!
Dean

"Joel" wrote in message
...
Dean: I split Consololidate Project into two subroutines. Just moved
around
a little bit of the code and created a new subroutine consolidate. My
plan
is to just call consolidate for testing.

You will need a new routine that will look something like Consolidate_All.
Notice I put a blank string at the beginning of the Array to skip the
first
array member which has an index of zero.

I think you may want to pass the new function the Path as a third
parameter.
I will make the ProjectFile Array easier to enter the names (they will be
shorter).

Let me know if you need additional help.

Sub Consolidate_All()
Dim n As Integer
ProjectFile = Array("","Filename1","Filename2",.........)
For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName,ProjectFile(n))
Next n
End Sub



Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False

'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

call Consolidate(FileName, Prj_Sht)
end sub


Sub Consolidate(ByVal FileName as String, ByVal Prj_Sht as String)
'
'Perform the Consolidation
'
'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

ConFile = FileNameOnly(FileName)
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

"Dean" wrote:

Thanks so much for your help. I'm not sure I know everything that goes
on
behind the scenes, so I may err on the side of giving you more than you
need. The first subroutine loads the dialog screen. From looking at the
form, this seems to simply populate the labels beside the 60 buttons. I
have a hunch you don't need it but here that is:

Sub Load_Consolidate_Dialog()
'If this bombs, you may have to clear out the names in the hidden
"utility"
worksheet
Dim n As Integer
For n = 1 To 60
If Range("projectName" & (n)).Value "" Then
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Else
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= "Project " & (n)
End If
Next n
Consolidate_Dialog.Show
End Sub

It has several buttons on it, such as clear selected projects but I don't
use them, so I only care about the two buttons I mentioned.

From what you said, I assume that the select file button invokes this
macro
(but I may be mistaken - it's always an EXCEL file):

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Lastly, the consolidate button uses this macro (which may be a nightmare
to
follow, as even I have had my hand in adding to it):

Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
ConFile = FileNameOnly(FileName)
'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

'
'Perform the Consolidation
'
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

Let me know if you need more. And thanks again, so much.

Dean





"Joel" wrote in message
...
The option buttons are the 60 blank little circles. When you select
one
of
these buttons it is running a macro that is using GetOpenFilename.

What you need to do is create a table of project numbers and filenames.
This can easily be done using an Array statement in VBA

Filenames = Array("Name1","Name2","Name3").

You could also have a base name for each file with a diffferent number
added
for each input.

What you really need to do is to post the old code at this website
because
you need a new macro. You can't just have a new macro call the old
macro
because the method of selecting the filenames will be different.

I think from your description only minor changes will be needed, but it
will
make the operation fully automatic.

"Dean" wrote:

I have an extremely complex template, its ancestry traced to another
author,
that I need to test extensively, often, as I add features to it. It
can
handle up to 60 projects and, when I test it, I would like to test it
with
all 60 used. I will use 60 imported data files called "Project
1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and process
the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like
some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow
populated
more) is presented. It already was designed by another long before I
was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says
"Select
File", which opens up a dialog box allowing me to manually navigate to
and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume
it
is
the default (last used) folder), I click the open button on that
dialog
box,
which causes it to import the file and do its business, and then the
dialog
box goes away.

(4) Next, I click a rectangular button on the form titled
"Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which
you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60
using
files named Project 2test.xls thru Project 60test.xls", all in the
same
folder.

I think I figured out that you meant "It", not "I", below, Can you modify
the code to use a path? Assume the path is:

C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files

Dean

"Dean" wrote in message
...
Unfortunately, I am not too swift with macros, so I'm a tad confused.

Within the array, do the filenames need the .xls extension? If the names
of the file really were test1 thru test60, would the array statement need
them all listed or is it smart enough for a ... to work? By path, I
assume you mean the path to the folder where the files will be. If so, I
think we can ignore that for now, i.e., as long as the last time it was
queried, we supplied the same folder, it will use that folder as a
default, right?

When you say:

I will make the ProjectFile Array easier to enter the names (they will be
shorter).

I don't know what you mean. Are you planning on doing some more work?

I tried copying your macro into my file, just using, test1, test2, and
test3 as the three project names in the array and I notice that, every
time the

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

command shows up, it is in red font. What could be the problem there?

Also, in red font is these two msgbox commands:

MsgBox ("The selected project was successfully consolidated."),

MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical

Thanks!
Dean

"Joel" wrote in message
...
Dean: I split Consololidate Project into two subroutines. Just moved
around
a little bit of the code and created a new subroutine consolidate. My
plan
is to just call consolidate for testing.

You will need a new routine that will look something like
Consolidate_All.
Notice I put a blank string at the beginning of the Array to skip the
first
array member which has an index of zero.

I think you may want to pass the new function the Path as a third
parameter.
I will make the ProjectFile Array easier to enter the names (they will be
shorter).

Let me know if you need additional help.

Sub Consolidate_All()
Dim n As Integer
ProjectFile = Array("","Filename1","Filename2",.........)
For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName,ProjectFile(n))
Next n
End Sub



Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False

'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

call Consolidate(FileName, Prj_Sht)
end sub


Sub Consolidate(ByVal FileName as String, ByVal Prj_Sht as String)
'
'Perform the Consolidation
'
'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

ConFile = FileNameOnly(FileName)
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

"Dean" wrote:

Thanks so much for your help. I'm not sure I know everything that goes
on
behind the scenes, so I may err on the side of giving you more than you
need. The first subroutine loads the dialog screen. From looking at
the
form, this seems to simply populate the labels beside the 60 buttons. I
have a hunch you don't need it but here that is:

Sub Load_Consolidate_Dialog()
'If this bombs, you may have to clear out the names in the hidden
"utility"
worksheet
Dim n As Integer
For n = 1 To 60
If Range("projectName" & (n)).Value "" Then
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Else
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= "Project " & (n)
End If
Next n
Consolidate_Dialog.Show
End Sub

It has several buttons on it, such as clear selected projects but I
don't
use them, so I only care about the two buttons I mentioned.

From what you said, I assume that the select file button invokes this
macro
(but I may be mistaken - it's always an EXCEL file):

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Lastly, the consolidate button uses this macro (which may be a nightmare
to
follow, as even I have had my hand in adding to it):

Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
ConFile = FileNameOnly(FileName)
'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value = True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

'
'Perform the Consolidation
'
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" & (n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

Let me know if you need more. And thanks again, so much.

Dean





"Joel" wrote in message
...
The option buttons are the 60 blank little circles. When you select
one
of
these buttons it is running a macro that is using GetOpenFilename.

What you need to do is create a table of project numbers and
filenames.
This can easily be done using an Array statement in VBA

Filenames = Array("Name1","Name2","Name3").

You could also have a base name for each file with a diffferent number
added
for each input.

What you really need to do is to post the old code at this website
because
you need a new macro. You can't just have a new macro call the old
macro
because the method of selecting the filenames will be different.

I think from your description only minor changes will be needed, but
it
will
make the operation fully automatic.

"Dean" wrote:

I have an extremely complex template, its ancestry traced to another
author,
that I need to test extensively, often, as I add features to it. It
can
handle up to 60 projects and, when I test it, I would like to test it
with
all 60 used. I will use 60 imported data files called "Project
1test.xls"
thru Project 60test.xls".



Presently, you have to repeat the same procedure to import and
process
the
60 files and, though it is simple and cute, for all 60 files, it is
dreadfully tedious, so much so that you can mess it up. So, I'd like
some
help in automating the procedure.



Assume that a custom screen (a "form" in VB editor that is somehow
populated
more) is presented. It already was designed by another long before I
was
asked to augment the template. Here is the subsequent procedu



The "form" I am presented with has 60 little blank circles which say,
"Project 1" . thru "Project 60" beside them, respectively.



(1) I click on the blank circle beside Project 1 and it fills it in.

(2) Then I click on a rectangular button on that "form" that says
"Select
File", which opens up a dialog box allowing me to manually navigate
to
and
choose an EXCEL data file for it to import.

(3) Once I find the right file, call it "Project 1test.xls" (assume
it
is
the default (last used) folder), I click the open button on that
dialog
box,
which causes it to import the file and do its business, and then the
dialog
box goes away.

(4) Next, I click a rectangular button on the form titled
"Consolidate".
About ten seconds later, it responds with "This project has been
successfully consolidated", leaving you with a dialog box upon which
you
click OK.





Then, I would repeat the exact same procedure for Projects 2 thru 60
using
files named Project 2test.xls thru Project 60test.xls", all in the
same
folder.