Hey Dave:

Got the following error message at the line starting at:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

Compile Error
Syntex Error

"Dave Peterson" wrote:

dim myNum as long
dim myFileName as string
dim myFolder as string

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)
if myNum < 1 _
or mynum 500 then
msgbox "try again later"
exit sub
end if

myfolder = "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples\"
if right(myfolder,1) < "\" then
myfolder = myfolder & "\"
end if

myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& myNum _
& "_20080111_20080202.xls"

activeworkbook.saveas filename:=myfilename, _
FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


if you wanted leading 0's in that "84" portion, you could use:
myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& format(myNum,"000") _
& "_20080111_20080202.xls"

ps.
You don't need the ChDir line in your code. The .saveas is sufficient.


Beep Beep wrote:

I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--

Dave Peterson