| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
savings files with the use of variables
I need a little help with a macro I've created. I currently have a form with
check boxes indicating months and fiscal years. For example a user can click the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button that automatically saves the file "September 2005 GL UPLOAD.csv" to a location that I already specified in code. I used a multiple condition "If then" statement to accomplish my task. The following code is a example of this: If CheckBox1.Value = True Then If OptionButton1.Value = True Then Sheets("54040 MONTHLY UPLOAD DATA").Select Sheets("54040 MONTHLY UPLOAD DATA").Copy ChDir _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files" ActiveWorkbook.SaveAs Filename:= _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files\September 2005 GL UPLOAD.csv" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End If End If I basically copied this code numerous times, just changing the object names and file names to make the macro work properly.The macro works fine, however, it's not the most effecient macro. I had to write the code (above) 24 times to accomadate just two fiscal years. My boss wants me to find a more effecient way of accomplishing this task. He suggested looking into declaring variables for the file names so when a user clicks a checkbox the program will automatically know the naming of the file (and where) to save the file. Can anyone help me with this, or at least point me in the right direction? I have moderate excel programming skills and limited knowledge on using variables. Any information would be appreciated -- Regards, timmulla |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
what you need to know (I suppose) is:
you can create new constantes: const toto="my name" you can add strings: example: "abc" & toto will gives "abcmy name" so you can construct complex filenames this way do remember to take care of the "\" in the name ! example: complete = folder & "\" & filename if the folder string do not already finished with a "\" you can also put the name of the file and/or folder in 1- a text box in the form 2- or in a special (config) sheet ot cell of your excel file. the point 2 can be efficient because this will gives always the good name for differents files. (the name is in the file) Sometimes when one of my workbooks need special config data I never hesitate to add a special node "config" with (in your case) the name and folder of the wanted file. But the form is ok to. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
this might also help. This is a file name I assign that is based on several
variables. Dim NameB as String NameB = "ADH" & Year & "." & month2 & "." & LDR & ".xls" I hope this helps |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
As I see it, you need to separate the variable parts of your file paths from
the repeated parts, and make a better method for selecting the differences. I will assume for demonstration purposes that the varying parts of your file paths are the part after "FY" (" 2004", " 2005", etc.) and the month+year part of the destination file name ("September 2005", "October 2005", etc.). You need to put these into arrays, for example: Dim strYear(0 to 6) As String strYear(0) = "2000" strYear(1) = "2001" .... etc., and Dim strMonth(0 To 11) As String strMonth(0) = "January" strMonth(2) = "February" .... etc. Now go through your checkboxes to create indices: Dim iYearIndex as Integer iYearIndex = -1 If chkYear0.Value = True Then iYearIndex = 0 ElseIf chkYear1.Value = True Then iYearIndex = 1 ... ElseIf chkYear6.Value = True Then iYearIndex = 6 End If Dim iMonthIndex as Integer iMonthIndex = -1 If chkMonth0.Value = True Then iMonthIndex = 0 ElseIf chkMonth1.Value = True Then iMonthIndex = 1 ... ElseIf chkMonth11.Value = True Then iMonthIndex = 11 End If (You can use the -1 default values on the indices to detect if the user didn't check any of the boxes.) Then construct your file path strings: strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY " & _ strYear(iYearIndex) & " uploaded files" strUpload = strRoot & "\" & strMonth(iMonthIndex) & " " & strYear(iYearIndex) & _ " GL UPLOAD.csv" You then can have your ChDir and Save File commands occur only once, as: ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False If you use list boxes instead of check boxes, you can generate your indices quicker, or get your month and year components directly from the choices in the listboxes, but that's up to you. -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: I need a little help with a macro I've created. I currently have a form with check boxes indicating months and fiscal years. For example a user can click the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button that automatically saves the file "September 2005 GL UPLOAD.csv" to a location that I already specified in code. I used a multiple condition "If then" statement to accomplish my task. The following code is a example of this: If CheckBox1.Value = True Then If OptionButton1.Value = True Then Sheets("54040 MONTHLY UPLOAD DATA").Select Sheets("54040 MONTHLY UPLOAD DATA").Copy ChDir _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files" ActiveWorkbook.SaveAs Filename:= _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files\September 2005 GL UPLOAD.csv" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End If End If I basically copied this code numerous times, just changing the object names and file names to make the macro work properly.The macro works fine, however, it's not the most effecient macro. I had to write the code (above) 24 times to accomadate just two fiscal years. My boss wants me to find a more effecient way of accomplishing this task. He suggested looking into declaring variables for the file names so when a user clicks a checkbox the program will automatically know the naming of the file (and where) to save the file. Can anyone help me with this, or at least point me in the right direction? I have moderate excel programming skills and limited knowledge on using variables. Any information would be appreciated -- Regards, timmulla |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
Everybody thanks for your help! LabElf, I really liked the way you wrote
your code to solve my problem, so I decided to use your method. However, after writing my code I'm getting errors pertaining to my If, ElseIf statements. The error says "compile error - Else without If. If you don't mind, and have a free moment could you please look over my code. You will also notice that I had to add another variable (program) to file path naming convention. My code is: Private Sub CommandButton1_Click() Dim strYear(0 To 5) As String strYear(0) = "2005" strYear(1) = "2006" strYear(2) = "2007" strYear(3) = "2008" strYear(4) = "2009" strYear(5) = "2010" Dim strMonth(0 To 11) As String strMonth(0) = "September" strMonth(1) = "October" strMonth(2) = "Novemeber" strMonth(3) = "December" strMonth(4) = "January" strMonth(5) = "February" strMonth(6) = "March" strMonth(7) = "April" strMonth(8) = "May" strMonth(9) = "June" strMonth(10) = "July" strMonth(11) = "August" Dim strProgram(0 To 5) As String strProgram(0) = "54000" strProgram(1) = "54001" strProgram(2) = "54002" strProgram(3) = "54010" strProgram(4) = "54020" strProgram(5) = "54040" Dim IYearIndex As Integer IYearIndex = -1 If optYear0.Value = True Then IYearIndex = 0 ElseIf optYear1.Value = True Then IYearIndex = 1 ElseIf optYear2.Value = True Then IYearIndex = 2 ElseIf optYear3.Value = True Then IYearIndex = 3 ElseIf optYear4.Value = True Then IYearIndex = 4 ElseIf optYear5.Value = True Then IYearIndex = 5 End If Dim IMonthIndex As Integer IMonthIndex = -1 If chkMonth0.Value = True Then IMonthIndex = 0 ElseIf chkMonth1.Value = True Then IMonthIndex = 1 ElseIf chkMonth2.Value = True Then IMonthIndex = 2 ElseIf chkMonth3.Value = True Then IMonthIndex = 3 ElseIf chkMonth4.Value = True Then IMonthIndex = 4 ElseIf chkMonth5.Value = True Then IMonthIndex = 5 ElseIf chkMonth6.Value = True Then IMonthIndex = 6 ElseIf chkMonth7.Value = True Then IMonthIndex = 7 ElseIf chkMonth8.Value = True Then IMonthIndex = 8 ElseIf chkMonth9.Value = True Then IMonthIndex = 9 ElseIf chkMonth10.Value = True Then IMonthIndex = 10 ElseIf chkMonth11.Value = True Then IMonthIndex = 11 End If Dim IProgramIndex As Integer IProgramIndex = -1 If chkProgram0.Value = True Then IProgramIndex = 0 ElseIf chkProgram1.Value = True Then IProgramIndex = 1 ElseIf chkProgram2.Value = True Then IProgramIndex = 2 ElseIf chkProgram3.Value = True Then IProgramIndex = 3 ElseIf chkProgram4.Value = True Then IProgramIndex = 4 ElseIf chkProgram5.Value = True Then IProgramIndex = 5 End If Dim strRoot As String strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY" & strYear(IYearIndex) & "uploaded files" Dim strUpload As String strUpload = strRoot & "\" & strMonth(IMonthIndex) & " " & strYear(IYearIndex) & strProgram(IProgramIndex) & "GL UPLOAD.csv" ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False End Sub I really appreciate your help! -- Regards, timmulla "LabElf" wrote: As I see it, you need to separate the variable parts of your file paths from the repeated parts, and make a better method for selecting the differences. I will assume for demonstration purposes that the varying parts of your file paths are the part after "FY" (" 2004", " 2005", etc.) and the month+year part of the destination file name ("September 2005", "October 2005", etc.). You need to put these into arrays, for example: Dim strYear(0 to 6) As String strYear(0) = "2000" strYear(1) = "2001" ... etc., and Dim strMonth(0 To 11) As String strMonth(0) = "January" strMonth(2) = "February" ... etc. Now go through your checkboxes to create indices: Dim iYearIndex as Integer iYearIndex = -1 If chkYear0.Value = True Then iYearIndex = 0 ElseIf chkYear1.Value = True Then iYearIndex = 1 ... ElseIf chkYear6.Value = True Then iYearIndex = 6 End If Dim iMonthIndex as Integer iMonthIndex = -1 If chkMonth0.Value = True Then iMonthIndex = 0 ElseIf chkMonth1.Value = True Then iMonthIndex = 1 ... ElseIf chkMonth11.Value = True Then iMonthIndex = 11 End If (You can use the -1 default values on the indices to detect if the user didn't check any of the boxes.) Then construct your file path strings: strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY " & _ strYear(iYearIndex) & " uploaded files" strUpload = strRoot & "\" & strMonth(iMonthIndex) & " " & strYear(iYearIndex) & _ " GL UPLOAD.csv" You then can have your ChDir and Save File commands occur only once, as: ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False If you use list boxes instead of check boxes, you can generate your indices quicker, or get your month and year components directly from the choices in the listboxes, but that's up to you. -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: I need a little help with a macro I've created. I currently have a form with check boxes indicating months and fiscal years. For example a user can click the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button that automatically saves the file "September 2005 GL UPLOAD.csv" to a location that I already specified in code. I used a multiple condition "If then" statement to accomplish my task. The following code is a example of this: If CheckBox1.Value = True Then If OptionButton1.Value = True Then Sheets("54040 MONTHLY UPLOAD DATA").Select Sheets("54040 MONTHLY UPLOAD DATA").Copy ChDir _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files" ActiveWorkbook.SaveAs Filename:= _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files\September 2005 GL UPLOAD.csv" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End If End If I basically copied this code numerous times, just changing the object names and file names to make the macro work properly.The macro works fine, however, it's not the most effecient macro. I had to write the code (above) 24 times to accomadate just two fiscal years. My boss wants me to find a more effecient way of accomplishing this task. He suggested looking into declaring variables for the file names so when a user clicks a checkbox the program will automatically know the naming of the file (and where) to save the file. Can anyone help me with this, or at least point me in the right direction? I have moderate excel programming skills and limited knowledge on using variables. Any information would be appreciated -- Regards, timmulla |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
I'm sorry, Timulla, I can't find any unmatched Else statements in the code
you posted. Did you change any other routines? If you're sure it's this routine, I would suggest you replace the If-ElseIf constructs with dummy assignments and see if the problem goes away. Then replace them one by one to see if the problem comes back. This should help you locate the source of the error. A few other comments: I noticed you used my names for the checkboxes. Did you also change the names of your checkboxes to match? You have strMonth(2) misspelled as Novemeber. In your new code, you've taken out the spaces around the year string in the last directory in the path. If you want to use the same subdirectory as before, add a space after "FY" and before "uploaded files". I can't tell for sure, because of the way the forum program breaks lines, but do you have "_" characters to connect your multiline commands? You can assign the default (-1) index values with a final Else clause at the end of your If-ElseIf structures. For instance: If optYear0.Value = True Then IYearIndex = 0 ElseIf optYear1.Value = True Then IYearIndex = 1 ... Else iYearIndex = -1 End If Be sure to check the indices before attempting to use them. You will get a "subscript out of range" error if you try to use a subscript with a -1 value. Pop up a message box and quit if any subscript has value -1 after you have tested all the checkboxes. Good Luck! -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: Everybody thanks for your help! LabElf, I really liked the way you wrote your code to solve my problem, so I decided to use your method. However, after writing my code I'm getting errors pertaining to my If, ElseIf statements. The error says "compile error - Else without If. If you don't mind, and have a free moment could you please look over my code. You will also notice that I had to add another variable (program) to file path naming convention. My code is: Private Sub CommandButton1_Click() Dim strYear(0 To 5) As String strYear(0) = "2005" strYear(1) = "2006" strYear(2) = "2007" strYear(3) = "2008" strYear(4) = "2009" strYear(5) = "2010" Dim strMonth(0 To 11) As String strMonth(0) = "September" strMonth(1) = "October" strMonth(2) = "Novemeber" strMonth(3) = "December" strMonth(4) = "January" strMonth(5) = "February" strMonth(6) = "March" strMonth(7) = "April" strMonth(8) = "May" strMonth(9) = "June" strMonth(10) = "July" strMonth(11) = "August" Dim strProgram(0 To 5) As String strProgram(0) = "54000" strProgram(1) = "54001" strProgram(2) = "54002" strProgram(3) = "54010" strProgram(4) = "54020" strProgram(5) = "54040" Dim IYearIndex As Integer IYearIndex = -1 If optYear0.Value = True Then IYearIndex = 0 ElseIf optYear1.Value = True Then IYearIndex = 1 ElseIf optYear2.Value = True Then IYearIndex = 2 ElseIf optYear3.Value = True Then IYearIndex = 3 ElseIf optYear4.Value = True Then IYearIndex = 4 ElseIf optYear5.Value = True Then IYearIndex = 5 End If Dim IMonthIndex As Integer IMonthIndex = -1 If chkMonth0.Value = True Then IMonthIndex = 0 ElseIf chkMonth1.Value = True Then IMonthIndex = 1 ElseIf chkMonth2.Value = True Then IMonthIndex = 2 ElseIf chkMonth3.Value = True Then IMonthIndex = 3 ElseIf chkMonth4.Value = True Then IMonthIndex = 4 ElseIf chkMonth5.Value = True Then IMonthIndex = 5 ElseIf chkMonth6.Value = True Then IMonthIndex = 6 ElseIf chkMonth7.Value = True Then IMonthIndex = 7 ElseIf chkMonth8.Value = True Then IMonthIndex = 8 ElseIf chkMonth9.Value = True Then IMonthIndex = 9 ElseIf chkMonth10.Value = True Then IMonthIndex = 10 ElseIf chkMonth11.Value = True Then IMonthIndex = 11 End If Dim IProgramIndex As Integer IProgramIndex = -1 If chkProgram0.Value = True Then IProgramIndex = 0 ElseIf chkProgram1.Value = True Then IProgramIndex = 1 ElseIf chkProgram2.Value = True Then IProgramIndex = 2 ElseIf chkProgram3.Value = True Then IProgramIndex = 3 ElseIf chkProgram4.Value = True Then IProgramIndex = 4 ElseIf chkProgram5.Value = True Then IProgramIndex = 5 End If Dim strRoot As String strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY" & strYear(IYearIndex) & "uploaded files" Dim strUpload As String strUpload = strRoot & "\" & strMonth(IMonthIndex) & " " & strYear(IYearIndex) & strProgram(IProgramIndex) & "GL UPLOAD.csv" ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False End Sub I really appreciate your help! -- Regards, timmulla "LabElf" wrote: As I see it, you need to separate the variable parts of your file paths from the repeated parts, and make a better method for selecting the differences. I will assume for demonstration purposes that the varying parts of your file paths are the part after "FY" (" 2004", " 2005", etc.) and the month+year part of the destination file name ("September 2005", "October 2005", etc.). You need to put these into arrays, for example: Dim strYear(0 to 6) As String strYear(0) = "2000" strYear(1) = "2001" ... etc., and Dim strMonth(0 To 11) As String strMonth(0) = "January" strMonth(2) = "February" ... etc. Now go through your checkboxes to create indices: Dim iYearIndex as Integer iYearIndex = -1 If chkYear0.Value = True Then iYearIndex = 0 ElseIf chkYear1.Value = True Then iYearIndex = 1 ... ElseIf chkYear6.Value = True Then iYearIndex = 6 End If Dim iMonthIndex as Integer iMonthIndex = -1 If chkMonth0.Value = True Then iMonthIndex = 0 ElseIf chkMonth1.Value = True Then iMonthIndex = 1 ... ElseIf chkMonth11.Value = True Then iMonthIndex = 11 End If (You can use the -1 default values on the indices to detect if the user didn't check any of the boxes.) Then construct your file path strings: strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY " & _ strYear(iYearIndex) & " uploaded files" strUpload = strRoot & "\" & strMonth(iMonthIndex) & " " & strYear(iYearIndex) & _ " GL UPLOAD.csv" You then can have your ChDir and Save File commands occur only once, as: ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False If you use list boxes instead of check boxes, you can generate your indices quicker, or get your month and year components directly from the choices in the listboxes, but that's up to you. -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: I need a little help with a macro I've created. I currently have a form with check boxes indicating months and fiscal years. For example a user can click the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button that automatically saves the file "September 2005 GL UPLOAD.csv" to a location that I already specified in code. I used a multiple condition "If then" statement to accomplish my task. The following code is a example of this: If CheckBox1.Value = True Then If OptionButton1.Value = True Then Sheets("54040 MONTHLY UPLOAD DATA").Select Sheets("54040 MONTHLY UPLOAD DATA").Copy ChDir _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files" ActiveWorkbook.SaveAs Filename:= _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files\September 2005 GL UPLOAD.csv" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End If End If I basically copied this code numerous times, just changing the object names and file names to make the macro work properly.The macro works fine, however, it's not the most effecient macro. I had to write the code (above) 24 times to accomadate just two fiscal years. My boss wants me to find a more effecient way of accomplishing this task. He suggested looking into declaring variables for the file names so when a user clicks a checkbox the program will automatically know the naming of the file (and where) to save the file. Can anyone help me with this, or at least point me in the right direction? I have moderate excel programming skills and limited knowledge on using variables. Any information would be appreciated -- Regards, timmulla |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
Hi LabElf,
I had already changed the names of the checkboxes. I always seem to have problems using the "ElseIf" statement at work. I study excel programming at home for excel 2003 , however, at work it doesn't seem to work for me (I believe we use excel 2000 at work). Well anyways, I eventualy got my macro to work properly using the following code for my statements: Dim iYearIndex As Integer iYearIndex = -1 If optYear0.Value = True Then iYearIndex = 0 If optYear1.Value = True Then iYearIndex = 1 If optYear2.Value = True Then iYearIndex = 2 If optYear3.Value = True Then iYearIndex = 3 If optYear4.Value = True Then iYearIndex = 4 If optYear5.Value = True Then iYearIndex = 5 I know it might not be the best way to accomplish my task, but until I figure out how to do it better, it will have to do. I still need to figure out how to let only one check box be true at a time. Could you recommend any good books on excel programming? I'm looking for good intermediate level books b/c I don't have a programming background (finance/accounting). Or any excel programming classes offered by colleges or computer schools? I greatly appreciated your help! I learned a tremendous amount today. Regards, timmulla "timmulla" wrote: Everybody thanks for your help! LabElf, I really liked the way you wrote your code to solve my problem, so I decided to use your method. However, after writing my code I'm getting errors pertaining to my If, ElseIf statements. The error says "compile error - Else without If. If you don't mind, and have a free moment could you please look over my code. You will also notice that I had to add another variable (program) to file path naming convention. My code is: Private Sub CommandButton1_Click() Dim strYear(0 To 5) As String strYear(0) = "2005" strYear(1) = "2006" strYear(2) = "2007" strYear(3) = "2008" strYear(4) = "2009" strYear(5) = "2010" Dim strMonth(0 To 11) As String strMonth(0) = "September" strMonth(1) = "October" strMonth(2) = "Novemeber" strMonth(3) = "December" strMonth(4) = "January" strMonth(5) = "February" strMonth(6) = "March" strMonth(7) = "April" strMonth(8) = "May" strMonth(9) = "June" strMonth(10) = "July" strMonth(11) = "August" Dim strProgram(0 To 5) As String strProgram(0) = "54000" strProgram(1) = "54001" strProgram(2) = "54002" strProgram(3) = "54010" strProgram(4) = "54020" strProgram(5) = "54040" Dim IYearIndex As Integer IYearIndex = -1 If optYear0.Value = True Then IYearIndex = 0 ElseIf optYear1.Value = True Then IYearIndex = 1 ElseIf optYear2.Value = True Then IYearIndex = 2 ElseIf optYear3.Value = True Then IYearIndex = 3 ElseIf optYear4.Value = True Then IYearIndex = 4 ElseIf optYear5.Value = True Then IYearIndex = 5 End If Dim IMonthIndex As Integer IMonthIndex = -1 If chkMonth0.Value = True Then IMonthIndex = 0 ElseIf chkMonth1.Value = True Then IMonthIndex = 1 ElseIf chkMonth2.Value = True Then IMonthIndex = 2 ElseIf chkMonth3.Value = True Then IMonthIndex = 3 ElseIf chkMonth4.Value = True Then IMonthIndex = 4 ElseIf chkMonth5.Value = True Then IMonthIndex = 5 ElseIf chkMonth6.Value = True Then IMonthIndex = 6 ElseIf chkMonth7.Value = True Then IMonthIndex = 7 ElseIf chkMonth8.Value = True Then IMonthIndex = 8 ElseIf chkMonth9.Value = True Then IMonthIndex = 9 ElseIf chkMonth10.Value = True Then IMonthIndex = 10 ElseIf chkMonth11.Value = True Then IMonthIndex = 11 End If Dim IProgramIndex As Integer IProgramIndex = -1 If chkProgram0.Value = True Then IProgramIndex = 0 ElseIf chkProgram1.Value = True Then IProgramIndex = 1 ElseIf chkProgram2.Value = True Then IProgramIndex = 2 ElseIf chkProgram3.Value = True Then IProgramIndex = 3 ElseIf chkProgram4.Value = True Then IProgramIndex = 4 ElseIf chkProgram5.Value = True Then IProgramIndex = 5 End If Dim strRoot As String strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY" & strYear(IYearIndex) & "uploaded files" Dim strUpload As String strUpload = strRoot & "\" & strMonth(IMonthIndex) & " " & strYear(IYearIndex) & strProgram(IProgramIndex) & "GL UPLOAD.csv" ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False End Sub I really appreciate your help! -- Regards, timmulla "LabElf" wrote: As I see it, you need to separate the variable parts of your file paths from the repeated parts, and make a better method for selecting the differences. I will assume for demonstration purposes that the varying parts of your file paths are the part after "FY" (" 2004", " 2005", etc.) and the month+year part of the destination file name ("September 2005", "October 2005", etc.). You need to put these into arrays, for example: Dim strYear(0 to 6) As String strYear(0) = "2000" strYear(1) = "2001" ... etc., and Dim strMonth(0 To 11) As String strMonth(0) = "January" strMonth(2) = "February" ... etc. Now go through your checkboxes to create indices: Dim iYearIndex as Integer iYearIndex = -1 If chkYear0.Value = True Then iYearIndex = 0 ElseIf chkYear1.Value = True Then iYearIndex = 1 ... ElseIf chkYear6.Value = True Then iYearIndex = 6 End If Dim iMonthIndex as Integer iMonthIndex = -1 If chkMonth0.Value = True Then iMonthIndex = 0 ElseIf chkMonth1.Value = True Then iMonthIndex = 1 ... ElseIf chkMonth11.Value = True Then iMonthIndex = 11 End If (You can use the -1 default values on the indices to detect if the user didn't check any of the boxes.) Then construct your file path strings: strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY " & _ strYear(iYearIndex) & " uploaded files" strUpload = strRoot & "\" & strMonth(iMonthIndex) & " " & strYear(iYearIndex) & _ " GL UPLOAD.csv" You then can have your ChDir and Save File commands occur only once, as: ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False If you use list boxes instead of check boxes, you can generate your indices quicker, or get your month and year components directly from the choices in the listboxes, but that's up to you. -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: I need a little help with a macro I've created. I currently have a form with check boxes indicating months and fiscal years. For example a user can click the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button that automatically saves the file "September 2005 GL UPLOAD.csv" to a location that I already specified in code. I used a multiple condition "If then" statement to accomplish my task. The following code is a example of this: If CheckBox1.Value = True Then If OptionButton1.Value = True Then Sheets("54040 MONTHLY UPLOAD DATA").Select Sheets("54040 MONTHLY UPLOAD DATA").Copy ChDir _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files" ActiveWorkbook.SaveAs Filename:= _ "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded files\September 2005 GL UPLOAD.csv" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End If End If I basically copied this code numerous times, just changing the object names and file names to make the macro work properly.The macro works fine, however, it's not the most effecient macro. I had to write the code (above) 24 times to accomadate just two fiscal years. My boss wants me to find a more effecient way of accomplishing this task. He suggested looking into declaring variables for the file names so when a user clicks a checkbox the program will automatically know the naming of the file (and where) to save the file. Can anyone help me with this, or at least point me in the right direction? I have moderate excel programming skills and limited knowledge on using variables. Any information would be appreciated -- Regards, timmulla |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
savings files with the use of variables
Thanks for your insight, it has been very helpfull. I think I'll work on
changing the check boxes to options buttons in the future. I might also make one of the variables a list box. This should help reinforce what you helped me learn today. Thanks again for your help. -- Regards, timmulla "LabElf" wrote: Timmulla - Sorry the "ElseIf" construct didn't work in your Excel 2000 macro. I checked some macro code I have, and I am able to use the If-ElseIf-Else-End If construct without error in Excel 2000 and Excel 2003. If you can't use ElseIf, the series of If statements you have is probably the next best thing. Note that if you have more than one checkbox checked, the ElseIf block will pick up the first one and not check the subsequent ones. The series of If statements will check all the boxes and you will get the result from the last one. You could write the full form of a nested If, e.g., If optYear0.Value = True Then iYearIndex = 0 Else If optYear1.Value = True Then iYearIndex = 1 Else If optYear2.Value = True Then iYearIndex = 2 Else iYearIndex = -1 End If End If End If but you might run into limits to the nesting depth and there's more chance of getting your "If"s and "End If"s unbalanced. (I skipped option boxes for 3, 4 and 5 in my example above.) I don't know if this is the best book available, but when I started with my project I got "Using Excel Visual Basic for Applications", 2nd Edition, by Jeff Webb, published 1996 by Que Corporation. It has some errors, but then, nearly all do. Use at your own risk, no models under 18 were used etc. ... I do most of my work with full-on Visual Basic, so I'm not that familiar with how certain things are done in the macro world, but that book does tend to lean toward the macro approach. I looked in there for a way to have only one check box at a time be true, and the answer is to use OptionButtons instead. Put each group of buttons that you want to be mutually exclusive into a group box (add the group box so it encloses the OptionButtons). Excel will allow only one button in the group to be True. Even better, you should look into using drop-down list boxes. My book says they can be placed on worksheets, charts, and dialog sheets, so you should be able to use them. You could load them up with your years, months, and programs, and just use the selected values - no need for complex logic to figure out what was selected. Alternatively, you could attach each check box button to subroutine that would set the value of a variable (which would have to be global to your module). Contact me if you get the book and want to know what errors I've found so far. You can respond to this thread, or email me at where first = jim and last = rodarmel. Finally, you might mark my response as being helpful in the forum here so others will see it as an answer (click on the post that was useful, and click the Yes button next to the "Was this post helpful to you?" question. -- In theory, there is no difference between theory and practice; in practice, there is. "timmulla" wrote: Hi LabElf, I had already changed the names of the checkboxes. I always seem to have problems using the "ElseIf" statement at work. I study excel programming at home for excel 2003 , however, at work it doesn't seem to work for me (I believe we use excel 2000 at work). Well anyways, I eventualy got my macro to work properly using the following code for my statements: Dim iYearIndex As Integer iYearIndex = -1 If optYear0.Value = True Then iYearIndex = 0 If optYear1.Value = True Then iYearIndex = 1 If optYear2.Value = True Then iYearIndex = 2 If optYear3.Value = True Then iYearIndex = 3 If optYear4.Value = True Then iYearIndex = 4 If optYear5.Value = True Then iYearIndex = 5 I know it might not be the best way to accomplish my task, but until I figure out how to do it better, it will have to do. I still need to figure out how to let only one check box be true at a time. Could you recommend any good books on excel programming? I'm looking for good intermediate level books b/c I don't have a programming background (finance/accounting). Or any excel programming classes offered by colleges or computer schools? I greatly appreciated your help! I learned a tremendous amount today. Regards, timmulla "timmulla" wrote: Everybody thanks for your help! LabElf, I really liked the way you wrote your code to solve my problem, so I decided to use your method. However, after writing my code I'm getting errors pertaining to my If, ElseIf statements. The error says "compile error - Else without If. If you don't mind, and have a free moment could you please look over my code. You will also notice that I had to add another variable (program) to file path naming convention. My code is: Private Sub CommandButton1_Click() Dim strYear(0 To 5) As String strYear(0) = "2005" strYear(1) = "2006" strYear(2) = "2007" strYear(3) = "2008" strYear(4) = "2009" strYear(5) = "2010" Dim strMonth(0 To 11) As String strMonth(0) = "September" strMonth(1) = "October" strMonth(2) = "Novemeber" strMonth(3) = "December" strMonth(4) = "January" strMonth(5) = "February" strMonth(6) = "March" strMonth(7) = "April" strMonth(8) = "May" strMonth(9) = "June" strMonth(10) = "July" strMonth(11) = "August" Dim strProgram(0 To 5) As String strProgram(0) = "54000" strProgram(1) = "54001" strProgram(2) = "54002" strProgram(3) = "54010" strProgram(4) = "54020" strProgram(5) = "54040" Dim IYearIndex As Integer IYearIndex = -1 If optYear0.Value = True Then IYearIndex = 0 ElseIf optYear1.Value = True Then IYearIndex = 1 ElseIf optYear2.Value = True Then IYearIndex = 2 ElseIf optYear3.Value = True Then IYearIndex = 3 ElseIf optYear4.Value = True Then IYearIndex = 4 ElseIf optYear5.Value = True Then IYearIndex = 5 End If Dim IMonthIndex As Integer IMonthIndex = -1 If chkMonth0.Value = True Then IMonthIndex = 0 ElseIf chkMonth1.Value = True Then IMonthIndex = 1 ElseIf chkMonth2.Value = True Then IMonthIndex = 2 ElseIf chkMonth3.Value = True Then IMonthIndex = 3 ElseIf chkMonth4.Value = True Then IMonthIndex = 4 ElseIf chkMonth5.Value = True Then IMonthIndex = 5 ElseIf chkMonth6.Value = True Then IMonthIndex = 6 ElseIf chkMonth7.Value = True Then IMonthIndex = 7 ElseIf chkMonth8.Value = True Then IMonthIndex = 8 ElseIf chkMonth9.Value = True Then IMonthIndex = 9 ElseIf chkMonth10.Value = True Then IMonthIndex = 10 ElseIf chkMonth11.Value = True Then IMonthIndex = 11 End If Dim IProgramIndex As Integer IProgramIndex = -1 If chkProgram0.Value = True Then IProgramIndex = 0 ElseIf chkProgram1.Value = True Then IProgramIndex = 1 ElseIf chkProgram2.Value = True Then IProgramIndex = 2 ElseIf chkProgram3.Value = True Then IProgramIndex = 3 ElseIf chkProgram4.Value = True Then IProgramIndex = 4 ElseIf chkProgram5.Value = True Then IProgramIndex = 5 End If Dim strRoot As String strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY" & strYear(IYearIndex) & "uploaded files" Dim strUpload As String strUpload = strRoot & "\" & strMonth(IMonthIndex) & " " & strYear(IYearIndex) & strProgram(IProgramIndex) & "GL UPLOAD.csv" ChDir strRoot ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False End Sub I really appreciate your help! -- Regards, timmulla |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Man Hour Savings | Excel Discussion (Misc queries) | |||
| Trying to get annual savings | Excel Discussion (Misc queries) | |||
| Happy Daylight Savings | Excel Discussion (Misc queries) | |||
| What kind of savings instrument is this? | Excel Discussion (Misc queries) | |||
| Public variables and separate library files | Excel Programming | |||
Hybrid Mode
