savings files with the use of variables

[Guest]

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

 

[abcd]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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!

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

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 (e-mail address removed)
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.

 

[Guest]

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.