Macro for creating dynamic filenames

[Brian Beck]

I'm sure this is going to end up being convoluted, but if anyone can help
with this I will be so ecstatic!

I have an excel file that contains business names (Business), a unique ID
that will be used later for generating a letter (LetterID), and a month
field that tracks what month a particular order took place (Month). There
are other fields, but these are the only ones I'm concerned with. I will be
using this excel file for a mail merge with a letter template that will
generate a letter for each of the businesses in the list. There will be an
entry in the spreadsheet for everytime a business placed an order, so if
Business XYZ placed 12 orders for the time period I'm dealing with, then
there will be 12 instances of "XYZ" in the Business column of the
spreadsheet...and the month column will contain either Mar, Apr, or May
depending on when the order was placed.

Here is the problem...I'm wanting to add a field to the spreadsheet called
"Filename" that will concatenate together the business name, the unique
letter ID used for them this letter, and the months that the letter will
include information for. So essentially the filename template would look
like this:

BusinessName_LetterID_Months06

where Months could be any combination of Mar, Apr, and/or May and the 06 is
just marking the year.

Now concatenating the first 2 things is no problem...but figuring out how to
determine whether the Months should be Mar, MarApr, MarMay, MarAprMay, Apr,
AprMay, or May is causing me to pull my hair out. It's easy enough if there
is only one entry for a business because then obviously the "Months" part of
the filename will simply be whatever value is in "Month"...but if there is
more then one entry for the business, how can I programmatically find out
which months need to be included in the filename?

I hope this makes sense...my brain seems to be turning to mush over this.

-Brian

 

[Bob Phillips]

Function DynFilename(Business, LetterId) As String
Dim cell As Range
DynFilename = Business & "_" & LetterId & "_"
For Each cell In ActiveSheet.Range("Businesses")
If cell.Value = Business And cell.Offset(0, 1).Value = LetterId Then
DynFilename = DynFilename & Format(cell.Offset(0, 2).Value,
"mmm")
End If
Next cell

End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Brian Beck]

But wouldn't this just append the month to the filename for each entry in
the spreadsheet?

So if I had the following entry in the excel file:

BusinessName LetterID Month
ACME C06-089 Mar
ACME C06-089 Mar
ACME C06-089 Apr
ACME C06-089 Apr
BusA C06-090 Apr
BusA C06-090 May

Wouldn't I end up with the following filenames?:

ACME_C06-089_Mar
ACME_C06-089_Mar
ACME_C06-089_Apr
ACME_C06-089_Apr
BusA_C06-090_Apr
BusA_C06-090_May

What I'm wanting to get is the following:

ACME_C06-089_MarApr
ACME_C06-089_MarApr
ACME_C06-089_MarApr
ACME_C06-089_MarApr
BusA_C06-090_AprMay
BusA_C06-090_AprMay

All of the ACME entries are going to end up in 1 letter, so I only need 1
filename to use for that letter....same with BusA and so on.

-Brian

 

[Bob Phillips]

Try it and see, you might be surprised.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Brian Beck]

OK, I finally got the coding to work, and it gets me closer to what I need,
but I'm still not there. In the example that I gave earlier, I now see
output that looks like this:

ACME_C06-089_MarMarAprApr
ACME_C06-089_MarMarAprApr
ACME_C06-089_MarMarAprApr
ACME_C06-089_MarMarAprApr
BusA_C06-090_AprMay
BusA_C06-090_AprMay

Now BusA turns out being exactly what I need, but only because there just
happened to be only one April and one March. But ACME has ALL the months
appended and what I'm wanting to get is just 1 occurrence of each month that
is present. So even if a company had 45 records that consisted of 10 in
March, 30 in April and 5 in May, the filename I want would just read:

companyname_LetterID_MarAprMay

-Brian

 

[Bob Phillips]

You never mentioned there could be multiples per month!

Function DynFilename(Business, LetterId) As String
Dim cell As Range
Dim colMonths As Collection
Dim itm

Set colMonths = New Collection
DynFilename = Business & "_" & LetterId & "_"
For Each cell In ActiveSheet.Range("Businesses")
If cell.Value = Business And cell.Offset(0, 1).Value = LetterId Then
On Error Resume Next
colMonths.Add Format(cell.Offset(0, 2).Value, "mmm"), _
Format(cell.Offset(0, 2).Value, "mmm")
On Error GoTo 0
End If
Next cell

For Each itm In colMonths
DynFilename = DynFilename & itm
Next itm
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Brian Beck]

You're right, I totally failed to mention that. I apologize...and I also
bow down before you and your Excel mastery. Thank you!!!!!

-Brian