Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
Dean: I trying to minimize the risk of breaking the code that is already
working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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? I t looked like the line wrapped. The underscore "_" should be on the same line as the = and False should be on the next line. the posting sometimes get extra lines if the lines are too long. I did not modify these lines. Look at your original code and enter these lines like they were originally enetered. 6) 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 I did not modify these lines. It looks like the lines wrapped and should be one line. Look at the original code and enter these lines like they appeared in the original code. -------------------------------------------------------------------------------------------- "Dean" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
The old code was/is working fine, so the problem is in the new code. I
fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. Can you kindly answer these questions and rewrite Consolidate ALL so that it automates both the filename and the worksheet name? Thanks much for your help! Dean "Joel" wrote in message ... Dean: I trying to minimize the risk of breaking the code that is already working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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? I t looked like the line wrapped. The underscore "_" should be on the same line as the = and False should be on the next line. the posting sometimes get extra lines if the lines are too long. I did not modify these lines. Look at your original code and enter these lines like they were originally enetered. 6) 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 I did not modify these lines. It looks like the lines wrapped and should be one line. Look at the original code and enter these lines like they appeared in the original code. -------------------------------------------------------------------------------------------- "Dean" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
1) The old code was/is working fine, so the problem is in the new code. I
fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Is did reverse order. sorry 2) Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 New code above is using your suggestion 3) Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else Your original code had the function below which may be causing part of your problems. There is a names range called "projectname". I think you can see it in the worksheet menu Insert - Name - Define if it is not in the above try File - Properties Let me know which one it is located. There are few ways of defining ranges and I can't tell from the code which method is being used. ---------------------------------------------------------------------- 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 --------------------------------------------------------------------- 4) If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else This code is a little different from Load_Consolidate_Dialog. It doesn't use a named range instead it is actuall using a cell location in worksheet utility. Range("I55"). Notice the double quotes around the range which indicates this is a string. An ampersand (&) connects string together and converts a number (n) to a string. So if N = 55 (N is a number), Range("I" & N) is the same as Range("I55"). VBA converted automaitcally the number 55 to a string "55". 5) in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. B = A does mean assign A's value to B. Not sure why you are interpreting this backwards. It may be the IF statement If Sheets("Utility").Range("I" & (n + 40)) "" then The above is a test check if the string on sheet utility Range "I?" (? is number) is not a blank string. I prefer not to use but use < (not equal) If Sheets("Utility").Range("I" & (n + 40)) < "" then The statement below is the assignment if the string is not empty Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) New Consolidate All I added a messgebox to help get it working. You can remove the message box after we get the code working. Check the placement of the spaces in the msgbox. You had "test60" and "test 60". Not sure which is right. I add a blank before the number (" "). Remove the blank and one of the ampersands if it is wrong from both statements below. chr(13) put a carraige return into the messbox to make the message easier to read. -------------------------------------------------------------------------------- Sub Consolidate_All() Dim n As Integer mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" sht = "joe jones" files = "test" For n = 1 To 60 PrjName = sht & " " & n ProjectFile = mypath & sht & files & " " & n & ".xls" 'remove msgbox after code works msgbox("File and project name are as follows : " & chr(13) & _ ProjectFile & chr(13) & PrjName) Call Consolidate(ProjectFile,PrjName) Next n End Sub "Dean" wrote: The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. Can you kindly answer these questions and rewrite Consolidate ALL so that it automates both the filename and the worksheet name? Thanks much for your help! Dean "Joel" wrote in message ... Dean: I trying to minimize the risk of breaking the code that is already working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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? I t looked like the line wrapped. The underscore "_" should be on the same line as the = and False should be on the next line. the posting sometimes get extra lines if the lines are too long. I did not modify these lines. Look at your original code and enter these lines like they were originally enetered. 6) 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 I did not modify these lines. It looks like the lines wrapped and should be one line. Look at the original code and enter these lines like they appeared in the original code. -------------------------------------------------------------------------------------------- "Dean" wrote: 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:= |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
The project names were, indeed, range names. I'm not sure that is
important. The new macro is crashing (subscript out of range) at: Sheets(Prj_Sht).Activate I'm not sure why. The filename and sheet name look fine. It seems that we are now skipping the Consolidate Project macro and, perhaps, it was the last part of that macro that was defining what Prj_Sht is. Which brings me to that question that confuses you on the B = A thing: I can't figure out how column I of Utility gets populated. I know that, somehow, when the old macro ran, it did, but I can't figure out how. By the way, if a macro crashes at a certain point, does everything before that point get done, or does it, somehow, not do anything? The cell on Utility is not getting populated - I can tell you that. Thanks! Dean "Joel" wrote in message ... 1) The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Is did reverse order. sorry 2) Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 New code above is using your suggestion 3) Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else Your original code had the function below which may be causing part of your problems. There is a names range called "projectname". I think you can see it in the worksheet menu Insert - Name - Define if it is not in the above try File - Properties Let me know which one it is located. There are few ways of defining ranges and I can't tell from the code which method is being used. ---------------------------------------------------------------------- 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 --------------------------------------------------------------------- 4) If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else This code is a little different from Load_Consolidate_Dialog. It doesn't use a named range instead it is actuall using a cell location in worksheet utility. Range("I55"). Notice the double quotes around the range which indicates this is a string. An ampersand (&) connects string together and converts a number (n) to a string. So if N = 55 (N is a number), Range("I" & N) is the same as Range("I55"). VBA converted automaitcally the number 55 to a string "55". 5) in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. B = A does mean assign A's value to B. Not sure why you are interpreting this backwards. It may be the IF statement If Sheets("Utility").Range("I" & (n + 40)) "" then The above is a test check if the string on sheet utility Range "I?" (? is number) is not a blank string. I prefer not to use but use < (not equal) If Sheets("Utility").Range("I" & (n + 40)) < "" then The statement below is the assignment if the string is not empty Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) New Consolidate All I added a messgebox to help get it working. You can remove the message box after we get the code working. Check the placement of the spaces in the msgbox. You had "test60" and "test 60". Not sure which is right. I add a blank before the number (" "). Remove the blank and one of the ampersands if it is wrong from both statements below. chr(13) put a carraige return into the messbox to make the message easier to read. -------------------------------------------------------------------------------- Sub Consolidate_All() Dim n As Integer mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" sht = "joe jones" files = "test" For n = 1 To 60 PrjName = sht & " " & n ProjectFile = mypath & sht & files & " " & n & ".xls" 'remove msgbox after code works msgbox("File and project name are as follows : " & chr(13) & _ ProjectFile & chr(13) & PrjName) Call Consolidate(ProjectFile,PrjName) Next n End Sub "Dean" wrote: The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. Can you kindly answer these questions and rewrite Consolidate ALL so that it automates both the filename and the worksheet name? Thanks much for your help! Dean "Joel" wrote in message ... Dean: I trying to minimize the risk of breaking the code that is already working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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? I t looked like the line wrapped. The underscore "_" should be on the same line as the = and False should be on the next line. the posting sometimes get extra lines if the lines are too long. I did not modify these lines. Look at your original code and enter these lines like they were originally enetered. 6) 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 I did not modify these lines. It looks like the lines wrapped and should be one line. Look at the original code and enter these lines like they appeared in the original code. -------------------------------------------------------------------------------------------- "Dean" wrote: 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:= |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
Dean: Last night I tried to take the approach that would havve the best
changge of working. I guess I failed.. I susspect you are getting error 1004 which indicates the workshhet was not found. I wondering if the code is failing when n = 1 1 or at some higher value of N. The code your have posted wqs checking for projects that havve empty strings. Maybe it is failing bacause of a project that doesn't exist? 1) First try this change from Call Consolidate(ProjectFile,PrjName) to Thisworkbook.activate Call Consolidate(ProjectFile,PrjName) The codee may be working for the first project and not the second. When a workbook opens occurs it changes the focus window to the newly opened workbook. the coede may be failing because the workbook that contains the worksheett Utility is not activated. The real fix would be to change the statement that is failing but againn I don't want to risk breaking working code from Sheets(Prj_Sht).Activate to Thisworkbook.Sheets(Prj_Sht).Activate 2) Try adding amessage box before this line of code and run the old code and see what you get. compare the results with the old code and new code from Sheets(Prj_Sht).Activate to msgbox(Prj_Sht) Sheets(Prj_Sht).Activate 3) Look at the utility worksheet in columns G and I like the code below 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 Code that checks if the project exists. this is more complicated and dfon't really want you to try this until we get the other code working. We may have to use it to skip projects that don't exist. Sub Consolidate_All() Dim n As Integer MyPath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Files = "test" For n = 1 To 60 ThisWorkbook.Activate 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 If Prj_Sht < "" Then ProjectFile = MyPath & Prj_Sht & Files & n & ".xls" 'remove msgbox after code works MsgBox ("File and project name are as follows : " & Chr(13) & _ ProjectFile & Chr(13) & Prj_Sht) Call Consolidate(ProjectFile, Prj_Sht) End If Next n End Sub Last night I first stated writing the code like the code below but felt It would be harder to get working. Maybe we need to change the code. "Dean" wrote: The project names were, indeed, range names. I'm not sure that is important. The new macro is crashing (subscript out of range) at: Sheets(Prj_Sht).Activate I'm not sure why. The filename and sheet name look fine. It seems that we are now skipping the Consolidate Project macro and, perhaps, it was the last part of that macro that was defining what Prj_Sht is. Which brings me to that question that confuses you on the B = A thing: I can't figure out how column I of Utility gets populated. I know that, somehow, when the old macro ran, it did, but I can't figure out how. By the way, if a macro crashes at a certain point, does everything before that point get done, or does it, somehow, not do anything? The cell on Utility is not getting populated - I can tell you that. Thanks! Dean "Joel" wrote in message ... 1) The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Is did reverse order. sorry 2) Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 New code above is using your suggestion 3) Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else Your original code had the function below which may be causing part of your problems. There is a names range called "projectname". I think you can see it in the worksheet menu Insert - Name - Define if it is not in the above try File - Properties Let me know which one it is located. There are few ways of defining ranges and I can't tell from the code which method is being used. ---------------------------------------------------------------------- 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 --------------------------------------------------------------------- 4) If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else This code is a little different from Load_Consolidate_Dialog. It doesn't use a named range instead it is actuall using a cell location in worksheet utility. Range("I55"). Notice the double quotes around the range which indicates this is a string. An ampersand (&) connects string together and converts a number (n) to a string. So if N = 55 (N is a number), Range("I" & N) is the same as Range("I55"). VBA converted automaitcally the number 55 to a string "55". 5) in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. B = A does mean assign A's value to B. Not sure why you are interpreting this backwards. It may be the IF statement If Sheets("Utility").Range("I" & (n + 40)) "" then The above is a test check if the string on sheet utility Range "I?" (? is number) is not a blank string. I prefer not to use but use < (not equal) If Sheets("Utility").Range("I" & (n + 40)) < "" then The statement below is the assignment if the string is not empty Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) New Consolidate All I added a messgebox to help get it working. You can remove the message box after we get the code working. Check the placement of the spaces in the msgbox. You had "test60" and "test 60". Not sure which is right. I add a blank before the number (" "). Remove the blank and one of the ampersands if it is wrong from both statements below. chr(13) put a carraige return into the messbox to make the message easier to read. -------------------------------------------------------------------------------- Sub Consolidate_All() Dim n As Integer mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" sht = "joe jones" files = "test" For n = 1 To 60 PrjName = sht & " " & n ProjectFile = mypath & sht & files & " " & n & ".xls" 'remove msgbox after code works msgbox("File and project name are as follows : " & chr(13) & _ ProjectFile & chr(13) & PrjName) Call Consolidate(ProjectFile,PrjName) Next n End Sub "Dean" wrote: The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. Can you kindly answer these questions and rewrite Consolidate ALL so that it automates both the filename and the worksheet name? Thanks much for your help! Dean "Joel" wrote in message ... Dean: I trying to minimize the risk of breaking the code that is already working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help end the testing tedium please
I will try some of this. I wanted to let you know that I changed the 60 to
4, because I wanted to see the result with only 4 before I bothered to create 60 files, which is tedious. And since your message box only popped up once, and had the correct info for the first file, I assume it failed on the first file. You didn't answer my questions as to whether, if a macro bombs, if the stuff that before the bomb stays put, or if it somehow is reset back to the beginning. I don't see that anything in column I of Utility is being filled in. More importantly, I was hoping you might be able to tell me how that column I of Utility gets populated. It seems that, perhaps, it is getting populated via the Consolidate Project macro and we seem to have circumvented that macro, but I'm not even sure that's where it happens. To me, it seems that (my "Then B = A "argument) Prj_Sht is set equal to what's in column I, not the other way around, and I'm still wondering how column I gets populated. When the macro starts, it is empty - I can tell you that. Do you have any idea? I think that is the problem. Dean "Joel" wrote in message ... Dean: Last night I tried to take the approach that would havve the best changge of working. I guess I failed.. I susspect you are getting error 1004 which indicates the workshhet was not found. I wondering if the code is failing when n = 1 1 or at some higher value of N. The code your have posted wqs checking for projects that havve empty strings. Maybe it is failing bacause of a project that doesn't exist? 1) First try this change from Call Consolidate(ProjectFile,PrjName) to Thisworkbook.activate Call Consolidate(ProjectFile,PrjName) The codee may be working for the first project and not the second. When a workbook opens occurs it changes the focus window to the newly opened workbook. the coede may be failing because the workbook that contains the worksheett Utility is not activated. The real fix would be to change the statement that is failing but againn I don't want to risk breaking working code from Sheets(Prj_Sht).Activate to Thisworkbook.Sheets(Prj_Sht).Activate 2) Try adding amessage box before this line of code and run the old code and see what you get. compare the results with the old code and new code from Sheets(Prj_Sht).Activate to msgbox(Prj_Sht) Sheets(Prj_Sht).Activate 3) Look at the utility worksheet in columns G and I like the code below 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 Code that checks if the project exists. this is more complicated and dfon't really want you to try this until we get the other code working. We may have to use it to skip projects that don't exist. Sub Consolidate_All() Dim n As Integer MyPath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Files = "test" For n = 1 To 60 ThisWorkbook.Activate 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 If Prj_Sht < "" Then ProjectFile = MyPath & Prj_Sht & Files & n & ".xls" 'remove msgbox after code works MsgBox ("File and project name are as follows : " & Chr(13) & _ ProjectFile & Chr(13) & Prj_Sht) Call Consolidate(ProjectFile, Prj_Sht) End If Next n End Sub Last night I first stated writing the code like the code below but felt It would be harder to get working. Maybe we need to change the code. "Dean" wrote: The project names were, indeed, range names. I'm not sure that is important. The new macro is crashing (subscript out of range) at: Sheets(Prj_Sht).Activate I'm not sure why. The filename and sheet name look fine. It seems that we are now skipping the Consolidate Project macro and, perhaps, it was the last part of that macro that was defining what Prj_Sht is. Which brings me to that question that confuses you on the B = A thing: I can't figure out how column I of Utility gets populated. I know that, somehow, when the old macro ran, it did, but I can't figure out how. By the way, if a macro crashes at a certain point, does everything before that point get done, or does it, somehow, not do anything? The cell on Utility is not getting populated - I can tell you that. Thanks! Dean "Joel" wrote in message ... 1) The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Is did reverse order. sorry 2) Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 New code above is using your suggestion 3) Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else Your original code had the function below which may be causing part of your problems. There is a names range called "projectname". I think you can see it in the worksheet menu Insert - Name - Define if it is not in the above try File - Properties Let me know which one it is located. There are few ways of defining ranges and I can't tell from the code which method is being used. ---------------------------------------------------------------------- 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 --------------------------------------------------------------------- 4) If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else This code is a little different from Load_Consolidate_Dialog. It doesn't use a named range instead it is actuall using a cell location in worksheet utility. Range("I55"). Notice the double quotes around the range which indicates this is a string. An ampersand (&) connects string together and converts a number (n) to a string. So if N = 55 (N is a number), Range("I" & N) is the same as Range("I55"). VBA converted automaitcally the number 55 to a string "55". 5) in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. B = A does mean assign A's value to B. Not sure why you are interpreting this backwards. It may be the IF statement If Sheets("Utility").Range("I" & (n + 40)) "" then The above is a test check if the string on sheet utility Range "I?" (? is number) is not a blank string. I prefer not to use but use < (not equal) If Sheets("Utility").Range("I" & (n + 40)) < "" then The statement below is the assignment if the string is not empty Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) New Consolidate All I added a messgebox to help get it working. You can remove the message box after we get the code working. Check the placement of the spaces in the msgbox. You had "test60" and "test 60". Not sure which is right. I add a blank before the number (" "). Remove the blank and one of the ampersands if it is wrong from both statements below. chr(13) put a carraige return into the messbox to make the message easier to read. -------------------------------------------------------------------------------- Sub Consolidate_All() Dim n As Integer mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" sht = "joe jones" files = "test" For n = 1 To 60 PrjName = sht & " " & n ProjectFile = mypath & sht & files & " " & n & ".xls" 'remove msgbox after code works msgbox("File and project name are as follows : " & chr(13) & _ ProjectFile & chr(13) & PrjName) Call Consolidate(ProjectFile,PrjName) Next n End Sub "Dean" wrote: The old code was/is working fine, so the problem is in the new code. I fixed the wrap around problem as you advised. I am a little confused by the two arguments needed for "Consolidate". When you call it from Consolidate All, it seems that you may be reversing their order from what Consolidate wants. Is that correct? Considering the above, I am also a bit confused by your suggestion fro automating the assumed filename, perhaps because you have copied the routine to name the project sheet. Please assume that both the filename and the worksheet name are sequential. Let's say the filenames are test1 thru test60 and that the sheet names will be joe jones test1 thru joe jones test 60 Lastly, though it is not essential info for me to understand, I know that a worksheet called Utility somehow captures the sheet names, but am confused as to how it gets them. There are statements such as If Sheets("Utility").Range("I" & (n + 40)) "" Then Prj_Sht = Sheets("Utility").Range("I" & (n + 40)) Else in the macro which are probably doing it, but I think I am confused because I am still interpreting them backwards. When I see "Then B = A", I assume that it means to assign A's value to B. But I am guessing that this is just the opposite. If so, I guess you have answered my question. If not, then if you have any clue as to how it is finding its way onto the Utility worksheet, let me know (it may be off somewhere in another macro). I do know which cell in each input file becomes Prj_Sht. I just am a little confused as to where the macro puts it. Can you kindly answer these questions and rewrite Consolidate ALL so that it automates both the filename and the worksheet name? Thanks much for your help! Dean "Joel" wrote in message ... Dean: I trying to minimize the risk of breaking the code that is already working. I don't think you have posted all the code which I don't need but don't want to break. There is probably code on the sheet pages and Thisworkbook that you are not aware of. Lets not worry about that code. Lets just get your test function working. From your psting I'm not sure if the old code still works or the problem is the new code. right now I'm assuming the new code had problems. I modified my code to include the path Sub Consolidate_All() mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files\" Dim n As Integer ProjectFile = Array("","Filename1","Filename2",.........) For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & ProjectFile(n)) Next n End Sub Answer to questions -------------------------------------------------------------------------------------------- 1) in the array, do the filenames need the .xls extension? Yes 2) 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? The are lots of ways to get the filename. The array method will work ynder any condition. If there is a pattern to the file name then the array may not be necessary such like the code below For n = 1 To 60 ProjectName = Range("projectName" & (n)).Value Call Consolidate(ProjectName, mypath & "test" & n) Next n 3) 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? The new code I gave you now includes the path 4) 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? As I said before, There are lots of ways to get the files names depending if there is a pattern to the names or if they are random. I wanted yo to see what I was doing and then expected exactly the questions you are asking. the additional work is what I'm doping now which is helping you find the best way to get the filenames. I hope I'm not confusing you. I felt it was better not to give you too much information until you saw the code. the I planed to answer the questions you needed answered. 5) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing | Excel Discussion (Misc queries) | |||
TESTING | Excel Worksheet Functions | |||
testing..... | Excel Programming | |||
testing for #NA with VBA | Excel Programming | |||
just testing | Excel Programming |