Change the file name in Macro

  • Thread starter Thread starter terdampar
  • Start date Start date
T

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.
 
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
 
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????
 
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")
 
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")
Click to expand...

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! :-)
 
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).
 
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 -
Click to expand...

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.
 
I understand what etcetera means. I'm just wasn't sure what you meant by it.

But glad you got it working.
 
Back
Top