Change the file name in Macro

[terdampar]

I created Macro that open several workbooks (actually 17 of them) at
the same time like this;

Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\01 - Feb09 CFW.xls"
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\11 - Feb09 CFW.xls"
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\02-09 RUI SALES\21 - Feb09 CFW.xls"

It works fine till I have to use it for the following month. I have to
go back to my Macro and change manually the directory from 02-09 to
03-09 and file name from Feb09 to Mar09.

I need your help if you can advise me the more practical way to do it?
Do I need to change my Macro or write another Macro?

Thank you so much.

 

[Gary''s Student]

Define two strings one time and use them 17 times:

Dim s As String, t As String
t = "Feb09"
s = "02-09"
Workbooks.Open Filename:= _
"H:\Month End Work\FY 2009\" & s & "RUI SALES\01 - " & t & "CFW.xls"

So each month you only need to update two statements rather than 17

 

[Dave Peterson]

Can you use the current date to determine the names of the folders and files?

If yes:

Dim myPath as string
dim myDate as string
dim iCtr as long

mypath = "H:\Month End Work\FY " & year(date) _
& "\" & format(date,"mm-yy") & " RUI SALES\"

mydate = format(date,"mmmyy")

for ictr = 1 to 21 step 10
workbooks.open filename:=mypath & format(ictr,"00") _
& " - " & mydate & " cfw.xls"
next ictr

========
I'm not sure how the names are really created. Maybe you can use a loop and
Format() to get the prefix for those filenames????

 

[Dave Peterson]

Ps. If you can't use the current date, maybe you could just ask for the date to
use in an inputbox???

Dim myInputDate as date
myinputdate = application.inputbox(Prompt:="Enter the date to use", type:=1)

'minor validity checks
if year(myinputdate) < 2000 _
or year(myinputdate) > 2020 then
msgbox "Quitting!"
exit sub
end if

Then
mypath = "H:\Month End Work\FY " & year(date) _
& "\" & format(date,"mm-yy") & " RUI SALES\"

mydate = format(date,"mmmyy")

would become:

mypath = "H:\Month End Work\FY " & year(myinputdate) _
& "\" & format(myinputdate,"mm-yy") & " RUI SALES\"

mydate = format(myinputdate,"mmmyy")

 

[terdampar]

Ps.  If you can't use the current date, maybe you could just ask for the date to
use in an inputbox???

Dim myInputDate as date
myinputdate = application.inputbox(Prompt:="Enter the date to use", type:=1)

'minor validity checks
if year(myinputdate) < 2000 _
 or year(myinputdate) > 2020 then
  msgbox "Quitting!"
  exit sub
end if

Then
mypath = "H:\Month End Work\FY " & year(date) _
            & "\" & format(date,"mm-yy") & " RUI SALES\"

mydate = format(date,"mmmyy")

would become:

mypath = "H:\Month End Work\FY " & year(myinputdate) _
            & "\" & format(myinputdate,"mm-yy") & " RUI SALES\"

mydate = format(myinputdate,"mmmyy")

Thank you Dave,

The problem now is that "Step 10" thing. I think since I gave you
example 01 11 and 21 you must've assumed that it was added by 10 for
every new branch ... my bad.
I should have told you that the branch number is built by the area
code so it's unique. Actually it's 03; 05; 06; 10; 12; 14; 16; etc.
It's not mathematical numbers at all.
How could I get it right now? other than that your VBA is awesome!

 

[Dave Peterson]

Dim iCtr as long
dim myNums as variant
mynums = array(3, 5, 6, 10, 12, 14, 16)

' all that other stuff.

For ictr = lbound(mynums) to ubound(mynums)
workbooks.open filename:=mypath & format(mynums(ictr),"00") _
& " - " & mydate & " cfw.xls"
next ictr

I'm not sure what etc means in this case. I'm assuming that it's less than 100
(two digit numbers).

 

[terdampar]

Dim iCtr as long
dim myNums as variant
mynums = array(3, 5, 6, 10, 12, 14, 16)

' all that other stuff.

For ictr = lbound(mynums) to ubound(mynums)
  workbooks.open filename:=mypath & format(mynums(ictr),"00") _
                             & " - " & mydate & " cfw.xls"
next ictr

I'm not sure what etc means in this case.  I'm assuming that it's less than 100
(two digit numbers).








--

Dave Peterson- Hide quoted text -

- Show quoted text -

It works great!! ... thank you so much!
etc means we have another branches, but for now it's not more than 17
branches. I just didn't want to say all.

 

[Dave Peterson]

I understand what etcetera means. I'm just wasn't sure what you meant by it.

But glad you got it working.